Converting IF(isnull to SQL scripting

[hopkinsc]

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

[vishsaggi]

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

[hopkinsc]

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?

[marcus_sommer]

Maybe you could apply the following approach:

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

- Marcus

[hopkinsc]

Hi Marcus,

I now have the following..

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

OR

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

but i am getting an error as below..

[marcus_sommer]

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

- Marcus