Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: Merging 2 queries

    Wished I don't have to post here again.. Seems like I just ask, and don't contribute, but I have limited knowledge of Oracle SQL. Sorry about that.

    Anyways, if someone could help, I need to combine these 2 queries into one.

    Code:
    SELECT CNTR.ARR_DTTM, TO_CHAR(CNTR.ARR_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.ARR_DTTM, 'HH24MI') AS ATIME
    FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
    WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.LD_VV_CD))
    AND CNTR.ARR_DTTM BETWEEN TO_DATE('11/01/2003', 'MM/DD/YYYY') AND TO_DATE('11/30/2003', 'MM/DD/YYYY')
    AND CNTR.PURP_CD = 'EX'
    Code:
    SELECT CNTR.EXIT_DTTM, TO_CHAR(CNTR.EXIT_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.EXIT_DTTM, 'HH24MI') AS ATIME
    FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
    WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.DISC_VV_CD))
    AND CNTR.EXIT_DTTM BETWEEN TO_DATE('11/01/2003', 'MM/DD/YYYY') AND TO_DATE('11/30/2003', 'MM/DD/YYYY')
    AND CNTR.PURP_CD = 'IM'
    One comes from the ARR_DTTM column, while the other is the EXIT_DTTM column. And the tables are joined using 2 differently for both queries. Thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    18

    Re: Merging 2 queries

    Hi,

    use UNION between queries.


    SELECT CNTR.ARR_DTTM,
    TO_CHAR(CNTR.ARR_DTTM, 'DY') AS ADAY,
    TO_CHAR(CNTR.ARR_DTTM, 'HH24MI') AS ATIME
    FROM TOPS.VESSEL_CALL VES,
    TOPS.BERTHING BERTHING,
    TOPS.CNTR_EVENT_LOG CNTR
    WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.LD_VV_CD))
    AND CNTR.ARR_DTTM BETWEEN TO_DATE('11/01/2003', 'MM/DD/YYYY')
    AND TO_DATE('11/30/2003', 'MM/DD/YYYY')
    AND CNTR.PURP_CD = 'EX'

    union



    SELECT CNTR.EXIT_DTTM,
    TO_CHAR(CNTR.EXIT_DTTM, 'DY') AS ADAY,
    TO_CHAR(CNTR.EXIT_DTTM, 'HH24MI') AS ATIME
    FROM TOPS.VESSEL_CALL VES,
    TOPS.BERTHING BERTHING,
    TOPS.CNTR_EVENT_LOG CNTR
    WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.DISC_VV_CD))
    AND CNTR.EXIT_DTTM BETWEEN TO_DATE('11/01/2003', 'MM/DD/YYYY') AND TO_DATE('11/30/2003', 'MM/DD/YYYY')
    AND CNTR.PURP_CD = 'IM';

    This will work.


    ------------------------------------------------

    Originally posted by zedd
    Wished I don't have to post here again.. Seems like I just ask, and don't contribute, but I have limited knowledge of Oracle SQL. Sorry about that.

    Anyways, if someone could help, I need to combine these 2 queries into one.

    Code:
    SELECT CNTR.ARR_DTTM, TO_CHAR(CNTR.ARR_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.ARR_DTTM, 'HH24MI') AS ATIME
    FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
    WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.LD_VV_CD))
    AND CNTR.ARR_DTTM BETWEEN TO_DATE('11/01/2003', 'MM/DD/YYYY') AND TO_DATE('11/30/2003', 'MM/DD/YYYY')
    AND CNTR.PURP_CD = 'EX'
    Code:
    SELECT CNTR.EXIT_DTTM, TO_CHAR(CNTR.EXIT_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.EXIT_DTTM, 'HH24MI') AS ATIME
    FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
    WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.DISC_VV_CD))
    AND CNTR.EXIT_DTTM BETWEEN TO_DATE('11/01/2003', 'MM/DD/YYYY') AND TO_DATE('11/30/2003', 'MM/DD/YYYY')
    AND CNTR.PURP_CD = 'IM'
    One comes from the ARR_DTTM column, while the other is the EXIT_DTTM column. And the tables are joined using 2 differently for both queries. Thanks.

  3. #3
    Join Date
    Nov 2003
    Posts
    16
    Exactly the thing I need. Thanks a mil'. Found out there's a UNION ALL command since I need all the records instead of distinct ones. Thanks!

Posting Permissions

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