Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Maybe you could apply the following approach:
TRUNC(if(len(INTERNALINFO_UPDATEDATE)>1, INTERNALINFO_UPDATEDATE, INTERNALINFO_CREATIONDATE))
- Marcus
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..
It seems that len() isn't valid within Oracle - just try it with length().
- Marcus