|
View previous topic :: View next topic
|
| Author |
Message |
sailaja cherla
New User
Joined: 08 Oct 2007
Posts: 13
Location: India
|
|
|
|
Hi Team,
Can you let me know if we can create an MQT using recursive SQL.
I am getting the following error message when trying to one
SQL0199N The use of the reserved word "WITH" following "" is not valid.
Expected tokens may include: "SELECT (". SQLSTATE=42601 |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| line 2 of your sql is the problem. |
|
| Back to top |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
| Back to top |
|
|
sailaja cherla
New User
Joined: 08 Oct 2007
Posts: 13
Location: India
|
|
|
|
I am using the following SQL
| Code: |
create table gbst_team_member_mqt as (
WITH TEMP1(OPP_KEY,EMP_NOTES_ID,ALL_EMP,CNT) AS (
SELECT A.OPP_KEY,
A.EMP_NOTES_ID,
VARCHAR(A.EMP_NOTES_ID, 2000),
SMALLINT(1)
FROM (SELECT MIN(OPP_KEY) AS OPP_KEY,
MIN(EMP_NOTES_ID) AS EMP_NOTES_ID
FROM TEAM A
GROUP BY OPP_KEY) A
UNION ALL
SELECT A.OPP_KEY,
A.EMP_NOTES_ID,
B.ALL_EMP || ';' || A.EMP_NOTES_ID,
SMALLINT(B.CNT + 1)
FROM TEMP1 B,
TEAM A
WHERE A.OPP_KEY = B.OPP_KEY
AND A.EMP_NOTES_ID > B.EMP_NOTES_ID
AND A.EMP_NOTES_ID = (SELECT MIN(EMP_NOTES_ID)
FROM TEAM C
WHERE C.OPP_KEY = B.OPP_KEY
AND C.EMP_NOTES_ID > B.EMP_NOTES_ID))
SELECT F.OPP_KEY, ALL_EMP
FROM TEMP1 D,
TEAM F
WHERE CNT = (SELECT MAX(CNT)
FROM TEMP1 E
WHERE E.OPP_KEY = D.OPP_KEY)
AND F.OPP_KEY = D.OPP_KEY)
DATA INITIALLY DEFERRED REFRESH DEFERRED; |
Any problem with this SQL? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
i don't think you can do with in a subselect.
Maybe this is allowed :
| Code: |
with temp1() as (select ...)
create table xx_mqt as (select ...) |
Have you tried this :
| Code: |
select F.OPP_KEY, replace(replace(xml2clob(xmlagg(xmlelement(NAME a, EMP_NOTES_ID))),'<A>',''),'</A>',';')
FROM (select opp_key,emp_notes_id from team order by opp_key, MP_NOTES_ID ) f
GROUP BY F.OPP_KEY |
another way of concatenating columns, but I'm not sure the order of EMP_NOTES_ID is assured. |
|
| Back to top |
|
|
sailaja cherla
New User
Joined: 08 Oct 2007
Posts: 13
Location: India
|
|
|
|
Thank you so much for your response, though the MQT creation part did not work out, your other solution of using XML functions served my purpose.. thanks for that.
Still I am interested to know how we can create an MQT using recurcive SQL. |
|
| Back to top |
|
|
|
|
