DB2 - L

 View Only
  • 1.  SQL- Format to a single row

    Posted Nov 17, 2021 01:45 PM
    Edited by Ron Thomas Nov 17, 2021 01:46 PM
    Hi -We have the below data in a table 

    STORE_NBR PIC_NBR MaxSellPrice fromdate todate
    4,917.00 7,433,876       3.5 11/17/2017 ?
    4,917.00 7,433,876       3.5 ? 12/1/2017
    4,917.00 7,433,876         2 12/2/2017 ?
    4,917.00 7,433,876         2 ? 12/15/2017

    Output Needed 

    STORE_NBR PIC_NBR MaxSellPrice fromdate todate
    4,917.00 7,433,876       3.5 11/17/2017 12/1/2017
    4,917.00 7,433,876         2 12/2/2017 12/15/2017

    is there a way we can build the data using a query .

    Regards
    Ron T 


    ------------------------------
    RonThomasXYZ Corp
    ------------------------------


  • 2.  RE: SQL- Format to a single row

    Posted Nov 17, 2021 01:59 PM

    Easy-peasy (!)

     

    SELECT STORE_NBR, PIC_NBR, MaxSellPrice, MAX(fromdate) fromdate, MAX(todate) todate

    FROM

      (SELECT STORE_NBR, PIC_NBR, MaxSellPrice, VALUE(fromdate,'0001-01-01') as fromdate, VALUE(todate,'0001-01-01') as todate

       FROM [your table name here]

      GROUP BY STORE_NBR, PIC_NBR, MaxSellPrice

      )

    ;

     

    I think I've got the details right.  Give it a spin.

     

    Philip Sevetson

     






  • 3.  RE: SQL- Format to a single row

    Posted Nov 17, 2021 02:00 PM

    Sorry, one fix, in line.

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     






  • 4.  RE: SQL- Format to a single row

    Posted Nov 17, 2021 02:12 PM

    Problem with the server or something.

     

    In my sent email as sent, the fix was applied to the SQL which I provided in the _first_ email.  However, the second one arrived back in my mailbox as below; with neither the first nor the fixed SQL inline.  So: 

    1.       Sorry about three emails on this.

    2.       Here's the _fixed_ SQL:

     

    SELECT STORE_NBR, PIC_NBR, MaxSellPrice, MAX(fromdate) fromdate, MAX(todate) todate

    FROM

      (SELECT STORE_NBR, PIC_NBR, MaxSellPrice, VALUE(fromdate,'0001-01-01') as fromdate, VALUE(todate,'0001-01-01') as todate

       FROM [your table name here]

      )

      GROUP BY STORE_NBR, PIC_NBR, MaxSellPrice

    ;

     

    If anyone does not see a complete SELECT statement, with a table expression and a GROUP BY clause, please let me know.  Maybe this is somehow my error, or a system glitch, but I hope it's resolved now.

     

    /phil

     

     

    Philip Sevetson

     






  • 5.  RE: SQL- Format to a single row

    Posted Nov 17, 2021 02:51 PM
    Thanks a lot Philip . It helped .

    Regards
    Ron T

    ------------------------------
    RonThomasXYZ Corp
    ------------------------------



  • 6.  RE: SQL- Format to a single row

    Posted Nov 18, 2021 09:36 AM
    my go to choice is the use of CTE  .. I have 3 CTEs here (STORE_DATE is to build some data from your base table like structure, the other 2 are STORE_PIC_FROM & STORE_PIC_TO to build the subsets with respective records with non-null values on the FROM/TO_DATE columns as you can see - then the final query to join the 2 CTEs on Store, Pic, and MSPrice).  for testing I used 'BLANK' in lieu of NULL values.

    hope this is something you are looking for..

    ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--
    With STORE_DATE(Store, Pic, MSPrice, Fdate, Tdate) 00010000
    AS 00020000
    ( 00030016
    SELECT 4917, 7433876,3.5, '11/17/2017', 'BLANK' 00031016
    from sysibm.sysdummy1 UNION 00040003
    SELECT 4917, 7433876,2.0, '12/2/2017', 'BLANK' 00050013
    from sysibm.sysdummy1 UNION 00060005
    SELECT 4917, 7433876,3.5, 'BLANK', '12/4/2017' 00070013
    from sysibm.sysdummy1 UNION 00080005
    SELECT 4917, 7433876,2.0, 'BLANK', '12/8/2017' 00090013
    from sysibm.sysdummy1 union 00100016
    00109116
    SELECT 4917, 5433876,3.5, '11/17/2017', 'BLANK' 00109219
    from sysibm.sysdummy1 UNION 00109319
    SELECT 4917, 5433876,2.0, '12/2/2017', 'BLANK' 00109419
    from sysibm.sysdummy1 UNION 00109519
    SELECT 4917, 5433876,3.5, 'BLANK', '12/4/2017' 00109619
    from sysibm.sysdummy1 UNION 00109719
    SELECT 4917, 5433876,2.0, 'BLANK', '12/8/2017' 00109819
    from sysibm.sysdummy1 union 00109919
    00110019
    SELECT 5917, 7433876,3.5, '11/17/2017', 'BLANK' 00110119
    from sysibm.sysdummy1 UNION 00110219
    SELECT 5917, 7433876,2.0, '12/2/2017', 'BLANK' 00110319
    from sysibm.sysdummy1 UNION 00110419
    SELECT 5917, 7433876,3.5, 'BLANK', '12/4/2017' 00110519
    from sysibm.sysdummy1 UNION 00110619
    SELECT 5917, 7433876,2.0, 'BLANK', '12/8/2017' 00110719
    from sysibm.sysdummy1 00110819
    00110919
    ) 00111018
    , 00120001
    STORE_PIC_FROM(StoreNbr, PicNbr, MSPrice, Fdate) 00130012
    AS 00140000
    (Select STORE, Pic, MSPrice, Fdate 00150010
    from STORE_DATE 00160000
    WHERE Fdate <> 'BLANK' 00170013
    ) 00180006
    , 00190000
    STORE_PIC_TO(StoreNbr, PicNbr, MSPrice, TDate) 00200012
    AS 00210000
    (Select STORE, Pic, MSPrice, Tdate 00220010
    from STORE_DATE 00230000
    WHERE TDate <> 'BLANK' 00240013
    ) 0025

    select A.StoreNbr, A.PicNbr, A.Fdate, B.TDate 00260012
    from STORE_PIC_FROM A, 00270012
    STORE_PIC_TO B 00280015
    WHERE A.StoreNbr = B.StoreNbr 00290000
    AND A.PicNbr = B.PicNbr 00300000
    AND A.MSPrice = B.MSPrice 00310012
    ORDER BY 1,2,3,4 00320000
    WITH UR; 00330000

    ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--
    STORENBR PICNBR FDATE TDATE
    ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--
    4917 5433876 11/17/2017 12/4/2017
    4917 5433876 12/2/2017 12/8/2017
    4917 7433876 11/17/2017 12/4/2017
    4917 7433876 12/2/2017 12/8/2017
    5917 7433876 11/17/2017 12/4/2017
    5917 7433876 12/2/2017 12/8/2017

    DSNE610I NUMBER OF ROWS DISPLAYED IS 6


    ------------------------------
    VASUDEVAN NATARAJAN
    Walmart
    ------------------------------



  • 7.  RE: SQL- Format to a single row

    Posted Nov 18, 2021 09:39 AM
    here is just the Query:
    With STORE_DATE(Store, Pic, MSPrice, Fdate, Tdate)
    AS
    (
    SELECT 4917, 7433876,3.5, '11/17/2017', 'BLANK'
    from sysibm.sysdummy1 UNION
    SELECT 4917, 7433876,2.0, '12/2/2017', 'BLANK'
    from sysibm.sysdummy1 UNION
    SELECT 4917, 7433876,3.5, 'BLANK', '12/4/2017'
    from sysibm.sysdummy1 UNION
    SELECT 4917, 7433876,2.0, 'BLANK', '12/8/2017'
    from sysibm.sysdummy1 union

    SELECT 4917, 5433876,3.5, '11/17/2017', 'BLANK'
    from sysibm.sysdummy1 UNION
    SELECT 4917, 5433876,2.0, '12/2/2017', 'BLANK'
    from sysibm.sysdummy1 UNION
    SELECT 4917, 5433876,3.5, 'BLANK', '12/4/2017'
    from sysibm.sysdummy1 UNION
    SELECT 4917, 5433876,2.0, 'BLANK', '12/8/2017'
    from sysibm.sysdummy1 union

    SELECT 5917, 7433876,3.5, '11/17/2017', 'BLANK'
    from sysibm.sysdummy1 UNION
    SELECT 5917, 7433876,2.0, '12/2/2017', 'BLANK'
    from sysibm.sysdummy1 UNION
    SELECT 5917, 7433876,3.5, 'BLANK', '12/4/2017'
    from sysibm.sysdummy1 UNION
    SELECT 5917, 7433876,2.0, 'BLANK', '12/8/2017'
    from sysibm.sysdummy1

    )
    ,
    STORE_PIC_FROM(StoreNbr, PicNbr, MSPrice, Fdate)
    AS
    (Select STORE, Pic, MSPrice, Fdate
    from STORE_DATE
    WHERE Fdate <> 'BLANK'
    )
    ,
    STORE_PIC_TO(StoreNbr, PicNbr, MSPrice, TDate)
    AS
    (Select STORE, Pic, MSPrice, Tdate
    from STORE_DATE
    WHERE TDate <> 'BLANK'
    )

    select A.StoreNbr, A.PicNbr, A.Fdate, B.TDate
    from STORE_PIC_FROM A,
    STORE_PIC_TO B
    WHERE A.StoreNbr = B.StoreNbr
    AND A.PicNbr = B.PicNbr
    AND A.MSPrice = B.MSPrice
    ORDER BY 1,2,3,4
    WITH UR;

    ------------------------------
    VASUDEVANNATARAJANwalmart
    ------------------------------



  • 8.  RE: SQL- Format to a single row

    Posted Nov 19, 2021 09:01 AM
    Thanks Vasudevan .  i tried the same and this worked like a charm .. sorry for the delay in responding .

    Regards
    Ron T

    ------------------------------
    RonThomasXYZ Corp
    ------------------------------