Comparing SQL Queries 1

[TeresaSalesPlus]

Hi,

We have migrated to a new version of an application that integrates with Crystal Reports 9.0. I'm trying to update the reports and replace an old database field with a different one. I notice that when I compare the old and new queries on my database, the number of returned rows is different. I know this is more of an SQL question, but thought I would post anyways. Any help would be appreciated!

The old and new queries are pasted below. Sorry, they're kinda long.

Thanks,
TY

Competitive Lease Expiry Report – OLD Query.
SELECT DISTINCT ric_compet_prod.compet_prod_id, ric_compet_prod.create_date, ric_compet_prod.product_code, ric_compet_prod.product_descr, ric_compet_prod.install_date, ric_compet_prod.contract_start_dt, ric_compet_prod.lease_expiry_dt, ric_compet_prod.lease_term, ric_compet_prod.machine_sell_price, au_company.name, ric_compet_prod.create_emp, au_address.addrline_1, au_address.city, au_address.post_code, au_contact.first_name, au_contact.last_name, ric_compet_prod.rec_del_ind, ric_compet_prod.locale_code, au_company.sls_terr_code, au_sls_terr.descr, au_company.phone_num
FROM (((ric_compet_prod ric_compet_prod INNER JOIN au_company au_company ON ric_compet_prod.comp_id=au_company.comp_id) INNER JOIN au_contact au_contact ON au_company.cont_id_pri=au_contact.cont_id) INNER JOIN au_address au_address ON au_company.addr_id_main=au_address.addr_id) INNER JOIN au_sls_terr au_sls_terr ON au_company.sls_terr_code=au_sls_terr.sls_terr_code
WHERE ric_compet_prod.rec_del_ind='N' AND au_company.name LIKE N'%' AND (ric_compet_prod.lease_expiry_dt>={ts '2009-04-01 00:00:00'} AND ric_compet_prod.lease_expiry_dt<{ts '2009-06-18 00:00:00'}) AND ric_compet_prod.locale_code=1033 AND au_sls_terr.descr LIKE N'%' AND ((au_company.sls_terr_code LIKE N'732%' OR au_company.sls_terr_code LIKE N'735%' OR au_company.sls_terr_code LIKE N'750%' OR au_company.sls_terr_code LIKE N'832%') OR (au_company.sls_terr_code LIKE N'526%' OR au_company.sls_terr_code LIKE N'550%' OR au_company.sls_terr_code LIKE N'553%' OR au_company.sls_terr_code LIKE N'554%' OR au_company.sls_terr_code LIKE N'555%' OR au_company.sls_terr_code LIKE N'557%' OR au_company.sls_terr_code LIKE N'558%') OR (au_company.sls_terr_code LIKE N'317%' OR au_company.sls_terr_code LIKE N'318%' OR au_company.sls_terr_code LIKE N'323%' OR au_company.sls_terr_code LIKE N'818%' OR au_company.sls_terr_code LIKE N'823%') OR (au_company.sls_terr_code LIKE N'312%' OR au_company.sls_terr_code LIKE N'315%' OR au_company.sls_terr_code LIKE N'316%' OR au_company.sls_terr_code LIKE N'319%' OR au_company.sls_terr_code LIKE N'320%' OR au_company.sls_terr_code LIKE N'815%') OR (au_company.sls_terr_code LIKE N'508%' OR au_company.sls_terr_code LIKE N'509%' OR au_company.sls_terr_code LIKE N'511%' OR au_company.sls_terr_code LIKE N'513%' OR au_company.sls_terr_code LIKE N'527%' OR au_company.sls_terr_code LIKE N'800%' OR au_company.sls_terr_code LIKE N'808%' OR au_company.sls_terr_code LIKE N'811%') OR (au_company.sls_terr_code LIKE N'730%' OR au_company.sls_terr_code LIKE N'731%' OR au_company.sls_terr_code LIKE N'733%' OR au_company.sls_terr_code LIKE N'734%' OR au_company.sls_terr_code LIKE N'736%' OR au_company.sls_terr_code LIKE N'737%' OR au_company.sls_terr_code LIKE N'738%' OR au_company.sls_terr_code LIKE N'760%' OR au_company.sls_terr_code LIKE N'765%' OR au_company.sls_terr_code LIKE N'770%' OR au_company.sls_terr_code LIKE N'836%') OR (au_company.sls_terr_code LIKE N'501%' OR au_company.sls_terr_code LIKE N'502%' OR au_company.sls_terr_code LIKE N'503%' OR au_company.sls_terr_code LIKE N'504%' OR au_company.sls_terr_code LIKE N'507%' OR au_company.sls_terr_code LIKE N'590%' OR au_company.sls_terr_code LIKE N'803%' OR au_company.sls_terr_code LIKE N'804%' OR au_company.sls_terr_code LIKE N'807%'))

