SQL For Customise Report

Hi,

I am having data using a SQL :

SELECT A.USERID as EMP ,B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT
FROM ITGDTA.ZPLD A, ITGDTA.DESCPF  B
WHERE
A.ZSTATECD=B.DESCITEM AND B.DESCTABL= 'TJ824' and A.validflag = '1' and A.zsbucode = '16'
GROUP BY  B.LONGDESC, A.USERID ORDER BY A.USERID

data is coming in that format

EMP                          STAGE                                      QNT          AMOUNT
ANMAKUMAR       APolicy Creation                    2      2210.00      
ANMAKUMAR       GPolicy Dispatch                    78      198931.00      
ANMAKUMAR       CManual Quality Check               7      14235.00      
ANMATULYA       GPolicy Dispatch                    97      1513784.00      
ANMATULYA       CManual Quality Check               32      616308.00      
ANMATULYA       ZPolicy Filing                      3      266384.00      


i WANNT IT IN THIS FORMAT

EMP                   CREATION    DISPATCH   QUALITY CHECK  FILING  AMOUNT
ANMAKUMAR            2                78                    7               -           215376
ANMATULYA             -                 97                    3              3         2396476

ANY ONE SUGGEST ME WHAT TO CHANGE IN MY SQL

MY DATABASE IS DB2 AND I WANT TO DISPALY RESULT IN A JAVA PROGRAM


     




itgiAsked:
Who is Participating?
 
LowfatspreadCommented:
you basically want to pivot the results

like this...

Select Emp
      ,sum(Case Stage when 'GPolicy Creation' then qty else null end) as Creation
      ,sum(Case Stage when 'GPolicy Dispatch' then qty else null end) as Dispatch
      ,sum(Case Stage when 'CManual Quality Check' then qty else null end) as QualityCheck
      ,Sum(Amount) as Amount
 From (
SELECT A.USERID as EMP ,B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT
  FROM ITGDTA.ZPLD as A
 Inner Join ITGDTA.DESCPF as B
    on A.ZSTATECD=B.DESCITEM
 Where B.DESCTABL= 'TJ824'
   and A.validflag = '1'
   and A.zsbucode = '16'
 GROUP BY  B.LONGDESC, A.USERID
   ) as X
 Group by Userid
 ORDER BY USERID



of course  you could do it directly in the one select  by targeting the select against the longdesc ...
0
 
itgiAuthor Commented:
EMP                          STAGE                          NT          AMOUNT
ANMAKUMAR      APolicy Creation                     2            2210.00    
ANMAKUMAR      GPolicy Dispatch                   78         198931.00    
ANMAKUMAR      CManual Quality Check           7           14235.00    
ANMATULYA      GPolicy Dispatch                    97        1513784.00    
ANMATULYA      CManual Quality Check           32         616308.00    
ANMATULYA      ZPolicy Filing                          3          266384.00    


i WANNT IT IN THIS FORMAT

EMP                   CREATION    DISPATCH   QUALITY CHECK  FILING  AMOUNT
ANMAKUMAR            2                78                    7               -           215376
ANMATULYA             -                 97                   32             3         2396476
0
 
sachinwadhwaCommented:
following is just an example how to achieve this, this may not be accurate as I don't have your table structure...

still, try this (this is a single query!!) :


SELECT COALESCE(CREATION.EMP, DISPATCH.EMP) AS EMP, CREATION.QTY AS CREATION, DISPATCH.QTY AS DISPATCH, CHECK.QTY AS CHECK, (FILING.QTY AS FILING, CREATION.AMOUNT + DISPATCH.AMOUNT + CHECK.AMOUNT + FILING.AMOUNT) AS AMOUNT

FROM

(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'APolicy Creation' GROUP BY B.LONGDESC, A.USERID) CREATION

FULL OUTER JOIN
(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'GPolicy Dispatch' GROUP BY B.LONGDESC, A.USERID) DISPATCH ON CREATION.USERID = DISPATCH.USERID

FULL OUTER JOIN
(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'CManual Quality Check' GROUP BY B.LONGDESC, A.USERID) CHECK ON CREATION.USERID = CHECK.USERID

(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'ZPolicy Filing' GROUP BY B.LONGDESC, A.USERID) FILING ON CREATION.USERID = FILING.USERID



NB: 250 points to this query is too low...!!
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
itgiAuthor Commented:
IS THIS LINE IS OK  ???


CHECK.QTY AS CHECK, (FILING.QTY AS FILING, CREATION.AMOUNT + DISPATCH.AMOUNT + CHECK.AMOUNT + FILING.AMOUNT) AS AMOUNT
0
 
itgiAuthor Commented:
SOME PROBLEM IN THIS SECTION

