Question Re: SQL function returning NULL value . in Technology Q&A

SQL function returning NULL value .

venkataramana_paidi — Mon, 03 Aug 2015 13:34:31 GMT

Hi ,

I have to capture the max date from the table and I am using the below function .

$G_LastUpdate = sql('Omega00', 'select max(LAST_UPDATE) from EMP_DIM');

print($G_LastUpdate);

But it is returning the NULL value instead of the value. I have tried different varchar field in the table but it is returning the value properly.

Please suggest what is wrong here . Why not it is working for datetime and working for varchar

I have tried in the SQL editor it is working fine . But It is not giving the DS script .

Thanks & Regards,

Venkata Ramana Paidi

Re: SQL function returning NULL value .

Former Member — Mon, 03 Aug 2015 13:53:15 GMT

Please check the connection of datastore !! It may be possbile your datastore is pointing to some other database!

Thanks,

Re: SQL function returning NULL value .

venkataramana_paidi — Mon, 03 Aug 2015 13:58:37 GMT

If I change the data type for $G_LastUpdate from datetime to date it is working. But I don't understand why it is not working for datetime datatype

Re: SQL function returning NULL value .

venkataramana_paidi — Mon, 03 Aug 2015 14:00:26 GMT

Hi Swapnil,

I checked the DEPARTMENT with max function it is working fine. I have tried by changing the variable data type to date from datetime it is working . What is the problem with the datetime data type here.

Thanks & Regards,

Ramana

Re: SQL function returning NULL value .

Former Member — Mon, 03 Aug 2015 14:13:02 GMT

Your backend database?

Re: SQL function returning NULL value .

venkataramana_paidi — Mon, 03 Aug 2015 14:16:31 GMT

Sybase ASE

Re: SQL function returning NULL value .

Former Member — Mon, 03 Aug 2015 14:21:05 GMT

There is a limitation with timestamp column.

I found it in SAP BODS technical manual 4.2..

Re: SQL function returning NULL value .

venkataramana_paidi — Mon, 03 Aug 2015 14:27:19 GMT

Thanks Swapnil, May be it wont working with Sybase ASE datetime data type .

Re: SQL function returning NULL value .

Former Member — Mon, 03 Aug 2015 14:32:12 GMT

No.. It is generalized limitation not specific to any database!!! For all database it would be same.

Re: SQL function returning NULL value .

Former Member — Mon, 03 Aug 2015 21:54:51 GMT

Have you tried this -

$G_LastUpdate = sql('Omega00', 'select to_char(max(LAST_UPDATE),\'DD-MM-YYYY\') from EMP_DIM');

$G_ConvertedDate = to_date($G_LastUpdate,'DD-MM-YYY');

Arun

Re: SQL function returning NULL value .

former_member187605 — Tue, 04 Aug 2015 06:54:44 GMT

To avoid this type of problems never use date(time) variables as results of a DS sql function (which actually returns varchar results only) and always convert your database date(time)s to varchar in the select statement. Both internal and external date(time) formats may differ significantly from one environment to another.

 
$G_LastUpdate = sql('Omega00', 'select to_char(max(LAST_UPDATE),\'DD-MM-YYYY\') from EMP_DIM'); 
$G_ConvertedDate = to_date($G_LastUpdate,'DD-MM-YYY');

To make this work, make sure to declare $G_LastUpdate as varchar(10) and $G_ConvertedDate as date.

Re: SQL function returning NULL value .

Former Member — Fri, 22 Apr 2016 12:53:02 GMT

Hi Venkata ramana paidi,

how did u achieve this , I am facing same problem with Sybase ASE in sap bods,I want to capture the date time in a variable like this format

2016.04.14 15:03:31.450000000

Please help me in that

Thanks,

Narendra