using OR vs using Union

vickrampurswani · #1 (**permalink**) 09-09-09, 05:08

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
)

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

mike_bike_kite · #2 (**permalink**) 09-09-09, 05:41

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.

pootle flump · #3 (**permalink**) 09-09-09, 06:06

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?

r937 · #4 (**permalink**) 09-09-09, 08:10

no, these aren't wacky outer joins, the operator is "=" in all cases

the queries are using a single run-time parameter, :srcaccountid

pootle flump · #5 (**permalink**) 09-09-09, 08:30

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.

vickrampurswani · #6 (**permalink**) 09-09-09, 11:02

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

vickrampurswani · #7 (**permalink**) 09-09-09, 11:04

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