SELECT COALESCE(CREATION.EMP, DISPATCH.EMP) AS EMP, CREATION.QTY AS CREATION, DISPATCH.QTY AS DISPATCH, CHECK.QTY AS CHECK, (FILING.QTY AS FILING, CREATION.AMOUNT + DISPATCH.AMOUNT + CHECK.AMOUNT + FILING.AMOUNT) AS AMOUNT

FROM

SQL NOT RUNNING
0
 
sachinwadhwaCommented:
it should be:

CHECK.QTY AS CHECK, FILING.QTY AS FILING, (CREATION.AMOUNT + DISPATCH.AMOUNT + CHECK.AMOUNT + FILING.AMOUNT) AS AMOUNT

oops..bracket at wrong place..
0
 
itgiAuthor Commented:
ERROR AFTER RECTIFICATION

Message: [SQL5001] Column qualifier or table DISPATCH undefined. Cause . . . . . :   Name DISPATCH was used to qualify a column name or was specified

HIGHLIGHT AT

+ DISPATCH.AMOUNT
0
 
itgiAuthor Commented:
COPMLETE LOG IS :::

SQL State: 42703
Vendor Code: -5001
Message: [SQL5001] Column qualifier or table DISPATCH undefined. Cause . . . . . :   Name DISPATCH was used to qualify a column name or was specified as the operand of the RRN, PARTITION, NODENAME, NODENUMBER, DBPARTITIONNAME, DBPARTITIONNUM, DATAPARTITIONNAME, or DATAPARTITIONNUM scalar function.  The name is not defined to be a table designator in this SQL statement or the table designator cannot be referenced where it is specified in the SQL statement. If a correlation name is specified following the table name in a FROM clause, the correlation name is considered to be the table designator.  If a correlation name is not specified, the table name is considered to be the table designator.  If using SQL naming and the table is qualified with authorization name, then the table designator is authorization-name.table-name.  If the authorization name is not specified, the table designator is the implicit authorization name followed by the table name. Correlation from a nested table expression to a higher level table is only allowed if the TABLE keyword is used for the definition of the nested table expression. If the name is *N, a lateral correlation reference from a nested table expression is not allowed.  You can not correlate to a table at a higher level than the nested table expression for one of the following reasons: -- The nested table expression contains a UNION, EXCEPT, or INTERSECT. -- The nested table expression uses the DISTINCT keyword in the SELECT clause. -- The nested table expression contains an ORDER BY clause. -- The correlated provider is in the same FROM clause as the nested table expression but is part of a RIGHT OUTER JOIN or RIGHT EXCEPTION JOIN. -- The nested table expression is in the FROM clause of another nested table expression that contains one of these restrictions. Recovery  . . . :   Ensure all column names are qualified with a valid table designator.  Make sure a table designator is specified as the argument to the function.  Use the TABLE keyword to allow correlated columns within a nested table expression. Try the request again.
0
 
sachinwadhwaCommented:
try this..

SELECT COALESCE(CREATION_T.EMP, DISPATCH_T.EMP) AS EMP, CREATION_T.QTY AS CREATION, DISPATCH_T.QTY AS DISPATCH, CHECK_T.QTY AS CHECK, FILING_T.QTY AS FILING, (CREATION_T.AMOUNT + DISPATCH_T.AMOUNT + CHECK_T.AMOUNT + FILING_T.AMOUNT) AS AMOUNT

FROM

(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'APolicy Creation' GROUP BY B.LONGDESC, A.USERID) CREATION_T

FULL OUTER JOIN
(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'GPolicy Dispatch' GROUP BY B.LONGDESC, A.USERID) DISPATCH_T ON CREATION_T.USERID = DISPATCH_T.USERID

FULL OUTER JOIN
(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'CManual Quality Check' GROUP BY B.LONGDESC, A.USERID) CHECK_T ON CREATION_T.USERID = CHECK_T.USERID

(SELECT   A.USERID AS EMP, B.LONGDESC AS STAGE, COUNT(B.LONGDESC) AS QTY, SUM(A.ZPLAMOUNT) AS AMOUNT FROM  ITGDTA.ZPLD A, ITGDTA.DESCPF B
WHERE    A.ZSTATECD = B.DESCITEM AND B.DESCTABL = 'TJ824' AND A.VALIDFLAG = '1' AND A.ZSBUCODE = '16' AND B.LONGDESC= 'ZPolicy Filing' GROUP BY B.LONGDESC, A.USERID) FILING_T ON CREATION_T.USERID = FILING_T.USERID
0
 
itgiAuthor Commented:
SAME ERROR ON

DISPATCH_T

0
 
itgiAuthor Commented:
kindly look into the matter, it is very urgent
0
 
itgiAuthor Commented:
It help me a lot to solve my problem
0
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.

All Courses

From novice to tech pro — start learning today.