DB2 V 6 view

A. J. Zobjeck

DB2 V 6 view
I'm on DB2 v6 on Z/os and have a SQL statement like the following:

SELECT *

FROM(
SELECT
FLT_TYP_CD AS FLT_TYP_CD1
,RPT_RD_ID AS RPT_ROAD
,DECIMAL(COUNT(*)) CNT,0 AS CNTB
FROM DB2TSYS.TFD_TRIP_SMRY
WHERE FLT_TYP_CD NOT IN ('H','C',' ')
AND TRIP_IN_PROG_IN = 'Y'
AND TRIP_EVNT_TYP_CD <> 'JCD'
GROUP BY FLT_TYP_CD,RPT_RD_ID) TOT
UNION
(SELECT
A.FLT_TYP_CD AS FLT_TYP_CD1
,A.TO_RR_IC AS RPT_ROAD
,0 AS CNT
,DECIMAL(COUNT(*)) CNTB
WHERE FLT_TYP_CD NOT IN ('H','C',' ')
AND TRIP_IN_PROG_IN = 'Y'
AND TRIP_EVNT_TYP_CD = 'JCD'
GROUP BY A.FLT_TYP_CD,TO_RR_IC)
ORDER BY FLT_TYP_CD1,RPT_ROAD
WITH UR;

And the results are as follows:

B 0. 16.
B AB 2. 0.
B ABR 0. 2.
B ABS 2. 0.
B ACJR 0. 1.
B ACJR 2. 0.
B AGNT 0. 1.
B AGR 25. 0.
B AKMD 0. 7.
B AKMD 3. 0.

Now I want to combine the counts for columns FLT_TYP_CD and RPT_Road and
add the counts into one result. So, I was thinking of a view and on
this and DB2 says it's invalid:

Create view DB2TSYS.VRS_TRP_COUNTS as
SELECT
FLT_TYP_CD AS FLT_TYP_CD1
,RPT_RD_ID AS RPT_ROAD
,DECIMAL(COUNT(*)) CNT,0 AS CNTB
FROM DB2TSYS.TFD_TRIP_SMRY
WHERE FLT_TYP_CD NOT IN ('H','C',' ')
AND TRIP_IN_PROG_IN = 'Y'
AND TRIP_EVNT_TYP_CD <> 'JCD'
GROUP BY FLT_TYP_CD,RPT_RD_ID
UNION
SELECT
A.FLT_TYP_CD AS FLT_TYP_CD1
,A.TO_RR_IC AS RPT_ROAD
,0 AS CNT
,DECIMAL(COUNT(*)) CNTB
FROM DB2TSYS.TFD_TRIP_SMRY A
WHERE FLT_TYP_CD NOT IN ('H','C',' ')
AND TRIP_IN_PROG_IN = 'Y'
AND TRIP_EVNT_TYP_CD = 'JCD'
GROUP BY A.FLT_TYP_CD,TO_RR_IC ;

DSNT408I SQLCODE = -154, ERROR: THE STATEMENT IS INVALID BECAUSE THE
VIEW OR TABLE DEFINITION IS NOT VALID
DSNT418I SQLSTATE = 42909 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOVD SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -120 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF88' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
RETCODE = 8

Any Ideas?
------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Moschelli Mauro

Re: DB2 V 6 view
(in response to A. J. Zobjeck)
Db2 V.6 does not support fullselect in create view, and UNION can be used only in fullselect. You need V7 to do that

HTH

