Common Table Expression Not Working in QlikView

[carlcimino]

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;

[vishsaggi · Accepted solution]

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]

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.