How to retrieve the right rows ?

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
dok2ren
Member
Posts: 2
Joined: Tue Sep 27, 2011 7:42 pm

How to retrieve the right rows ?

Post by dok2ren » Fri Apr 04, 2014 12:03 pm

Hi.

I have a table wirh the following rows :

A B C D E F
-------+---------+---------+---------+---------+---------+---------+------
60201738. 4. F 20140405. 99991231 .285.
60201738. 1. P 20130401. 20131001. 285.
60201738. 2. P 20131001. 20140401. 285.
60201738. 3. P 20140401. 99991231. 285.
60201739. 1. P 20130401. 99991231. 72.
60201740. 1. P 20130401. 99991231. 272.
60201741. 1. P 20130401. 99991231. 473.
60201742. 1. P 20130401. 20131201. 118.
60201742. 2. P 20131201. 99991231. 118.
60201743. 1. P 20130401. 99991231. 118.
60201744. 1. P 20130401. 20131001. 289.
60201744. 2. P 20131001. 20140401. 289.
60201744. 3. P 20140401. 99991231. 289.
60201745. 1. P 20130401. 99991231. 73.
60201746. 1. P 20130401. 99991231. 275.

60201747. 1. P 20130401. 20131001. 293.
60201747. 2. P 20131001. 20140401. 293.
60201747. 3. P 20140401. 99991231. 293.
60201748. 1. P 20130401. 99991231. 74.
60201749. 1. P 20130401. 99991231. 279.
60201750. 1. P 20130401. 99991231. 479.

60201751. 1. P 20130401. 20131201. 120.
60201751. 2. P 20131201. 99991231. 120.
60201752. 1. P 20130401. 99991231. 120.
60201753. 1. P 20130401. 99991231. 485.


I need a SQl that can make the SUM(F) og the rows highligted. Its : all rows with a specific A but only 1 row for each A, either (with C='F') or
(C='P' AND D<=Date AND E>Date. And I can't use EXIST or NOT EXISTS

Hope it's understandable

greetz

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Fri Apr 04, 2014 2:27 pm

Not really because you have not used the code tags to preserve spacing.

However, pretending that it is psychic Friday, it looks as thogh you are only wanting all rows where column E = 99991231.
Regards
Nic

dok2ren
Member
Posts: 2
Joined: Tue Sep 27, 2011 7:42 pm

Post by dok2ren » Fri Apr 04, 2014 2:39 pm

NicC wrote:Not really because you have not used the code tags to preserve spacing.

However, pretending that it is psychic Friday, it looks as thogh you are only wanting all rows where column E = 99991231.

Code: Select all

A                B   C         D               E             F 
-------+---------+---------+---------+---------+---------+---------+------ 
60201738.   4.   F   20140405.   99991231.   285. 
60201738.   1.   P   20130401.   20131001.   285. 
60201738.   2.   P   20131001.   20140401.   285. 
60201738.   3.   P   20140401.   99991231.   285. 
60201739.   1.   P   20130401.   99991231.    72. 
60201740.   1.   P   20130401.   99991231.   272. 
60201741.   1.   P   20130401.   99991231.   473. 
60201742.   1.   P   20130401.   20131201.   118. 
60201742.   2.   P   20131201.   99991231.   118. 
60201743.   1.   P   20130401.   99991231.   118. 
60201744.   1.   P   20130401.   20131001.   289. 
60201744.   2.   P   20131001.   20140401.   289. 
60201744.   3.   P   20140401.   99991231.   289. 
60201745.   1.   P   20130401.   99991231.   73. 
60201746.   1.   P   20130401.   99991231.   275. 
60201747.   1.   P   20130401.   20131001.   293. 
60201747.   2.   P   20131001.   20140401.   293. 
60201747.   3.   P   20140401.   99991231.   293. 
60201748.   1.   P   20130401.   99991231.   74. 
60201749.   1.   P   20130401.   99991231.   279. 
60201750.   1.   P   20130401.   99991231.   479. 
60201751.   1.   P   20130401.   20131201.   120. 
60201751.   2.   P   20131201.   99991231.   120. 
60201752.   1.   P   20130401.   99991231.   120. 
60201753.   1.   P   20130401.   99991231.   485. 
60202439.   1.   F   20130401.   99991231.     10. 

No it's not about the date. If there is a row with C='F' i shall be selected and all other rows with the same A shall not be used. If there's no rows with C='F' the row with C='P' and the appropriate Date has to be selected (and onkly this row)

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Fri Apr 04, 2014 9:03 pm

Would C be the 4th or fifth byte of 602017.... as it is hard to make out. Possibly it is somewhere else completely but if you do not use the code tags (search the forum for how to use them) your data will not be properly spaced.
Regards
Nic

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Apr 06, 2014 5:19 am

Your data has been "Code'd". It appears there is an alignment issue.

Please repost your data properly Code'd.

Then a suggestion will be easier to make.

So far, I do not understand what the process will do whan it is working properly :?
Have a good one

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others