Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
carlcimino
Luminary
Luminary

Common Table Expression Not Working in QlikView

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;

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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;

View solution in original post

2 Replies
vishsaggi
Champion III
Champion III

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;

carlcimino
Luminary
Luminary
Author

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.