Mauro Moschelli
SanPaoloIMI S.p.A.

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> Behalf Of Zobjeck, A. J.
> Sent: Monday, December 12, 2005 3:33 PM
> To: [login to unmask email]
> Subject: [DB2-L] DB2 V 6 view
>
>
> I'm on DB2 v6 on Z/os and have a SQL statement like the following:
>
> SELECT *
>
> FROM(
> SELECT
> FLT_TYP_CD AS FLT_TYP_CD1
> ,RPT_RD_ID AS RPT_ROAD
> ,DECIMAL(COUNT(*)) CNT,0 AS CNTB
> FROM DB2TSYS.TFD_TRIP_SMRY
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD <> 'JCD'
> GROUP BY FLT_TYP_CD,RPT_RD_ID) TOT
> UNION
> (SELECT
> A.FLT_TYP_CD AS FLT_TYP_CD1
> ,A.TO_RR_IC AS RPT_ROAD
> ,0 AS CNT
> ,DECIMAL(COUNT(*)) CNTB
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD = 'JCD'
> GROUP BY A.FLT_TYP_CD,TO_RR_IC)
> ORDER BY FLT_TYP_CD1,RPT_ROAD
> WITH UR;
>
> And the results are as follows:
>
> B 0. 16.
> B AB 2. 0.
> B ABR 0. 2.
> B ABS 2. 0.
> B ACJR 0. 1.
> B ACJR 2. 0.
> B AGNT 0. 1.
> B AGR 25. 0.
> B AKMD 0. 7.
> B AKMD 3. 0.
>
> Now I want to combine the counts for columns FLT_TYP_CD and
> RPT_Road and
> add the counts into one result. So, I was thinking of a view and on
> this and DB2 says it's invalid:
>
> Create view DB2TSYS.VRS_TRP_COUNTS as
> SELECT
> FLT_TYP_CD AS FLT_TYP_CD1
> ,RPT_RD_ID AS RPT_ROAD
> ,DECIMAL(COUNT(*)) CNT,0 AS CNTB
> FROM DB2TSYS.TFD_TRIP_SMRY
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD <> 'JCD'
> GROUP BY FLT_TYP_CD,RPT_RD_ID
> UNION
> SELECT
> A.FLT_TYP_CD AS FLT_TYP_CD1
> ,A.TO_RR_IC AS RPT_ROAD
> ,0 AS CNT
> ,DECIMAL(COUNT(*)) CNTB
> FROM DB2TSYS.TFD_TRIP_SMRY A
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD = 'JCD'
> GROUP BY A.FLT_TYP_CD,TO_RR_IC ;
>
> DSNT408I SQLCODE = -154, ERROR: THE STATEMENT IS INVALID BECAUSE THE
> VIEW OR TABLE DEFINITION IS NOT VALID
>
> DSNT418I SQLSTATE = 42909 SQLSTATE RETURN CODE
>
> DSNT415I SQLERRP = DSNXOVD SQL PROCEDURE DETECTING ERROR
>
> DSNT416I SQLERRD = -120 0 0 -1 0 0 SQL DIAGNOSTIC
> INFORMATION
> DSNT416I SQLERRD = X'FFFFFF88' X'00000000' X'00000000'
>
> X'FFFFFFFF' X'00000000' X'00000000' SQL
> DIAGNOSTIC
> INFORMATION
>
> BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
>
> RETCODE = 8
>
>
> Any Ideas?
> --------------------------------------------------------------
> ----------
> ---------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences
> at http://conferences.idug.org/index.cfm
>
> --------------------------------------------------------------
> -------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the
> archives and home page at
> http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


Il contenuto e gli allegati di questo messaggio sono strettamente
confidenziali, e ne sono vietati la diffusione e l'uso non autorizzato.

Le opinioni ivi eventualmente espresse sono quelle dell'autore: di
conseguenza il messaggio non costituisce impegno contrattuale tra
il Gruppo Sanpaolo ed il destinatario, e la banca non assume alcuna
responsabilita' riguardo ai contenuti del testo e dei relativi allegati,
ne' per eventuali intercettazioni, modifiche o danneggiamenti.

Qualora il presente messaggio Le fosse pervenuto per errore, Le saremmo
grati se lo distruggesse e, via e-mail, ce ne comunicasse l' errata
ricezione all'indirizzo [login to unmask email]


