Oracle to TSQL - query question

Question

Post reply

Oracle to TSQL - query question

SQLMAIN

SSC Eights!

Points: 936
More actions
August 29, 2009 at 3:34 pm

#103812

I have this query that runs in ORACLE and I wanted to convert this to run in TSQL under SQL server 2000. I have made an attempt and looks ok - but somehow not getting the same results. If someone can check and verify that I have not missed anyrhing. That will help a lot.
It is likely that the tables on the two systems are not exact same.. but before I get to that route I wanted to make sure the query is good. I do not have access to the ORACLE tables so before I claim that the issue is with table data I wanted to verify that the query is properly converted.
Here is the Oracle version followed by TSQL
SELECT count(cust.ban) as qty, cust. BB_IND_Update
from (Select c.ban,
MAX(CASE WHEN(e.num_dist > '21.9' AND e.bb_ind = '.5')
THEN .2
ELSE e.bb_ind end) as BB_IND_Update
from ens_cust_profile2_dt c,
mtn_wtn_name_ap_dtl_rt e,
delta_product_ext_tbl d
where d.customer_id = c.ban
and d.product_id(+) = e.wtn
GROUP BY c.ban) cust
group by cust.BB_IND_Update
SELECT count(cust.ban) as qty, cust.BB_IND_Update
from (Select c.ban,
MAX(CASE WHEN(e.num_dist > '21.9' AND e.bb_ind = '.5')
THEN .2
ELSE e.bb_ind end) as BB_IND_Update
from delta_product_ext_tbl d
left join mtn_wtn_name_ap_dtl_rt e
on d.product_id = e.wtn
join ens_cust_profile2_dt c
on d.customer_id = c.ban
GROUP BY c.ban) cust
group by cust.BB_IND_Update

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Try a right join. If I remember correctly, (+) in Oracle is on the opposite column that you would expect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 2

What are the data types for e.num_dist and e.bb_ind ?

_____________
Code for TallyGenerator

Nice Marmot SSCrazy Points: 2278 More actions · Answer 3

Yes, Jeff is right - it is an outer join with "mtn_wtn_name_ap_dtl_rt". You need something like:

from ens_cust_profile2 c

inner join join delta_product_ext_tbl d

right outer join mtn_wtn_name_ap_dtl_rt e on ...

This syntax will work in Oracle as well. 😉

I would also suggest using WITH to factor out the in-line view and make the query more readable. HTH

Edit: D'OH! Had it the wrong way round :blush: ... see http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054062

SQLMAIN SSC Eights! Points: 936 More actions · Answer 4

Thanks to both of you - It turned out that the table also was not an exact copy which was the main cause for the gap.

I will fix the query after we run it again with proper data.

Thanks