|
View previous topic :: View next topic
|
| Author |
Message |
sree reddy
New User
Joined: 20 Jul 2007
Posts: 27
Location: bangalore
|
|
|
|
| Code: |
SELECT 'SELECT * FROM FEPG2.'
||'R'
||SUBSTR(RTRIM(CHAR(YEAR(CURRENT DATE))),3,2)
||'_CHG_AUX_FACT'
||MONTH1
FROM
(SELECT CASE WHEN MONTH(CURRENT DATE) < 10
THEN '0'||RTRIM(CHAR(MONTH(CURRENT DATE)))
ELSE
RTRIM(CHAR(MONTH(CURRENT DATE)))
END MONTH1
FROM SYSIBM.SYSDUMMY1
) A |
The above sQL creates SELECT * FROM FEPG2.R11_CHG_AUX_FACT06
My Question/request
I am using the above sql for creating dynamic table name because the Year and month changes e the table name changes every onth and the view name remains same
i want to create view from the above sql
i tried create view viewname as (the above sql)
i am getting error
| Code: |
DSNT408I SQLCODE = -153, ERROR: THE STATEMENT IS INVALID BECAUSE THE VIEW OR
TABLE DEFINITION DOES NOT INCLUDE A UNIQUE NAME FOR EACH COLUMN
DSNT418I SQLSTATE = 42908 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOVD SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF7E' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+ |
Please suggest me how to make this work
I want to create View |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Is it not the case that the SQLCODE indicates that there is a problem with the view definition, not with the query? |
|
| Back to top |
|
|
sree reddy
New User
Joined: 20 Jul 2007
Posts: 27
Location: bangalore
|
|
|
|
i am not warred about the error i need solution how to create a view based on the above SQL
Please help me |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
My guess:
You receive this error because you're creating the view on the original SELECT, not on the resulting row.
Please show how you build the CREATE VIEW statement. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| sree reddy wrote: |
| i am not warred [sic] about the error |
Oh? Although I don't have access to a mainframe today, the message
| Code: |
THE STATEMENT IS INVALID BECAUSE THE VIEW OR
TABLE DEFINITION DOES NOT INCLUDE A UNIQUE NAME FOR EACH COLUMN |
suggests that your problem is pretty obvious. Perhaps GuyC or another SQL maven could resolve this impasse for us. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| i am not warred [sic] about the error |
Until you do become "worried" about the errors actually presented to you by the system, i don't know how we can help. Just humour us and explain why there need be no concern for this error.
Explain what the system is telling you that you should be able t ignore. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Today's your lucky day...
| Code: |
//*---------------------------------------------------
//* CREATE 'CREATE VIEW' ROW
//*---------------------------------------------------
//STEP1 EXEC PGM=SYNCSORT,PARM='DB2=DSN'
//SYSOUT DD SYSOUT=*
//SORTDBIN DD *
SELECT CAST (
'CREATE VIEW XXYYZZ AS (SELECT * FROM FEPG2.'
||'R'
||SUBSTR(CHAR(CURRENT DATE),9,2)
||'_CHG_AUX_FACT'
||SUBSTR(CHAR(CURRENT DATE),4,2) || ')'
AS CHAR(80) )
FROM SYSIBM.SYSDUMMY1
//SORTOUT DD DISP=(NEW,PASS),DSN=&TEMPCR
//SYSIN DD *
SORT FIELDS=COPY
/*
//*---------------------------------------------------
//* RUN 'CREATE VIEW'
//*---------------------------------------------------
//STEP2 EXEC DSNTEP2,SYSTEM=DSN
//SYSIN DD DISP=(OLD,DELETE),DSN=&TEMPCR |
Note 1: I used SYNCSORT, you can use whatever tool you have to generate the create view row.
Note 2: I use a proc to run DSNTEP2, use your own convention.
Note 3: for me, current date is in european format (DD.MM.YYYY). |
|
| Back to top |
|
|
|
|
