Improve company productivity with a Business Account.Sign Up

x
Solved

DB2: V5R4M0: Create view and WITH statment

Posted on 2011-03-09
Medium Priority
797 Views
Last Modified: 2012-05-11
Is it possible use WITH statment in a create view statment?
because the code below doesn't work with WITH...

thanks

CREATE VIEW QS36F/MATERIAL1 AS
( WITH AAA AS ( SELECT HVARI2 AS MATERIAL
FROM QS36F/DDSH001
WHERE SUBSTRING(HVARI2, 1, 3) = '   ' AND TRIM(HVARI2)<>'' AND
HVARI1
IN (SELECT GRoUP1 FROM QS36F/SGRoup) GROUP BY HVARI2 )
SELECT AAA.*, MATDES.MATDES FROM AAA LEFT JOIN QS36F/MATDES ON
MATERIAL=MATERI
)
0
Question by:bobdylan75
• 3
• 2
• 2
• +1
8 Comments

LVL 46

Expert Comment

ID: 35083943
Hi Bob,

I'm not sure, but if it is you'll need to turn it around...

with AAA as (...)
CREATE VIEW myview ...

Kent
0

Author Comment

ID: 35083983
thank you but, it doesn't work in this way...
I tried 1 minute ago...
0

LVL 37

Accepted Solution

momi_sabag earned 2000 total points
ID: 35084042
why not

CREATE VIEW QS36F/MATERIAL1 AS
(

SELECT AAA.*, MATDES.MATDES FROM
( SELECT HVARI2 AS MATERIAL
FROM QS36F/DDSH001
WHERE SUBSTRING(HVARI2, 1, 3) = '   ' AND TRIM(HVARI2)<>'' AND
HVARI1 IN (SELECT GRoUP1 FROM QS36F/SGRoup) GROUP BY HVARI2 ) AAA
LEFT JOIN QS36F/MATDES ON
MATERIAL=MATERI
)
0

LVL 18

Expert Comment

ID: 35084046

Try removing the out parentheses.

e.g.

``````CREATE VIEW QS36F/MATERIAL1 AS
WITH AAA AS (
SELECT HVARI2 AS MATERIAL
FROM QS36F/DDSH001
WHERE SUBSTRING(HVARI2, 1, 3) = '   '
AND TRIM(HVARI2)<>''
AND HVARI1 IN (SELECT GRoUP1 FROM QS36F/SGRoup)
GROUP BY HVARI2
)
SELECT AAA.*,
MATDES.MATDES
FROM AAA
LEFT JOIN QS36F/MATDES
ON MATERIAL=MATERI
``````
0

LVL 46

Expert Comment

ID: 35084060
Ah... You're trying to put the CTE into the view.  (I thought that you were trying to define the columns from the CTE.)

I don't believe that this is supported.  You can still build a view over the joined tables but not with the CTE syntax.

CREATE VIEW QS36F/MATERIAL1 AS
SELECT AAA.*, MATDES.MATDES
FROM
(
SELECT HVARI2 AS MATERIAL
FROM QS36F/DDSH001
WHERE SUBSTRING(HVARI2, 1, 3) = '   '
AND TRIM(HVARI2)<>''
AND HVARI1 IN (SELECT GRoUP1 FROM QS36F/SGRoup)
GROUP BY HVARI2
) AAA
LEFT JOIN QS36F/MATDES
ON MATERIAL=MATERI

Kent
0

LVL 18

Expert Comment

ID: 35084081
Putting a CTE in a view works beautifully for me:

``````create view delthis as
with ndm as (
select *
from member
where deleteRequest = 'N'
)
select *
from ndm
``````
0

LVL 46

Expert Comment

ID: 35084138
Wow!

I learned something again today.  I had no idea that the iSeries had moved that far into the present.  :)

Thanks, Dave!

Kent
0

Author Closing Comment

ID: 35084230
THANK YOU EVERYBODY!
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…
Suggested Courses
Course of the Month8 days, 10 hours left to enroll

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.