Need help in Converting SQL SERVER 2000 QUERY In 2008

Question

Post reply

Need help in Converting SQL SERVER 2000 QUERY In 2008

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I believe this should be equivalent.

SELECT sum ( isnull ( s.amt_du_ic, 0 ) ),

d.pmt_dte,

r.juris_id,

c.acna,

d.ba_cntct_id,

d.cntct_nm,

d.cntct_nbr

FROM a_stmt s

RIGHT JOIN r_rj r ON s.juris_id = r.juris_id

RIGHT JOIN a_carr c ON s.acna = c.acna

RIGHT JOIN #pmt_dtes d ON s.act_pmt_dte = d.pmt_dte

WHERE c.acna = d.acna --This is in the WHERE because it might bring different results if used in the JOIN criteria.

and (s.stat = 'A' or s.stat = 'S' )

GROUP BY c.acna,

d.pmt_dte,

r.juris_id,

d.ba_cntct_id,

d.cntct_nm,

d.cntct_nbr

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Luis Cazares (11/24/2015)
I believe this should be equivalent.
SELECT sum ( isnull ( s.amt_du_ic, 0 ) ),
d.pmt_dte,
r.juris_id,
c.acna,
d.ba_cntct_id,
d.cntct_nm,
d.cntct_nbr
FROM a_stmt s
RIGHT JOIN r_rj r ON s.juris_id = r.juris_id
RIGHT JOIN a_carr c ON s.acna = c.acna
RIGHT JOIN #pmt_dtes d ON s.act_pmt_dte = d.pmt_dte
WHERE c.acna = d.acna --This is in the WHERE because it might bring different results if used in the JOIN criteria.
and (s.stat = 'A' or s.stat = 'S' )
GROUP BY c.acna,
d.pmt_dte,
r.juris_id,
d.ba_cntct_id,
d.cntct_nm,
d.cntct_nbr

This is what I came up with as well. Problem is we have nothing to test the queries against.

It would help if you could post the DDL for the tables involved along with some test data (not production data) so we can run both queries (using SQL Server 2000 compatibility) and verify that the results returned are identical.

Converting from SQL-89 to SQL-92 style outer joins can be a pain. You actually need to test and rewrite until the SQL-92 query returns the same result set as the original query. What it means is that sometimes you need to pull what looks like a filter condition from the where clause into the join clause to get the same results.

vineetbhargav Mr or Mrs. 500 Points: 522 More actions · Answer 3

Thanks, i tried this way only thing which is different over here is where condition i place that condition in ON instead of where and that resulted in wrong result.

Will try this as well.Also is there any ways out we can break out this query into multiple queries...

Sorry i cannot provide data as the size of data is very big and i have to mask whole thing as it is senistive data.

Thanks

-Vineet

Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

vineetbhargav (11/24/2015)
Thanks, i tried this way only thing which is different over here is where condition i place that condition in ON instead of where and that resulted in wrong result.
Will try this as well.Also is there any ways out we can break out this query into multiple queries...
Sorry i cannot provide data as the size of data is very big and i have to mask whole thing as it is senistive data.
Thanks
-Vineet

Sample data is just that, sample data. You should be able to mock a small subset of data based on but not using production data that reflects your problem domain, that includes have data that does not match between tables as well as data that does. In this case you may need about 20 rows for each to catch most of the cases you may run across in your production system.