Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQL qery for the below Scenario

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
a b c d

New User


Joined: 04 Mar 2010
Posts: 13
Location: UK

PostPosted: Fri Jul 16, 2010 4:13 pm    Post subject: SQL qery for the below Scenario
Reply with quote

Hi All,

My DB2 table looks as follows
Code:
Table T1
Col A   Col B   Col C
aaa     S1      100
bbb     S3      0
bbb             0
ccc     S2      50
ddd     P1      50
ddd             0


I need to report only those record where 1. Sum of the col C must be less than 100 or 2. Col B are spaces

Note - 1. For Sum of col C, I need to consider all records where col A are same. i.e for aaa sum=100, bbb=(0+0) = 0, ccc=50, ddd=(50+0)=50
2 - For Col B = spaces, I have to consider either off must be blank

In the above table except aaa record all other must be picked up
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Fri Jul 16, 2010 4:19 pm    Post subject:
Reply with quote

Yes, and ................. is there a question in here somewhere or is this just a hope that you get a free solution.
Please show what you have already tried and the problems arising.

Also, learn to use the code tags as shown below
Code:
 [code] your
code     
   goes
       here  [/code]
Back to top
View user's profile Send private message
a b c d

New User


Joined: 04 Mar 2010
Posts: 13
Location: UK

PostPosted: Fri Jul 16, 2010 4:23 pm    Post subject:
Reply with quote

I tried with group by with no luck.....

I am looking for a query for the above scenario
Back to top
View user's profile Send private message
a b c d

New User


Joined: 04 Mar 2010
Posts: 13
Location: UK

PostPosted: Fri Jul 16, 2010 4:36 pm    Post subject:
Reply with quote

Hello Moderator,

How to put the code in code tag?????????
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Jul 16, 2010 4:57 pm    Post subject:
Reply with quote

Here is an example from a db where the order_no has multiple line items,
each having a different prodkey. (I used 0, since prodkey is an integer)
to act like your column B = space.
price is amout, and i chose 6000 because that was convenient for my testing.

Code:

SELECT ORDER_NO
      ,sum(PRICE)
FROM AROMA.LINE_ITEMS P
WHERE EXISTS (SELECT ORDER_NO
              FROM AROMA.LINE_ITEMS S
              WHERE P.ORDER_NO = S.ORDER_NO
                AND S.PRODKEY = 0)
group by order_no HAVING SUM(PRICE) < 6000;


the CODE part of BBcode is explained here
Back to top
View user's profile Send private message
a b c d

New User


Joined: 04 Mar 2010
Posts: 13
Location: UK

PostPosted: Mon Jul 19, 2010 10:30 pm    Post subject:
Reply with quote

Thanks a lot Dick.....
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Assistance required for WHEN=GROUP sc... thirumalasetty JCL & VSAM 8 Wed Sep 10, 2014 12:56 am
No new posts OUTREC S0C7 issue for particular scen... Skolusu DFSORT/ICETOOL 6 Fri Mar 07, 2014 1:10 pm
No new posts ADRDSSU code scenario enrico-sorichetti JCL & VSAM 3 Fri Jan 25, 2013 2:50 pm
No new posts Tricky scenario useit JCL & VSAM 5 Wed Aug 31, 2011 12:09 pm
No new posts weird debugging scenario in XPEDITER nigelosberry Compuware & Other Tools 15 Wed Mar 30, 2011 10:09 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us