topic Re: Converting IF(isnull to SQL scripting in QlikView

Converting IF(isnull to SQL scripting

hopkinsc — Thu, 14 Mar 2019 10:08:16 GMT

Hi all,

I have the following where clause that is being used in an SQL SELECT statement..

Where TRUNC(INTERNALINFO_UPDATEDATE) = TRUNC(SYSDATE) OR TRUNC(INTERNALINFO_UPDATEDATE) = TRUNC(SYSDATE)-1

I need to change it as the INTERNALINFO_UPDATEDATE is not populated when a record is created, so i need to use an IF() statement to say if INTERNALINFO_UPDATEDATE is null then use INTERNALINFO_CREATIONDATE.

I have done the following, but it seems like i am now mixing QV and SQL scripting which is causing it to fail.

Where IF(ISNULL(TRUNC(INTERNALINFO_UPDATEDATE)),TRUNC(INTERNALINFO_CREATIONDATE),TRUNC(INTERNALINFO_UPDATEDATE)) = TRUNC(SYSDATE) OR IF(ISNULL(TRUNC(INTERNALINFO_UPDATEDATE)),TRUNC(INTERNALINFO_CREATIONDATE),TRUNC(INTERNALINFO_UPDATEDATE)) = TRUNC(SYSDATE)-1

Can anyone help me with getting a where clause written in SQL so i can use it within the SELECT statement please?

The purpose of this script is to only get new or updated records for the last 2 days (today and yesterday)

Thanks

Re: Converting IF(isnull to SQL scripting

vishsaggi — Thu, 14 Mar 2019 11:49:03 GMT

May be try this?
WHERE
CASE WHEN ISNULL(TRUNC(INTERNALINFO_UPDATEDATE)) THEN TRUNC(INTERNALINFO_CREATIONDATE)
ELSE
TRUNC(INTERNALINFO_UPDATEDATE) = TRUNC(SYSDATE)
End
OR
CASE WHEN ISNULL(TRUNC(INTERNALINFO_UPDATEDATE)) THEN TRUNC(INTERNALINFO_CREATIONDATE)
ELSE TRUNC(INTERNALINFO_UPDATEDATE) = TRUNC(SYSDATE)-1
End

Re: Converting IF(isnull to SQL scripting

hopkinsc — Thu, 14 Mar 2019 11:56:38 GMT

Thanks for your reply, unfortunately that is failing with the error..

My script is..

ProdTable:

SQL Select *

From DB_NAME."TABLENAME"

WHERE
CASE WHEN ISNULL(TRUNC(INTERNALINFO_UPDATEDATE)) THEN TRUNC(INTERNALINFO_CREATIONDATE)
ELSE
TRUNC(INTERNALINFO_UPDATEDATE) = TRUNC(SYSDATE)
End
OR
CASE WHEN ISNULL(TRUNC(INTERNALINFO_UPDATEDATE)) THEN TRUNC(INTERNALINFO_CREATIONDATE)
ELSE TRUNC(INTERNALINFO_UPDATEDATE) = TRUNC(SYSDATE)-1
End

;

any ideas what i am doing wrong?

Re: Converting IF(isnull to SQL scripting

marcus_sommer — Thu, 14 Mar 2019 13:13:31 GMT

Maybe you could apply the following approach:

TRUNC(if(len(INTERNALINFO_UPDATEDATE)>1, INTERNALINFO_UPDATEDATE, INTERNALINFO_CREATIONDATE))

- Marcus

Re: Converting IF(isnull to SQL scripting

hopkinsc — Thu, 14 Mar 2019 13:27:35 GMT

Hi Marcus,

I now have the following..

TRUNC(if(len(INTERNALINFO_UPDATEDATE)>1, TRUNC(INTERNALINFO_UPDATEDATE), TRUNC(INTERNALINFO_CREATIONDATE)))=TRUNC(SYSDATE)

TRUNC(if(len(INTERNALINFO_UPDATEDATE)>1, TRUNC(INTERNALINFO_UPDATEDATE), TRUNC(INTERNALINFO_CREATIONDATE)))=TRUNC(SYSDATE)-1

but i am getting an error as below..

Re: Converting IF(isnull to SQL scripting

marcus_sommer — Thu, 14 Mar 2019 13:36:40 GMT

It seems that len() isn't valid within Oracle - just try it with length().

- Marcus