Skip to main content
向
向郭_011-Visitor
1-Visitor
February 11, 2017
Solved

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

  • February 11, 2017
  • 1 reply
  • 21375 views

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.


Best answer by 向郭_01

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.

1 reply

A
ankigupta5-Regular Member
5-Regular Member
February 13, 2017

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

向
向郭_011-VisitorAuthor
1-Visitor
February 13, 2017

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.

M
mlohokare5-Regular Member
5-Regular Member
February 13, 2017

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

Terms & ConditionsAccessibility statement