NEW Competitive Lease Expiry – NEW SQL Query (replaced au_sls_terr.descr with au_sls_terr_d_vw.long_descr)
SELECT DISTINCT ric_compet_prod.compet_prod_id, ric_compet_prod.create_date, ric_compet_prod.product_code, ric_compet_prod.product_descr, ric_compet_prod.install_date, ric_compet_prod.contract_start_dt, ric_compet_prod.lease_expiry_dt, ric_compet_prod.lease_term, ric_compet_prod.machine_sell_price, au_company.name, ric_compet_prod.create_emp, au_address.addrline_1, au_address.city, au_address.post_code, au_contact.first_name, au_contact.last_name, ric_compet_prod.rec_del_ind, ric_compet_prod.locale_code, au_company.sls_terr_code, au_company.phone_num, au_sls_terr_d_vw.long_descr
FROM bcrm434.dbo.au_sls_terr_d_vw au_sls_terr_d_vw INNER JOIN ((((ric_compet_prod ric_compet_prod INNER JOIN au_company au_company ON ric_compet_prod.comp_id=au_company.comp_id) INNER JOIN au_contact au_contact ON au_company.cont_id_pri=au_contact.cont_id) INNER JOIN au_address au_address ON au_company.addr_id_main=au_address.addr_id) INNER JOIN au_sls_terr au_sls_terr ON au_company.sls_terr_code=au_sls_terr.sls_terr_code) ON au_sls_terr_d_vw.sls_terr_code=au_sls_terr.sls_terr_code
WHERE ric_compet_prod.rec_del_ind='N' AND au_company.name LIKE N'%' AND (ric_compet_prod.lease_expiry_dt>={ts '2009-04-01 00:00:00'} AND ric_compet_prod.lease_expiry_dt<{ts '2009-06-18 00:00:00'}) AND ric_compet_prod.locale_code=1033 AND au_sls_terr_d_vw.long_descr LIKE N'%' AND ((au_company.sls_terr_code LIKE N'732%' OR au_company.sls_terr_code LIKE N'735%' OR au_company.sls_terr_code LIKE N'750%' OR au_company.sls_terr_code LIKE N'832%') OR (au_company.sls_terr_code LIKE N'526%' OR au_company.sls_terr_code LIKE N'550%' OR au_company.sls_terr_code LIKE N'553%' OR au_company.sls_terr_code LIKE N'554%' OR au_company.sls_terr_code LIKE N'555%' OR au_company.sls_terr_code LIKE N'557%' OR au_company.sls_terr_code LIKE N'558%') OR (au_company.sls_terr_code LIKE N'317%' OR au_company.sls_terr_code LIKE N'318%' OR au_company.sls_terr_code LIKE N'323%' OR au_company.sls_terr_code LIKE N'818%' OR au_company.sls_terr_code LIKE N'823%') OR (au_company.sls_terr_code LIKE N'312%' OR au_company.sls_terr_code LIKE N'315%' OR au_company.sls_terr_code LIKE N'316%' OR au_company.sls_terr_code LIKE N'319%' OR au_company.sls_terr_code LIKE N'320%' OR au_company.sls_terr_code LIKE N'815%') OR (au_company.sls_terr_code LIKE N'508%' OR au_company.sls_terr_code LIKE N'509%' OR au_company.sls_terr_code LIKE N'511%' OR au_company.sls_terr_code LIKE N'513%' OR au_company.sls_terr_code LIKE N'527%' OR au_company.sls_terr_code LIKE N'800%' OR au_company.sls_terr_code LIKE N'808%' OR au_company.sls_terr_code LIKE N'811%') OR (au_company.sls_terr_code LIKE N'730%' OR au_company.sls_terr_code LIKE N'731%' OR au_company.sls_terr_code LIKE N'733%' OR au_company.sls_terr_code LIKE N'734%' OR au_company.sls_terr_code LIKE N'736%' OR au_company.sls_terr_code LIKE N'737%' OR au_company.sls_terr_code LIKE N'738%' OR au_company.sls_terr_code LIKE N'760%' OR au_company.sls_terr_code LIKE N'765%' OR au_company.sls_terr_code LIKE N'770%' OR au_company.sls_terr_code LIKE N'836%') OR (au_company.sls_terr_code LIKE N'501%' OR au_company.sls_terr_code LIKE N'502%' OR au_company.sls_terr_code LIKE N'503%' OR au_company.sls_terr_code LIKE N'504%' OR au_company.sls_terr_code LIKE N'507%' OR au_company.sls_terr_code LIKE N'590%' OR au_company.sls_terr_code LIKE N'803%' OR au_company.sls_terr_code LIKE N'804%' OR au_company.sls_terr_code LIKE N'807%'))

 

