topic Re: Common Table Expression Not Working in QlikView in QlikView

Common Table Expression Not Working in QlikView

carlcimino — Fri, 09 Dec 2016 20:31:43 GMT

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;

Re: Common Table Expression Not Working in QlikView

vishsaggi — Fri, 09 Dec 2016 20:50:40 GMT

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;

Re: Common Table Expression Not Working in QlikView

carlcimino — Fri, 09 Dec 2016 21:42:42 GMT

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.