Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Exclamation Unanswered: using OR vs using Union

    Hi All,
    this is my first post on any forum.
    It would be great if someone can tell me whether to use OR or a UNION when both are giving same results.

    Also i want to know is the use of CASE statement like below correct ?

    here are two queries that I am talking about

    1
    +++++++++++++++++++++++++++++++++++++++++++++++++
    SELECT
    'FAILED' status,
    'CR' trx_type,
    'P2P' eventstatuscode,
    src.devicenumber display_device,
    FROM transactionevent a, account src

    WHERE a.tgtaccountid =:srcaccountid
    AND a.eventstatuscode IN ('abc')
    AND a.transtypecode = 'P2P'
    AND src.id = a.srcaccountid

    UNION


    'FAILED' status,
    'DR' trx_type,
    'P2P' eventstatuscode,
    tgt.devicenumber display_device
    FROM transactionevent a, account tgt,

    WHERE a.srcaccountid =:srcaccountid
    AND a.eventstatuscode IN ('abc')
    AND a.transtypecode = 'P2P'
    AND tgt.id = a.tgtaccountid

    ++++++++++++++++++++++++++++++++++++++++++++++++++ +

    2
    ++++++++++++++++++++++++++++++++++++++++++++++++++
    SELECT
    'FAILED' status,
    (CASE
    WHEN src.id = a.tgtaccountid THEN 'DR'
    WHEN src.id = a.srcaccountid THEN 'CR'
    END ) trx_type,
    'P2P' eventstatuscode,
    src.devicenumber display_device
    FROM transactionevent a, account src

    WHERE ( a.tgtaccountid =:srcaccountid OR a.srcaccountid=:srcaccountid )
    AND a.eventstatuscode IN ('abc')
    AND a.transtypecode = 'P2P'
    AND src.id = (CASE
    WHEN a.srcaccountid =:srcaccountid then a.tgtaccountid
    WHEN a.tgtaccountid =:srcaccountid then a.srcaccountid
    END
    )

    ++++++++++++++++++++++++++++++++++++++++++++++++++

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Just some quick comments:
    • Welcome to the forum!
    • What RDBMS are you using?
    • If both statements produce the correct answer in a reasonable time then both statements are correct. Personally I find the OR easier to read.
    • You are missing a select after the union keyword.
    • You should use a.eventstatuscode = 'abc' rather than a.eventstatuscode IN ('abc')
    • Your first case statement uses = while the second case uses =: - this seems very odd.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mike - I _think_ these are non ANSI outer joins. As such, I would recommend changing the joins to ANSI.

    I'm fairly sure there are more syntax errors in those queries too.... OP - are you sure these run?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, these aren't wacky outer joins, the operator is "=" in all cases

    the queries are using a single run-time parameter, :srcaccountid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah ok. Note this wasn't in ANSI SQL when first posted otherwise I wouldn't have responded and displayed my ignorance.

    I'd still use ANSI joins though, be they innies or outies.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2009
    Posts
    3
    Quote Originally Posted by pootle flump
    Mike - I _think_ these are non ANSI outer joins. As such, I would recommend changing the joins to ANSI.

    I'm fairly sure there are more syntax errors in those queries too.... OP - are you sure these run?

    I missed 1 select after UNION while pasting it.

    the query works flawlessly , =: is parameter that i pass

  7. #7
    Join Date
    Sep 2009
    Posts
    3
    Quote Originally Posted by mike_bike_kite
    Just some quick comments:
    • Welcome to the forum!
    • What RDBMS are you using?
    • If both statements produce the correct answer in a reasonable time then both statements are correct. Personally I find the OR easier to read.
    • You are missing a select after the union keyword.
    • You should use a.eventstatuscode = 'abc' rather than a.eventstatuscode IN ('abc')
    • Your first case statement uses = while the second case uses =: - this seems very odd.
    Hi Mike, thanks for replying..actually when i use =: i am getting value from a parameter. srcAccoutnId is a parameter. I am using Oracle

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Note this wasn't in ANSI SQL when first posted...
    it wasn't in the oracle forum, by any chance, was it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - dbconcepts
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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