[lbass]

You added the table/view 'au_sls_terr_d_vw' with an inner join and that could have caused the results of the second query to have fewer rows. It would help to know how many rows each query returned, and which is the correct number for your purposes. While you could change the join to the view to a left outer join, you are also selecting on that table which would effectively undo the left join--unless you add the selection in the From clause like this:

FROM ((((ric_compet_prod ric_compet_prod
INNER JOIN au_company au_company ON ric_compet_prod.comp_id=au_company.comp_id)
INNER JOIN au_contact au_contact ON au_company.cont_id_pri=au_contact.cont_id)
INNER JOIN au_address au_address ON au_company.addr_id_main=au_address.addr_id)
INNER JOIN au_sls_terr au_sls_terr ON au_company.sls_terr_code=au_sls_terr.sls_terr_code)
left outer join bcrm434.dbo.au_sls_terr_d_vw au_sls_terr_d_vw ON au_sls_terr_d_vw.sls_terr_code=au_sls_terr.sls_terr_code and
au_sls_terr_d_vw.long_descr LIKE N'%'

You would also have to remove that criterion from the where clause.

-LB

 

[TeresaSalesPlus]

Thanks LB,

The first (older) query returned 969 rows. The newer one returned 1019 rows.

I'm going to try running the query with your FROM clause.

TY

 

[TeresaSalesPlus]

I ran the query replacing the old FROM clause with the clause you suggested (Thanks!) I got the same number of rows as the newer query (1019 rows)

Thank you.
TY

 

[lbass]

If the second query is resulting in more rows, then it is due to the addition of the new table. If you place the linking field from each of the two tables (au_sls_terr_d_vw and au_sls_terr.sls_terr_code) in the detail section, you will be able to see where the new table is adding a row.

-LB

 

[TeresaSalesPlus]

I think the difference in the number of rows is due to some records being repeated...

Would the use of Left Outer Join make repeats possible?

Thanks,
TY

 

[lbass]

You had the same number of records with a left or an inner join, so there is some field in the new table that has more than one record per the linking field. Changing the join will not fix that.

-LB

 

[TeresaSalesPlus]

I found there is an extra field in the table that the view is based on.

Thanks very much, LB!