topic Re: pgSQL applied to query definition in ArGIS in Geodatabase Questions

pgSQL applied to query definition in ArGIS

XiHan — Wed, 07 Jun 2017 15:55:56 GMT

I was using the following code to query out the record in ArcMap. when I used verify button for the code, it returned error. Anybody can tell me why?

replace(split_part( image_path_filename, '_', 5), '.pdf', '')::timestamp > CURRENT_TIMESTAMP - INTERVAL '545 day' and split_part( image_path_filename, '_', 5) not like 'sn-%' and split_part( image_path_filename , '_', 5) <> ' ' and maptype = 'Appraisal'

Thanks,

Re: pgSQL applied to query definition in ArGIS

Asrujit_SenGupta — Wed, 07 Jun 2017 16:57:57 GMT

Where are you trying to use that query?

Can you attach a screenshot?

Re: pgSQL applied to query definition in ArGIS

XiHan — Thu, 08 Jun 2017 21:09:15 GMT

Thanks for the reply. The script above itself ( replace(split_part( image_path_filename, '_', 5), '.pdf', '')::timestamp > CURRENT_TIMESTAMP - INTERVAL '545 day' and split_part( image_path_filename, '_', 5) not like 'sn-%' and split_part( image_path_filename , '_', 5) <> ' ' and maptype = 'Appraisal')

is working but when it combined with other condition it returned an error as below. Basically purpose of this script is to query Appraisal maps under a condition that the map date is earlier than 18 months from now. But the date info is not directly given is in a field with the record format of " //sv04docifs5/data5/vol2/records/map_sets/s-map/00840/s_00840_008_2016-11-08.pdf". That is why I had to extract the date info firstly. With this condition I also tried to query maptype = 'Bridge' OR maptype = 'Record' OR maptype = 'Survey' and it returned the following error.

(replace(split_part( image_path_filename, '_', 5), '.pdf', '')::timestamp > CURRENT_TIMESTAMP - INTERVAL '545 day'
and split_part( image_path_filename, '_', 5) not like 'sn-%' and split_part( image_path_filename , '_', 5) <> '' and maptype = 'Appraisal') or maptype = 'Bridge' OR maptype = 'Record' OR maptype = 'Survey'