?
Solved

SQL For Customise Report

Posted on 2006-04-25
13
Medium Priority
?
356 Views
Last Modified: 2011-10-03
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


     




0
Comment
Question by:itgi
  • 8
  • 3
12 Comments
 

Author Comment

by:itgi
ID: 16541334
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
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16541946
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
 

Author Comment

by:itgi
ID: 16541982
IS THIS LINE IS OK  ???


CHECK.QTY AS CHECK, (FILING.QTY AS FILING, CREATION.AMOUNT + DISPATCH.AMOUNT + CHECK.AMOUNT + FILING.AMOUNT) AS AMOUNT
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:itgi
ID: 16541986
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
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16541990
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
 

Author Comment

by:itgi
ID: 16542006
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
 

Author Comment

by:itgi
ID: 16542041
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
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16542044
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
 

Author Comment

by:itgi
ID: 16542076
SAME ERROR ON

DISPATCH_T

0
 

Author Comment

by:itgi
ID: 16542256
kindly look into the matter, it is very urgent
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1350 total points
ID: 16543096
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
 

Author Comment

by:itgi
ID: 16739679
It help me a lot to solve my problem
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

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

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

Join & Ask a Question