Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

MQT using recursive SQL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sailaja cherla

New User


Joined: 08 Oct 2007
Posts: 13
Location: India

PostPosted: Mon May 03, 2010 4:23 pm    Post subject: MQT using recursive SQL
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon May 03, 2010 5:31 pm    Post subject:
Reply with quote

line 2 of your sql is the problem.
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Mon May 03, 2010 5:32 pm    Post subject:
Reply with quote

this link might be useful.
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPJ10/APPENDIX1.5?DT=20040210163115#HDRQAPPENZ
Back to top
View user's profile Send private message
sailaja cherla

New User


Joined: 08 Oct 2007
Posts: 13
Location: India

PostPosted: Mon May 03, 2010 5:47 pm    Post subject: Reply to: MQT using recursive SQL
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon May 03, 2010 8:47 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sailaja cherla

New User


Joined: 08 Oct 2007
Posts: 13
Location: India

PostPosted: Tue May 04, 2010 12:15 pm    Post subject: Reply to: MQT using recursive SQL
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Recursive execution of Jcl for specif... Ed Goodman JCL & VSAM 7 Thu Mar 06, 2014 1:49 am
No new posts Generating test Data by Recursive SQL Jimy Carol DB2 2 Thu Dec 26, 2013 3:00 pm
No new posts Problem in recursive SQL Learncoholic DB2 6 Wed Nov 21, 2012 8:59 pm
No new posts Recursive query help needed GuyC DB2 9 Mon Feb 27, 2012 7:35 pm
No new posts Recursive clist error while passing d... neerajpeddu TSO/ISPF 7 Thu Apr 09, 2009 12:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us