The column index is out of range: 1, number of columns: 0

[向郭]

Dear everyone,

I stucked with the problem that input variable could not put into sql statement,Thingworx give error "

The column index is out of range: 1, number of columns: 0

"

my sql statement like:

select EXTRACT(MONTH from TIMESTAMP '[[ddd]]');   //ddd is the input variable  ,Datetime type

if I put the ddd with '2016-12-01 08:40:00', thingworx will give error.

thank you in advance.

[ankigupta]

Hi Xiang Jing Guo​,

I hope ddd is the input variable.

Try following query:

select EXTRACT(MONTH from TIMESTAMP [[ddd]]);

input variable should not be in quotes.

It works fine for me.

I hope it helps.

Thanks,

Ankit Gupta

[向郭]

Dear  Ankit,

I tryed the query you mentioned above,but get the following error:

ERROR: syntax error at or near "$1" .postion 37.

I use the postgresql as the database .

Thanks anyway.

[mlohokare]

Hi Xiang Jing Guo,

Have you tried with adding "Date/Time stamp" pattern in your query?

Like 'dd-mm-yyyy, yyyy-mm-dd' etc.

In my scenario, I used as suggested by Ankit and added specific pattern and it works,

select * from "FOO"

where

to_date(START_DATE)

BETWEEN

to_date([[StartDate]], 'dd-mm-yyyy')

AND

to_date([[EndDate]], 'dd-mm-yyyy')

I will suggest, first try to run the query which you are looking for on your database and then replicate it on ThingWorx.

Hope it helps.

Regards,

Mahesh Lohokare

[向郭 · Accepted solution]

Thank you Mahesh ,I have solved the problem,just change the query into "select EXTRACT(MONTH from [[ddd]]::TIMESTAMP);" 。

I don't know why , just keep trying.

[ttielebein]

Hey, if one of these answers helped you, do you think you could mark it as correct so other community users can more easily resolve similar issues? Thanks!

[向郭]

yes Tori,but I didn't find where to mark .