facing an error while executing postgresql services in thingworx 9.1

[Ru]

hii

i have one database(POSTGRESQL) configure with thingworx 9.1.

i have created postgresql thing which contain services(postgresql queries) for eg:-

i have one postgresql query which gives me allmembers data but it shows an error 

"select ch.tcr_id,concat(us.first_name,' ',us.last_name) as member_name,
ch.user_id,ch.checkin_time,ch.checkout_time,ch.band_id,ch.checkbox
from "tbl_checkin_records" as ch
join "tbl_members" as mem
on ch.user_id = mem.user_id
join "tbl_users" as us
on mem.user_id = us.user_id order by ch.checkout_time desc;"        this is my query which gives me an following error:- Unable to Invoke Service getAllCheckinRecordsForMember on PostgreSQL_DB : Execute Query failed: org.postgresql.util.PSQLException: ERROR: syntax error at or near "as" Position: 165

But when i was using 

i have attached snapshots below for your better understanding

[PaiChung]

You may be running into a known bug.

Please try adding an actual space between every statement vs. just using CR/LF

easy way to make sure you have done is to make it look like this

"select ch.tcr_id,concat(us.first_name,' ',us.last_name) as member_name,
 ch.user_id,ch.checkin_time,ch.checkout_time,ch.band_id,ch.checkbox
 from "tbl_checkin_records" as ch
 join "tbl_members" as mem
 on ch.user_id = mem.user_id
 join "tbl_users" as us
 on mem.user_id = us.user_id order by ch.checkout_time desc;"  

Not sure if you can tell but on each next line after your first I added a space in front.

[tdixit · Accepted solution]

Hello @Ru 

Thank you for contacting PTC. As stated by @PaiChung  this is a known bug in ThingWorx 9.1 and will be fixed in ThingWorx 9.1.1

Recommendation is to upgrade to ThingWorx 9.1.1 patch version. You can follow this article CS182505  to apply patch.

But , ThingWorx 9.1.1 is not released yet ,workaround is to remove line breaks or add space before follow-up line

• For example following query will return an error:

SELECT name 
FROM thing_model

• Following syntaxes can be used instead:

SELECT name FROM thing_model

SELECT name 
  FROM thing_model

Please feel free to reach out to us in case of any question/concern

Thanks & Regards,

Toolika Dixit

[slangley]

Hi @Ru.

If you feel your question has been answered, please mark it as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon

[slangley]

Hi @Ru.

ThingWorx 9.1.1 is now available for download on the support site.

If you feel your issue has been addressed, please mark the appropriate response as the Accepted Solution, for the benefit of others in the community.

Regards.

--Sharon