This e-mail (and any attachment(s)) is strictly confidential and for use
only by intended recipient(s). Any opinions therein expressed are those
of the author. Therefore its content doesn't represent any commitment
between Sanpaolo Group and the recipient(s) and no liability or
responsibility is accepted by Sanpaolo Group for the above mentioned
content.

Sanpaolo IMI S.p.A. is a Bank authorised by Banca d'Italia; Sanpaolo IMI
S.p.A., London Branch, is regulated by the Financial Services Authority
for the conduct of investment business in the UK.

If you are not an intended recipient(s), please notify
[login to unmask email] promptly and destroy this message.

James Campbell

Re: DB2 V 6 view
(in response to Moschelli Mauro)
Unions inside a view is one of the V7 enhancements -
http://publibz.boulder.ibm.com/cgi-
bin/bookmgr_OS390/BOOKS/DSNRGH14/4.3.1
V6 is now out of support.

James Campbell



On 12 Dec 2005 at 8:33, Zobjeck, A. J. wrote:

> I'm on DB2 v6 on Z/os and have a SQL statement like the following:
>
> SELECT *
>
> FROM(
> SELECT
> FLT_TYP_CD AS FLT_TYP_CD1
> ,RPT_RD_ID AS RPT_ROAD
> ,DECIMAL(COUNT(*)) CNT,0 AS CNTB
> FROM DB2TSYS.TFD_TRIP_SMRY
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD <> 'JCD'
> GROUP BY FLT_TYP_CD,RPT_RD_ID) TOT
> UNION
> (SELECT
> A.FLT_TYP_CD AS FLT_TYP_CD1
> ,A.TO_RR_IC AS RPT_ROAD
> ,0 AS CNT
> ,DECIMAL(COUNT(*)) CNTB
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD = 'JCD'
> GROUP BY A.FLT_TYP_CD,TO_RR_IC)
> ORDER BY FLT_TYP_CD1,RPT_ROAD
> WITH UR;
>
> And the results are as follows:
>
> B 0. 16.
> B AB 2. 0.
> B ABR 0. 2.
> B ABS 2. 0.
> B ACJR 0. 1.
> B ACJR 2. 0.
> B AGNT 0. 1.
> B AGR 25. 0.
> B AKMD 0. 7.
> B AKMD 3. 0.
>
> Now I want to combine the counts for columns FLT_TYP_CD and RPT_Road and
> add the counts into one result. So, I was thinking of a view and on
> this and DB2 says it's invalid:
>
> Create view DB2TSYS.VRS_TRP_COUNTS as
> SELECT
> FLT_TYP_CD AS FLT_TYP_CD1
> ,RPT_RD_ID AS RPT_ROAD
> ,DECIMAL(COUNT(*)) CNT,0 AS CNTB
> FROM DB2TSYS.TFD_TRIP_SMRY
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD <> 'JCD'
> GROUP BY FLT_TYP_CD,RPT_RD_ID
> UNION
> SELECT
> A.FLT_TYP_CD AS FLT_TYP_CD1
> ,A.TO_RR_IC AS RPT_ROAD
> ,0 AS CNT
> ,DECIMAL(COUNT(*)) CNTB
> FROM DB2TSYS.TFD_TRIP_SMRY A
> WHERE FLT_TYP_CD NOT IN ('H','C',' ')
> AND TRIP_IN_PROG_IN = 'Y'
> AND TRIP_EVNT_TYP_CD = 'JCD'
> GROUP BY A.FLT_TYP_CD,TO_RR_IC ;
>
> DSNT408I SQLCODE = -154, ERROR: THE STATEMENT IS INVALID BECAUSE THE
> VIEW OR TABLE DEFINITION IS NOT VALID
> DSNT418I SQLSTATE = 42909 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNXOVD SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = -120 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'FFFFFF88' X'00000000' X'00000000'
> X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
> INFORMATION
> BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
> RETCODE = 8
>
> Any Ideas?
> ------------------------------------------------------------------------

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm