November 24, 2015 at 9:16 am
Hi.
We are migrating our SQL SERVER 2000 to 2008 now , I have one complex query which is written in 2000 and I need to convert in 2008 version but not getting the success. Can some please help me on this..
---2000 Version Query
SELECT sum ( isnull ( a_stmt.amt_du_ic, 0 ) ),
#pmt_dtes.pmt_dte,
r_rj.juris_id,
a_carr.acna,
#pmt_dtes.ba_cntct_id,
#pmt_dtes.cntct_nm,
#pmt_dtes.cntct_nbr
FROM a_stmt,
r_rj,
a_carr, #pmt_dtes
WHERE ( a_stmt.juris_id =* r_rj.juris_id) and
( a_stmt.acna =* a_carr.acna) and
( a_stmt.act_pmt_dte =* #pmt_dtes.pmt_dte ) and
a_carr.acna = #pmt_dtes.acna and (a_stmt.stat = 'A' or a_stmt.stat = 'S' )
GROUP BY a_carr.acna, #pmt_dtes.pmt_dte,r_rj.juris_id, #pmt_dtes.ba_cntct_id, #pmt_dtes.cntct_nm, #pmt_dtes.cntct_nbr
--- 2008 Version Query
SELECT sum ( isnull ( a_stmt.amt_du_ic, 0 ) ),
#pmt_dtes.pmt_dte,
r_rj.juris_id,
a_carr.acna
,
#pmt_dtes.ba_cntct_id,
#pmt_dtes.cntct_nm,
#pmt_dtes.cntct_nbr
FROM a_stmt
RIGHT JOIN r_rj
on a_stmt.juris_id = r_rj.juris_id
AND (a_stmt.stat = 'A' or a_stmt.stat = 'S' )
RIGHT JOIN a_carr
ON a_stmt.acna = a_carr.acna
RIGHT JOIN #pmt_dtes
ON a_stmt.act_pmt_dte = #pmt_dtes.pmt_dte
and a_carr.acna = #pmt_dtes.acna
GROUP BY a_carr.acna,
#pmt_dtes.pmt_dte,
r_rj.juris_id
,
#pmt_dtes.ba_cntct_id, #pmt_dtes.cntct_nm, #pmt_dtes.cntct_nbr
the results are coming different , please let me know how can I convert it. Thanks in Advance
-Vineet
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com
November 24, 2015 at 9:33 am
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
November 24, 2015 at 9:40 am
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.
November 24, 2015 at 11:25 am
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
November 24, 2015 at 1:03 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply