Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a common table expression that works in Oracle SQL Developer but does not work when I enter it in the QlickView application. I have a feeling it is something simple but any help would be appreciated.
Thanks,
Carl
here's the oracle sql developer script that works fine in Oracle:
With CTE_GETMAXDATE
as
(SELECT trim(upper(USERID_C)) USER_ID
,MAX(ROW_EXP_D) mdate
FROM DBCMLT.CLTRF031_HR_LDAP
group by USERID_C
)
SELECT trim(upper(S.USERID_C)) USER_ID , S.EMP_FULL_NA USER_NAME
FROM DBCMLT.CLTRF031_HR_LDAP S
inner join CTE_GETMAXDATE M on trim(upper(S.USERID_C))= M.USER_ID and S.ROW_EXP_D = mdate;
Here's what I have in the QlickView script editor that throws an ORA-00904: : invalid identifier error:
load *;
ODBC CONNECT TO [ccgesgp;DBQ=ccgesgp] (XUserId is ANTFeKNGUBMADFVGKH, XPassword is TTGeVaNGUBMADVVGHG);
UserID:
SQL With CTE_GETMAXDATE
(SELECT trim(upper(USERID_C)) USER_ID
,MAX(ROW_EXP_D) mdate
FROM DBCMLT.CLTRF031_HR_LDAP
group by USERID_C
)
SELECT trim(upper(S.USERID_C)) USER_ID , S.EMP_FULL_NA USER_NAME
FROM DBCMLT.CLTRF031_HR_LDAP S
inner join CTE_GETMAXDATE M on trim(upper(S.USERID_C))= M.USER_ID and S.ROW_EXP_D = mdate;
Can you send a snapshot of your error? And before that can you use Preceding Load like:
UserID:
LOAD *;
SQL With CTE_GETMAXDATE
(SELECT trim(upper(USERID_C)) USER_ID
,MAX(ROW_EXP_D) mdate
FROM DBCMLT.CLTRF031_HR_LDAP
group by USERID_C
)
SELECT trim(upper(S.USERID_C)) USER_ID , S.EMP_FULL_NA USER_NAME
FROM DBCMLT.CLTRF031_HR_LDAP S
inner join CTE_GETMAXDATE M on trim(upper(S.USERID_C))= M.USER_ID and S.ROW_EXP_D = mdate;
Can you send a snapshot of your error? And before that can you use Preceding Load like:
UserID:
LOAD *;
SQL With CTE_GETMAXDATE
(SELECT trim(upper(USERID_C)) USER_ID
,MAX(ROW_EXP_D) mdate
FROM DBCMLT.CLTRF031_HR_LDAP
group by USERID_C
)
SELECT trim(upper(S.USERID_C)) USER_ID , S.EMP_FULL_NA USER_NAME
FROM DBCMLT.CLTRF031_HR_LDAP S
inner join CTE_GETMAXDATE M on trim(upper(S.USERID_C))= M.USER_ID and S.ROW_EXP_D = mdate;
Thanks Vishwarath I just tried to take this down. It was my load script. I had additional select command in there. Just as I thought something simple. Sorry to take up your time.