1 | //retrieve alias lowercase |
---|
2 | |
---|
3 | executing query |
---|
4 | select A.ld_id, A.ld_customid, A.ld_docref, A.ld_type, A.ld_version, A.ld_lastmodified, A.ld_date, A.ld_publisher, A.ld_creation, A.ld_creator, A.ld_filesize, A.ld_immutable, A.ld_indexed, A.ld_lockuserid, A.ld_filename, A.ld_status, A.ld_signed, A.ld_rating, A.ld_fileversion, A.ld_comment, A.ld_workflowstatus, A.ld_startpublishing, A.ld_stoppublishing, A.ld_published, FOLD.ld_name, A.ld_folderid, A.ld_tgs tags, A.ld_templateid, C.ld_name, A.ld_tenantid, A.ld_docreftype, A.ld_stamped, A.ld_password, A.ld_workflowstatusdisp, A.ld_language, A.ld_pages, A.ld_color from ld_document A join ld_folder FOLD on A.ld_folderid=FOLD.ld_id left outer join ld_template C on A.ld_templateid=C.ld_id , ld_document_ext C1 where A.ld_deleted=0 and A.ld_nature=0 and not A.ld_status = 3 and A.ld_folderid=FOLD.ld_id and A.ld_tenantid=1 and A.ld_templateid=102 and A.ld_docref is null and ( ((C1.ld_docid=A.ld_id and (C1.ld_name='Keyword' or C1.ld_name like 'Keyword-%') and lower(C1.ld_stringvalue) like '%memento%'))) UNION select A.ld_id, REF.ld_customid, A.ld_docref, A.ld_type, REF.ld_version, REF.ld_lastmodified, REF.ld_date, REF.ld_publisher, REF.ld_creation, REF.ld_creator, REF.ld_filesize, REF.ld_immutable, REF.ld_indexed, REF.ld_lockuserid, A.ld_filename, REF.ld_status, REF.ld_signed, REF.ld_rating, REF.ld_fileversion, A.ld_comment, REF.ld_workflowstatus, REF.ld_startpublishing, REF.ld_stoppublishing, REF.ld_published, FOLD.ld_name, A.ld_folderid, REF.ld_tgs tags, REF.ld_templateid, C.ld_name, A.ld_tenantid, A.ld_docreftype, REF.ld_stamped, REF.ld_password, REF.ld_workflowstatusdisp, REF.ld_language, REF.ld_pages, REF.ld_color from ld_document A join ld_folder FOLD on A.ld_folderid=FOLD.ld_id join ld_document REF on A.ld_docref=REF.ld_id left outer join ld_template C on REF.ld_templateid=C.ld_id , ld_document_ext C1 where A.ld_deleted=0 and A.ld_nature=0 and not A.ld_status = 3 and A.ld_folderid=FOLD.ld_id and A.ld_tenantid=1 and REF.ld_templateid=102 and A.ld_docref is not null and REF.ld_deleted=0 and A.ld_docref = REF.ld_id and ( ((C1.ld_docid=REF.ld_id and (C1.ld_name='Keyword' or C1.ld_name like 'Keyword-%') and lower(C1.ld_stringvalue) like '%memento%'))) |
---|
5 | |
---|
6 | // just lowercase |
---|
7 | executing query |
---|
8 | select A.ld_id, A.ld_customid, A.ld_docref, A.ld_type, A.ld_version, A.ld_lastmodified, A.ld_date, A.ld_publisher, A.ld_creation, A.ld_creator, A.ld_filesize, A.ld_immutable, A.ld_indexed, A.ld_lockuserid, A.ld_filename, A.ld_status, A.ld_signed, A.ld_rating, A.ld_fileversion, A.ld_comment, A.ld_workflowstatus, A.ld_startpublishing, A.ld_stoppublishing, A.ld_published, FOLD.ld_name, A.ld_folderid, A.ld_tgs tags, A.ld_templateid, C.ld_name, A.ld_tenantid, A.ld_docreftype, A.ld_stamped, A.ld_password, A.ld_workflowstatusdisp, A.ld_language, A.ld_pages, A.ld_color from ld_document A join ld_folder FOLD on A.ld_folderid=FOLD.ld_id left outer join ld_template C on A.ld_templateid=C.ld_id , ld_document_ext C1 where A.ld_deleted=0 and A.ld_nature=0 and not A.ld_status = 3 and A.ld_folderid=FOLD.ld_id and A.ld_tenantid=1 and A.ld_templateid=102 and A.ld_docref is null and ( ((C1.ld_docid=A.ld_id and (C1.ld_name='Keyword' or C1.ld_name like 'Keyword-%') and lower(C1.ld_stringvalue) like '%memento%'))) |
---|
9 | |
---|
10 | |
---|
11 | // saved query |
---|
12 | executing query |
---|
13 | select A.ld_id, A.ld_customid, A.ld_docref, A.ld_type, A.ld_version, A.ld_lastmodified, A.ld_date, A.ld_publisher, A.ld_creation, A.ld_creator, A.ld_filesize, A.ld_immutable, A.ld_indexed, A.ld_lockuserid, A.ld_filename, A.ld_status, A.ld_signed, A.ld_rating, A.ld_fileversion, A.ld_comment, A.ld_workflowstatus, A.ld_startpublishing, A.ld_stoppublishing, A.ld_published, FOLD.ld_name, A.ld_folderid, A.ld_tgs tags, A.ld_templateid, C.ld_name, A.ld_tenantid, A.ld_docreftype, A.ld_stamped, A.ld_password, A.ld_workflowstatusdisp, A.ld_language, A.ld_pages, A.ld_color from ld_document A join ld_folder FOLD on A.ld_folderid=FOLD.ld_id left outer join ld_template C on A.ld_templateid=C.ld_id , ld_document_ext C1 where A.ld_deleted=0 and A.ld_nature=0 and not A.ld_status = 3 and A.ld_folderid=FOLD.ld_id and A.ld_tenantid=1 and A.ld_templateid=102 and A.ld_docref is null and ( ((C1.ld_docid=A.ld_id and (C1.ld_name='Keyword' or C1.ld_name like 'Keyword-%') and C1.ld_stringvalue like '%memento%'))) |
---|
14 | |
---|
15 | |
---|
16 | // formattate: |
---|
17 | SELECT A.ld_id, |
---|
18 | A.ld_customid, |
---|
19 | A.ld_docref, |
---|
20 | A.ld_type, |
---|
21 | A.ld_version, |
---|
22 | A.ld_lastmodified, |
---|
23 | A.ld_date, |
---|
24 | A.ld_publisher, |
---|
25 | A.ld_creation, |
---|
26 | A.ld_creator, |
---|
27 | A.ld_filesize, |
---|
28 | A.ld_immutable, |
---|
29 | A.ld_indexed, |
---|
30 | A.ld_lockuserid, |
---|
31 | A.ld_filename, |
---|
32 | A.ld_status, |
---|
33 | A.ld_signed, |
---|
34 | A.ld_rating, |
---|
35 | A.ld_fileversion, |
---|
36 | A.ld_comment, |
---|
37 | A.ld_workflowstatus, |
---|
38 | A.ld_startpublishing, |
---|
39 | A.ld_stoppublishing, |
---|
40 | A.ld_published, |
---|
41 | FOLD.ld_name, |
---|
42 | A.ld_folderid, |
---|
43 | A.ld_tgs tags, |
---|
44 | A.ld_templateid, |
---|
45 | C.ld_name, |
---|
46 | A.ld_tenantid, |
---|
47 | A.ld_docreftype, |
---|
48 | A.ld_stamped, |
---|
49 | A.ld_password, |
---|
50 | A.ld_workflowstatusdisp, |
---|
51 | A.ld_language, |
---|
52 | A.ld_pages, |
---|
53 | A.ld_color |
---|
54 | FROM ld_document A |
---|
55 | JOIN ld_folder FOLD |
---|
56 | ON A.ld_folderid = FOLD.ld_id |
---|
57 | LEFT OUTER JOIN ld_template C |
---|
58 | ON A.ld_templateid = C.ld_id, |
---|
59 | ld_document_ext C1 |
---|
60 | WHERE A.ld_deleted = 0 |
---|
61 | AND A.ld_nature = 0 |
---|
62 | AND NOT A.ld_status = 3 |
---|
63 | AND A.ld_folderid = FOLD.ld_id |
---|
64 | AND A.ld_tenantid = 1 |
---|
65 | AND A.ld_templateid = 102 |
---|
66 | AND A.ld_docref IS NULL |
---|
67 | AND ((( C1.ld_docid = A.ld_id |
---|
68 | AND ( C1.ld_name = 'Keyword' |
---|
69 | OR C1.ld_name LIKE 'Keyword-%' ) |
---|
70 | AND Lower(C1.ld_stringvalue) LIKE '%memento%' ))) |
---|
71 | UNION |
---|
72 | SELECT A.ld_id, |
---|
73 | REF.ld_customid, |
---|
74 | A.ld_docref, |
---|
75 | A.ld_type, |
---|
76 | REF.ld_version, |
---|
77 | REF.ld_lastmodified, |
---|
78 | REF.ld_date, |
---|
79 | REF.ld_publisher, |
---|
80 | REF.ld_creation, |
---|
81 | REF.ld_creator, |
---|
82 | REF.ld_filesize, |
---|
83 | REF.ld_immutable, |
---|
84 | REF.ld_indexed, |
---|
85 | REF.ld_lockuserid, |
---|
86 | A.ld_filename, |
---|
87 | REF.ld_status, |
---|
88 | REF.ld_signed, |
---|
89 | REF.ld_rating, |
---|
90 | REF.ld_fileversion, |
---|
91 | A.ld_comment, |
---|
92 | REF.ld_workflowstatus, |
---|
93 | REF.ld_startpublishing, |
---|
94 | REF.ld_stoppublishing, |
---|
95 | REF.ld_published, |
---|
96 | FOLD.ld_name, |
---|
97 | A.ld_folderid, |
---|
98 | REF.ld_tgs tags, |
---|
99 | REF.ld_templateid, |
---|
100 | C.ld_name, |
---|
101 | A.ld_tenantid, |
---|
102 | A.ld_docreftype, |
---|
103 | REF.ld_stamped, |
---|
104 | REF.ld_password, |
---|
105 | REF.ld_workflowstatusdisp, |
---|
106 | REF.ld_language, |
---|
107 | REF.ld_pages, |
---|
108 | REF.ld_color |
---|
109 | FROM ld_document A |
---|
110 | JOIN ld_folder FOLD |
---|
111 | ON A.ld_folderid = FOLD.ld_id |
---|
112 | JOIN ld_document REF |
---|
113 | ON A.ld_docref = REF.ld_id |
---|
114 | LEFT OUTER JOIN ld_template C |
---|
115 | ON REF.ld_templateid = C.ld_id, |
---|
116 | ld_document_ext C1 |
---|
117 | WHERE A.ld_deleted = 0 |
---|
118 | AND A.ld_nature = 0 |
---|
119 | AND NOT A.ld_status = 3 |
---|
120 | AND A.ld_folderid = FOLD.ld_id |
---|
121 | AND A.ld_tenantid = 1 |
---|
122 | AND REF.ld_templateid = 102 |
---|
123 | AND A.ld_docref IS NOT NULL |
---|
124 | AND REF.ld_deleted = 0 |
---|
125 | AND A.ld_docref = REF.ld_id |
---|
126 | AND ((( C1.ld_docid = REF.ld_id |
---|
127 | AND ( C1.ld_name = 'Keyword' |
---|
128 | OR C1.ld_name LIKE 'Keyword-%' ) |
---|
129 | AND Lower(C1.ld_stringvalue) LIKE '%memento%' ))) |
---|
130 | |
---|
131 | |
---|
132 | ---- |
---|
133 | 2) |
---|
134 | executing query |
---|
135 | SELECT A.ld_id, |
---|
136 | A.ld_customid, |
---|
137 | A.ld_docref, |
---|
138 | A.ld_type, |
---|
139 | A.ld_version, |
---|
140 | A.ld_lastmodified, |
---|
141 | A.ld_date, |
---|
142 | A.ld_publisher, |
---|
143 | A.ld_creation, |
---|
144 | A.ld_creator, |
---|
145 | A.ld_filesize, |
---|
146 | A.ld_immutable, |
---|
147 | A.ld_indexed, |
---|
148 | A.ld_lockuserid, |
---|
149 | A.ld_filename, |
---|
150 | A.ld_status, |
---|
151 | A.ld_signed, |
---|
152 | A.ld_rating, |
---|
153 | A.ld_fileversion, |
---|
154 | A.ld_comment, |
---|
155 | A.ld_workflowstatus, |
---|
156 | A.ld_startpublishing, |
---|
157 | A.ld_stoppublishing, |
---|
158 | A.ld_published, |
---|
159 | FOLD.ld_name, |
---|
160 | A.ld_folderid, |
---|
161 | A.ld_tgs tags, |
---|
162 | A.ld_templateid, |
---|
163 | C.ld_name, |
---|
164 | A.ld_tenantid, |
---|
165 | A.ld_docreftype, |
---|
166 | A.ld_stamped, |
---|
167 | A.ld_password, |
---|
168 | A.ld_workflowstatusdisp, |
---|
169 | A.ld_language, |
---|
170 | A.ld_pages, |
---|
171 | A.ld_color |
---|
172 | FROM ld_document A |
---|
173 | JOIN ld_folder FOLD |
---|
174 | ON A.ld_folderid = FOLD.ld_id |
---|
175 | LEFT OUTER JOIN ld_template C |
---|
176 | ON A.ld_templateid = C.ld_id, |
---|
177 | ld_document_ext C1 |
---|
178 | WHERE A.ld_deleted = 0 |
---|
179 | AND A.ld_nature = 0 |
---|
180 | AND NOT A.ld_status = 3 |
---|
181 | AND A.ld_folderid = FOLD.ld_id |
---|
182 | AND A.ld_tenantid = 1 |
---|
183 | AND A.ld_templateid = 102 |
---|
184 | AND A.ld_docref IS NULL |
---|
185 | AND ((( C1.ld_docid = A.ld_id |
---|
186 | AND ( C1.ld_name = 'Keyword' |
---|
187 | OR C1.ld_name LIKE 'Keyword-%' ) |
---|
188 | AND Lower(C1.ld_stringvalue) LIKE '%memento%' ))) |
---|
189 | |
---|
190 | |
---|
191 | ---------------------------------------------- |
---|
192 | |
---|
193 | 3) |
---|
194 | SELECT A.ld_id, |
---|
195 | A.ld_customid, |
---|
196 | A.ld_docref, |
---|
197 | A.ld_type, |
---|
198 | A.ld_version, |
---|
199 | A.ld_lastmodified, |
---|
200 | A.ld_date, |
---|
201 | A.ld_publisher, |
---|
202 | A.ld_creation, |
---|
203 | A.ld_creator, |
---|
204 | A.ld_filesize, |
---|
205 | A.ld_immutable, |
---|
206 | A.ld_indexed, |
---|
207 | A.ld_lockuserid, |
---|
208 | A.ld_filename, |
---|
209 | A.ld_status, |
---|
210 | A.ld_signed, |
---|
211 | A.ld_rating, |
---|
212 | A.ld_fileversion, |
---|
213 | A.ld_comment, |
---|
214 | A.ld_workflowstatus, |
---|
215 | A.ld_startpublishing, |
---|
216 | A.ld_stoppublishing, |
---|
217 | A.ld_published, |
---|
218 | FOLD.ld_name, |
---|
219 | A.ld_folderid, |
---|
220 | A.ld_tgs tags, |
---|
221 | A.ld_templateid, |
---|
222 | C.ld_name, |
---|
223 | A.ld_tenantid, |
---|
224 | A.ld_docreftype, |
---|
225 | A.ld_stamped, |
---|
226 | A.ld_password, |
---|
227 | A.ld_workflowstatusdisp, |
---|
228 | A.ld_language, |
---|
229 | A.ld_pages, |
---|
230 | A.ld_color |
---|
231 | FROM ld_document A |
---|
232 | JOIN ld_folder FOLD |
---|
233 | ON A.ld_folderid = FOLD.ld_id |
---|
234 | LEFT OUTER JOIN ld_template C |
---|
235 | ON A.ld_templateid = C.ld_id, |
---|
236 | ld_document_ext C1 |
---|
237 | WHERE A.ld_deleted = 0 |
---|
238 | AND A.ld_nature = 0 |
---|
239 | AND NOT A.ld_status = 3 |
---|
240 | AND A.ld_folderid = FOLD.ld_id |
---|
241 | AND A.ld_tenantid = 1 |
---|
242 | AND A.ld_templateid = 102 |
---|
243 | AND A.ld_docref IS NULL |
---|
244 | AND ((( C1.ld_docid = A.ld_id |
---|
245 | AND ( C1.ld_name = 'Keyword' |
---|
246 | OR C1.ld_name LIKE 'Keyword-%' ) |
---|
247 | AND C1.ld_stringvalue LIKE '%memento%' ))) |
---|
248 | |
---|
249 | ---------- |
---|
250 | Search by filename |
---|
251 | 1) From the form |
---|
252 | |
---|
253 | select A.ld_id, A.ld_customid, A.ld_docref, A.ld_type, A.ld_version, A.ld_lastmodified, A.ld_date, A.ld_publisher, A.ld_creation, A.ld_creator, A.ld_filesize, A.ld_immutable, A.ld_indexed, A.ld_lockuserid, A.ld_filename, A.ld_status, A.ld_signed, A.ld_rating, A.ld_fileversion, A.ld_comment, A.ld_workflowstatus, A.ld_startpublishing, A.ld_stoppublishing, A.ld_published, FOLD.ld_name, A.ld_folderid, A.ld_tgs tags, A.ld_templateid, C.ld_name, A.ld_tenantid, A.ld_docreftype, A.ld_stamped, A.ld_password, A.ld_workflowstatusdisp, A.ld_language, A.ld_pages, A.ld_color from ld_document A join ld_folder FOLD on A.ld_folderid=FOLD.ld_id left outer join ld_template C on A.ld_templateid=C.ld_id where A.ld_deleted=0 and A.ld_nature=0 and not A.ld_status = 3 and A.ld_folderid=FOLD.ld_id and A.ld_tenantid=1 and A.ld_docref is null and ( (lower(A.ld_filename) like '%something%')) UNION select A.ld_id, REF.ld_customid, A.ld_docref, A.ld_type, REF.ld_version, REF.ld_lastmodified, REF.ld_date, REF.ld_publisher, REF.ld_creation, REF.ld_creator, REF.ld_filesize, REF.ld_immutable, REF.ld_indexed, REF.ld_lockuserid, A.ld_filename, REF.ld_status, REF.ld_signed, REF.ld_rating, REF.ld_fileversion, A.ld_comment, REF.ld_workflowstatus, REF.ld_startpublishing, REF.ld_stoppublishing, REF.ld_published, FOLD.ld_name, A.ld_folderid, REF.ld_tgs tags, REF.ld_templateid, C.ld_name, A.ld_tenantid, A.ld_docreftype, REF.ld_stamped, REF.ld_password, REF.ld_workflowstatusdisp, REF.ld_language, REF.ld_pages, REF.ld_color from ld_document A join ld_folder FOLD on A.ld_folderid=FOLD.ld_id join ld_document REF on A.ld_docref=REF.ld_id left outer join ld_template C on REF.ld_templateid=C.ld_id where A.ld_deleted=0 and A.ld_nature=0 and not A.ld_status = 3 and A.ld_folderid=FOLD.ld_id and A.ld_tenantid=1 and A.ld_docref is not null and REF.ld_deleted=0 and A.ld_docref = REF.ld_id and ( (lower(REF.ld_filename) like '%something%')) |
---|
254 | |
---|
255 | 2) From the saved query: |
---|
256 | select A.ld_id, A.ld_customid, A.ld_docref, A.ld_type, A.ld_version, A.ld_lastmodified, A.ld_date, A.ld_publisher, A.ld_creation, A.ld_creator, A.ld_filesize, A.ld_immutable, A.ld_indexed, A.ld_lockuserid, A.ld_filename, A.ld_status, A.ld_signed, A.ld_rating, A.ld_fileversion, A.ld_comment, A.ld_workflowstatus, A.ld_startpublishing, A.ld_stoppublishing, A.ld_published, FOLD.ld_name, A.ld_folderid, A.ld_tgs tags, A.ld_templateid, C.ld_name, A.ld_tenantid, A.ld_docreftype, A.ld_stamped, A.ld_password, A.ld_workflowstatusdisp, A.ld_language, A.ld_pages, A.ld_color from ld_document A join ld_folder FOLD on A.ld_folderid=FOLD.ld_id left outer join ld_template C on A.ld_templateid=C.ld_id where A.ld_deleted=0 and A.ld_nature=0 and not A.ld_status = 3 and A.ld_folderid=FOLD.ld_id and A.ld_tenantid=1 and A.ld_docref is null and ( (A.ld_filename like '%something%')) |
---|
257 | |
---|
258 | 1) Takes in consideration the aliases and the lowercase |
---|
259 | 2) Execute a query without the options above |
---|