Results 1 to 7 of 7

Thread: SQL Statement

  1. #1
    Join Date
    Jul 2013
    Posts
    5

    Unanswered: SQL Statement

    I need to pull data from two tables, with one to many relationship by ID. The Status in TBL2 will have status like INROUTE, ARRFORPUP, ARRFORDEL, DELED, PUPED. I need to pull the ARRFORPUP with date and ARRFORDEL with date for comparrison with TBL1.PickByTime and TBL1.DelByTime.

    TBL1
    ID, FB, PickByTime, DelByTime

    TBL2
    ID Status, Date.

    I know how to do it by first running for ARRFORPUP and then for ARRFORDEL I need this to appear as if it came from a single UNION or JOIN.

    Can you help?

    James E. Squier Sr.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by James E. Squier S View Post
    Can you help?
    Unlikely, unless you publish the query that you have so far, sufficient sample data, the current result, and the desired result. Mentioning your DB2 platform and version will also be useful.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2013
    Posts
    5

    SQL Select

    DB2 9.7

    Select critera

    Associated by TLORDER.DETAIL_LINE_ID = ORDSTAT.ORDERID
    SELECT ORDERSTAT.CHANGED AS ARRSHIP WHERE STATUS_CODE = "ARRSHIP"
    SELECT ORDERSTAT.CHANGED AS ARRCONS WHERE STATUS_CODE = "ARRCONS"

    Desired FIELDS
    CUSTOMER BILL_NUMBER ORIGIN ORIGNAME PICK_UP_BY ARRSHIP DELIVER_BY ARRCONS DESTINATION UPDATED_BY PICK_UP_DRIVER

    Table TLORDER
    SELECT
    "TLORDER"."DETAIL_LINE_ID",
    "TLORDER"."CUSTOMER",
    "TLORDER"."BILL_NUMBER",
    "TLORDER"."ORIGIN",
    "TLORDER"."ORIGNAME",
    "TLORDER"."PICK_UP_BY",
    "TLORDER"."DELIVER_BY",
    "TLORDER"."DESTINATION",
    "TLORDER"."PICK_UP_DRIVER"
    FROM TLORDER
    WHERE
    "TLORDER"."CURRENT_STATUS"<>'CANCL'
    AND ("TLORDER"."DELIVER_BY">={ts '2013-07-17 00:00:00'}
    AND "TLORDER"."DELIVER_BY"<{ts '2013-07-18 00:00:00'})
    AND "TLORDER"."DELIVERY_DRIVER" IS NOT NULL
    ORDER BY "TLORDER"."DETAIL_LINE_ID", "TLORDER"."CUSTOMER", "TLORDER"."ORIGIN", "TLORDER"."PICK_UP_BY"

    DETAIL_LINE_ID CUSTOMER BILL_NUMBER ORIGIN ORIGNAME PICK_UP_BY DELIVER_BY DESTINATION PICK_UP_DRIVER
    ============== =========== =========== =========== ========================================= ====================== ====================== =========== ==============
    254717 ELS401 7110226 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00:00 7/17/2013 22:00:00 ELS025 ROCHAEUGEN
    254720 ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00:00 7/17/2013 22:00:00 ELS011 2299
    254724 ELS401 7110233 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 22:00:00 7/17/2013 22:00:00 ELS002 RIOSSALCED
    254726 ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00:00 7/17/2013 22:00:00 ELS005 2104
    254732 ELS400 7110241 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:59:00 7/17/2013 22:00:00 ELS017 2369
    254733 ELS401 7110242 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:59:00 7/17/2013 22:00:00 ELS017 BIGSBYTERR
    254735 ELS400 7110244 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/17/2013 01:00:00 7/17/2013 22:00:00 ELS003 2209
    254736 ELS400 7110245 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/17/2013 01:00:00 7/17/2013 22:00:00 ELS003 HARPERRUBE
    255177 ELS401 7110416 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/17/2013 7/17/2013 CHEELS ROCHAEUGEN


    TABLE ORDSTAT
    SELECT * FROM ODRSTAT
    WHERE
    ("ODRSTAT"."CHANGED">={ts '2013-07-17 00:00:00'}
    AND "ODRSTAT"."CHANGED"<{ts '2013-07-18 00:00:00'})
    ORDER BY "ODRSTAT"."CHANGED"

    ORDER_ID CHANGED STATUS_CODE UPDATED_BY TRIP_NUMBER LEG_ID ZONE_ID INS_DATE ID
    ======== ====================== =========== =========== =========== ====== ======== ====================== ======

    255177 7/17/2013 ARRSHIP RHERNANDEZ 222124 584563 91761 7/18/2013 03:30:03 2864605
    254717 7/17/2013 00:01:00 DELV-OUT RHERNANDEZ 221912 583944 91950 7/17/2013 01:09:16 2861721
    254720 7/17/2013 00:01:00 DELV-OUT RHERNANDEZ 221933 583985 92801 7/17/2013 02:05:43 2861810
    254732 7/17/2013 00:01:00 ARRCONS RHERNANDEZ 221943 584014 90044 7/17/2013 02:11:39 2861825
    254726 7/17/2013 00:01:00 ARRCONS RHERNANDEZ 221938 583997 91605 7/17/2013 03:03:51 2861949
    254733 7/17/2013 00:01:00 DEPSHIP RHERNANDEZ 221960 584044 91761 7/17/2013 04:34:56 2862372
    254735 7/17/2013 00:01:00 DISP RHERNANDEZ 221969 584186 91761 7/17/2013 04:38:14 2862378
    254724 7/17/2013 00:02:00 DELV-IN RHERNANDEZ 221936 583992 91340 7/17/2013 04:19:08 2862333
    254768 7/17/2013 00:02:00 DELV-IN RHERNANDEZ 221936 583992 91340 7/17/2013 04:19:08 2862334
    254736 7/17/2013 00:03:00 DISP RHERNANDEZ 221976 584178 91761 7/17/2013 04:30:12 2862346
    254717 7/17/2013 00:06:00 DELVD RHERNANDEZ 221912 583944 91950 7/17/2013 01:09:20 2861722


    Join
    SELECT
    "TLORDER"."CUSTOMER",
    "TLORDER"."BILL_NUMBER",
    "TLORDER"."ORIGIN",
    "TLORDER"."ORIGNAME",
    "TLORDER"."PICK_UP_BY",
    "ODRSTAT"."CHANGED",
    "TLORDER"."PICK_UP_BY",
    "ODRSTAT"."CHANGED",
    "TLORDER"."DESTINATION",
    "ODRSTAT".UPDATED_BY,
    "TLORDER"."PICK_UP_DRIVER"
    FROM TLORDER "TLORDER" INNER JOIN ODRSTAT "ODRSTAT" ON "TLORDER"."DETAIL_LINE_ID"="ODRSTAT"."ORDER_ID "
    WHERE
    "TLORDER"."CURRENT_STATUS"<>'CANCL'
    AND ("TLORDER"."DELIVER_BY">={ts '2013-07-17 00:00:00'}
    AND "TLORDER"."DELIVER_BY"<{ts '2013-07-18 00:00:00'})
    AND "TLORDER"."DELIVERY_DRIVER" IS NOT NULL
    ORDER BY "TLORDER"."CUSTOMER", "TLORDER"."ORIGIN", "TLORDER"."PICK_UP_BY",
    "ODRSTAT"."CHANGED"

    CUSTOMER BILL_NUMBER ORIGIN ORIGNAME PICK_UP_BY CHANGED DELIVER_BY DESTINATION UPDATED_BY PICK_UP_DRIVER
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/15/2013 22:25 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/15/2013 22:26 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 00:06 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 00:06 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 00:06 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 00:06 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 21:30 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 21:43 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 22:00 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 22:30 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 23:00 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/16/2013 23:05 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/17/2013 00:01 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/17/2013 00:06 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110229 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 21:00 7/17/2013 00:06 7/17/2013 22:00 ELS011 RHERNANDEZ 2299
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/15/2013 22:25 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/15/2013 22:26 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 00:16 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 00:16 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 00:16 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 00:16 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 21:00 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 22:00 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 22:16 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/16/2013 22:30 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/17/2013 00:01 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/17/2013 00:06 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/17/2013 01:30 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/17/2013 01:35 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110235 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:00 7/17/2013 01:35 7/17/2013 22:00 ELS005 RHERNANDEZ 2104
    ELS400 7110241 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:59 7/15/2013 22:26 7/17/2013 22:00 ELS017 RHERNANDEZ 2369
    ELS400 7110241 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:59 7/15/2013 22:26 7/17/2013 22:00 ELS017 RHERNANDEZ 2369
    ELS400 7110241 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:59 7/16/2013 00:20 7/17/2013 22:00 ELS017 RHERNANDEZ 2369
    ELS400 7110241 FIE102 EL SUPER DISTRUBUTION ACCNT # 15283 7/16/2013 23:59 7/16/2013 00:20 7/17/2013 22:00 ELS017 RHERNANDEZ 2369

    Can you help?

    James E. Squier Sr.

  4. #4
    Join Date
    Jul 2013
    Posts
    5

    SQL Select

    I think it would be something similar to:

    SELECT
    "TLORDER"."CUSTOMER",
    "TLORDER"."BILL_NUMBER",
    "TLORDER"."ORIGIN",
    "TLORDER"."ORIGNAME",
    "TLORDER"."PICK_UP_BY",
    "SHIP"."ARRSHIP",
    "TLORDER"."DELIVER_BY",
    "DELIVER"."ARRCONS",
    "TLORDER"."DESTINATION",
    "ODRSTAT".UPDATED_BY,
    "TLORDER"."PICK_UP_DRIVER"

    FROM "TLORDER", "ODRSTAT",
    (SELECT "ODRSTAT"."CHANGED" AS "ARRSHIP",
    FROM "TLORDER", "ODRSTAT",
    WHERE "TLORDER"."DETAIL_LINE_ID"="ODRSTAT"."ORDER_ID "
    AND
    "ODRSTAT"."STATUS_CODE" = "ARRSHIP"
    ) AS SHIP
    (SELECT "ODRSTAT"."CHANGED" AS "ARRCONS",
    FROM "TLORDER", "ODRSTAT",
    WHERE "TLORDER"."DETAIL_LINE_ID"="ODRSTAT"."ORDER_ID "
    AND
    "ODRSTAT"."STATUS_CODE" = "ARRCONS"
    ) AS DELIVER

    WHERE "TLORDER"."CURRENT_STATUS"<>'CANCL'
    AND
    "TLORDER"."CUSTOMER"='UNI403'
    AND
    ("TLORDER"."DELIVER_BY">={ts '2013-07-17 00:00:00'}
    AND
    "TLORDER"."DELIVER_BY"<{ts '2013-07-18 00:00:00'})
    AND
    "TLORDER"."DELIVERY_DRIVER" IS NOT NULL

    ORDER BY "TLORDER"."CUSTOMER", "TLORDER"."ORIGIN", "TLORDER"."PICK_UP_BY"

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm sorry, it still doesn't make much sense to me. I must be particularly thick today. Tonkuma-san might have more patience to decipher all this.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    Maybe you can try this :
    Select xxx from TLB1 a,TLB2 b
    where a.id = b.id
    and b.Status in ('ARRFORPUP','ARRFORDEL')
    and ( (b.status = 'ARRFORPUP' and a.PickByTime= b.date)
    or (b.status = 'ARRFORDEL' and a.DelByTime = b.date) )

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that fengsun2 showed essentially right answer.
    (Though, it may be neccesary to add some more conditions.)

    I hope OP(James E. Squier S) report(or I want to see) the results of his(her?) trials.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •