Urgent Problem

Hi All,

I am facing very strange problem with one query. The query is generated by the application
itself(adhoc query). This query is combination of 3 queries. If we execute all the 3 queries seperately say D1,D2 and D3.
Then query D1 gives 13 records
query D2 gives 6 records
query D3 gives 13 records.
But when we combine query D1 and D2(D) it again gives 13 records. When we combine D3 with D1 and D2 and execute complete query
it gives only 4 records whereas it should give same 13 records.

Where condition in all the queries are absolutely same.

complete query using "first_name" and "last_name" as joining condition between query D3 and (D1+D2)(D).

At the same time when i add "ltrim" and "rtrim" in the joining clause complete query gives expected 13 records

Please see below the query and let me know if any suggestion.

select * from
--Start of D(combination D1 & D2 inline queries)
(SELECT * FROM
--Start of D1
(select
count(distinct T161609."OPTY_ID") as c1,
sum(T161609."PRIM_REP_REVN_AMT") as c2,
T40043."LVL6ANC_DIVN" as c3,
T40043."LVL7ANC_DIVN" as c4,
T158095."PRIM_REP_GRD_CD" as c5,
T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME" as c6,
T40043."LVL5ANC_DIVN" as c7
from
"W_SSTAGE_D" T31842,
"W_DAY_D" T33083,
"W_POSITION_D" T38942,
"WCX_RPTG_PARM" T148553,
"WCX_OPPORTUNITY_SCD" T158095,
"WCX_PIPELINE_POSITION_SALES_F_SNAP" T161609,
"W_POSITION_DH" T40043
where
T148553."ROW_WID" >= T161609."FACT_STRT_DT_WID"
and T158095."ROW_WID" = T161609."OPTY_WID"
and T148553."ROW_WID" < T161609."FACT_END_DT_WID"
and T31842."ROW_WID" = T161609."CURR_STG_WID"
and T33083."ROW_WID" = T161609."PRIM_REP_REVN_DT_WID"
and T38942."ROW_WID" = T40043."ROW_WID"
and T38942."ROW_WID" = T161609."POS_WID"
and case when T158095."PRIM_REP_CMTD_INDC" = 1 then 'Y' else 'N' end = 'Y'
and T158095."PRIM_REP_GRD_CD" = '1'
and T148553."RPTG_PARM_DT" = convert(datetime, '2005-10-26 00:00:00', 120)
and case 8.0 when 1 then T40043."LVL1ANC_POSTN_ID" when 2 then T40043."LVL2ANC_POSTN_ID"
when 3 then T40043."LVL3ANC_POSTN_ID" when 4 then T40043."LVL4ANC_POSTN_ID"
when 5 then T40043."LVL5ANC_POSTN_ID" when 6 then T40043."LVL6ANC_POSTN_ID"
when 7 then T40043."LVL7ANC_POSTN_ID" when 8 then T40043."LVL8ANC_POSTN_ID"
when 9 then T40043."TOP_LVL_POSTN_ID" end = '1-14ORTY'
and T31842."METHOD_NAME" = 'Default Sales Methodology'
and case when T161609."RPTB_INDC" = 1 then 'Y' else 'N' end = 'Y'
and (T158095."STAT_TXT" = 'Closed' or T158095."STAT_TXT" = 'Open')
and (T158095."OPTY_TYP_TXT" = 'New Contract' or T158095."OPTY_TYP_TXT" = 'Renewal')
and T33083."PER_NAME_MONTH" between '2005 / 10' and '2005 / 10'
group by T40043."LVL5ANC_DIVN", T40043."LVL6ANC_DIVN",
T40043."LVL7ANC_DIVN", T158095."PRIM_REP_GRD_CD",
T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME"
) D1,-- End of D1
-- Start of D2
(select
count(distinct T161609."OPTY_ID") as c8,
T40043."LVL5ANC_DIVN" as c9,
T158095."PRIM_REP_GRD_CD" as c10
from
"W_SSTAGE_D" T31842,
"W_DAY_D" T33083,
"W_POSITION_D" T38942,
"WCX_RPTG_PARM" T148553,
"WCX_OPPORTUNITY_SCD" T158095,
"WCX_PIPELINE_POSITION_SALES_F_SNAP" T161609,
"W_POSITION_DH" T40043
where
T148553."ROW_WID" >= T161609."FACT_STRT_DT_WID"
and T158095."ROW_WID" = T161609."OPTY_WID"
and T148553."ROW_WID" < T161609."FACT_END_DT_WID"
and T31842."ROW_WID" = T161609."CURR_STG_WID"
and T33083."ROW_WID" = T161609."PRIM_REP_REVN_DT_WID"
and T38942."ROW_WID" = T40043."ROW_WID"
and T38942."ROW_WID" = T161609."POS_WID"
and case when T158095."PRIM_REP_CMTD_INDC" = 1 then 'Y' else 'N' end = 'Y'
and T158095."PRIM_REP_GRD_CD" = '1'
and T148553."RPTG_PARM_DT" = convert(datetime, '2005-10-26 00:00:00', 120)
and case 8.0 when 1 then T40043."LVL1ANC_POSTN_ID" when 2 then T40043."LVL2ANC_POSTN_ID"
when 3 then T40043."LVL3ANC_POSTN_ID" when 4 then T40043."LVL4ANC_POSTN_ID"
when 5 then T40043."LVL5ANC_POSTN_ID" when 6 then T40043."LVL6ANC_POSTN_ID"
when 7 then T40043."LVL7ANC_POSTN_ID" when 8 then T40043."LVL8ANC_POSTN_ID"
when 9 then T40043."TOP_LVL_POSTN_ID" end = '1-14ORTY'
and T31842."METHOD_NAME" = 'Default Sales Methodology'
and case when T161609."RPTB_INDC" = 1 then 'Y' else 'N' end = 'Y'
and (T158095."STAT_TXT" = 'Closed' or T158095."STAT_TXT" = 'Open')
and (T158095."OPTY_TYP_TXT" = 'New Contract' or T158095."OPTY_TYP_TXT" = 'Renewal')
and T33083."PER_NAME_MONTH" between '2005 / 10' and '2005 / 10'
group by T40043."LVL5ANC_DIVN", T158095."PRIM_REP_GRD_CD"
) D2--End of D2
where
(D1.c5 = D2.c10 or (D1.c5 is null and D2.c10 is null)) and (D1.c7 = D2.c9)
)
D,--End of D
--Start of D3
(select
count(distinct T161609."OPTY_ID") as c4,
T40043."LVL5ANC_DIVN" as c5,
T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME" as c6
from
"W_SSTAGE_D" T31842,
"W_DAY_D" T33083,
"W_POSITION_D" T38942,
"WCX_RPTG_PARM" T148553,
"WCX_OPPORTUNITY_SCD" T158095,
"WCX_PIPELINE_POSITION_SALES_F_SNAP" T161609,
"W_POSITION_DH" T40043
where
T148553."ROW_WID" >= T161609."FACT_STRT_DT_WID"
and T158095."ROW_WID" = T161609."OPTY_WID"
and T148553."ROW_WID" < T161609."FACT_END_DT_WID"
and T31842."ROW_WID" = T161609."CURR_STG_WID"
and T33083."ROW_WID" = T161609."PRIM_REP_REVN_DT_WID"
and T38942."ROW_WID" = T40043."ROW_WID"
and T38942."ROW_WID" = T161609."POS_WID"
and case when T158095."PRIM_REP_CMTD_INDC" = 1 then 'Y' else 'N' end = 'Y'
and T158095."PRIM_REP_GRD_CD" = '1'
and T148553."RPTG_PARM_DT" = convert(datetime, '2005-10-26 00:00:00', 120)
and case 8.0 when 1 then T40043."LVL1ANC_POSTN_ID" when 2 then T40043."LVL2ANC_POSTN_ID"
when 3 then T40043."LVL3ANC_POSTN_ID" when 4 then T40043."LVL4ANC_POSTN_ID"
when 5 then T40043."LVL5ANC_POSTN_ID" when 6 then T40043."LVL6ANC_POSTN_ID"
when 7 then T40043."LVL7ANC_POSTN_ID" when 8 then T40043."LVL8ANC_POSTN_ID"
when 9 then T40043."TOP_LVL_POSTN_ID" end = '1-14ORTY'
and T31842."METHOD_NAME" = 'Default Sales Methodology'
and case when T161609."RPTB_INDC" = 1 then 'Y' else 'N' end = 'Y'
and (T158095."STAT_TXT" = 'Closed' or T158095."STAT_TXT" = 'Open')
and (T158095."OPTY_TYP_TXT" = 'New Contract' or T158095."OPTY_TYP_TXT" = 'Renewal')
and T33083."PER_NAME_MONTH" between '2005 / 10' and '2005 / 10'
group by T40043."LVL5ANC_DIVN", T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME"
) D3--End of D3
WHERE
D.C6=D3.C6


Thanks in advance,
aspire

Yes Madhivanan,

We combined query D1 and D2 adding where clause in the end..

When you use this sort of joining

SELECT
TABLE1,
TABLE2
WHERE
TABLE1.ID = TABLE2.ID

You are using an implicit INNER JOIN. This will filter out any records from TABLE1 which do not have corresponding records in TABLE2, and vice versa.

Its more readable to do this..

SELECT
TABLE1
INNER JOIN TABLE2 ON (TABLE1.ID = TABLE2.ID)

Then you can change to a LEFT JOIN, or FULL OUTER JOIN easily and see if that is why your result set is smaller than expected

Use FULL OUTER JOIN to get all the 13 records

Thanks everybody for your response..

Yes we can easily modify the query and get our desired resultset..

But here we just want to figure out why this query is producing different resultset.The same query is working fine on QA box and giving expected 13 records, but issues are being seen on Production box only..
This is giving us jitters..

Thanks
Aspire

If you would restore a backup of the production database on your development box, then you would see real-life data, and you would inevitably see the same results.

In your development system, you probably have matching values on all JOINs, so it doesn't matter if you use an INNER JOIN or a LEFT JOIN, you get the same number of rows.

The production system probably has unmatched values, so there an INNER JOIN will return less rows than a LEFT JOIN.

This is really something that you should be on the lookout for in the development stages.

quote:Originally posted by aspire

Thanks everybody for your response..

Yes we can easily modify the query and get our desired resultset..

But here we just want to figure out why this query is producing different resultset.The same query is working fine on QA box and giving expected 13 records, but issues are being seen on Production box only..
This is giving us jitters..

Thanks
Aspire

There are a number of SQL Server configuration options that can affect the results. Compatibility level, case-sensitivity, ANSI NULLS, and ANSI PADDING can all make identical queries give different results when run on identical data.

Check out sp_dbcmptlevel, case-sensitive instance of SQL Server, and ANSI compliance options in BOL (SQL Server help files).

Keith Payne
Technical Marketing Solutions
www.tms-us.com

Hi,

This is getting more interesting now. I took another query and executed the query on QA box and Prod box. On QA query is consistently showing 4 records. While on Prod query behaving strangely. Sometime it gives 3 records sometime it gives 7 records.

We checked out all the configuration settings (server configuration, database configuration). Settings are same on QA and Prod.

This is really surprising behaviour from sql server 2000.

I hope i m not bothering you guys..

Thanks,
Aspire.

You don't mention restoring a recent back-up of the production database in your test environment.

This should help clarifying whether it's an issue to do with actual data not conforming to the assumptions present in the design of the query.

Yes Adriaan,

We are performing regular backup and restore from prod to QA before the testing. But the results are still surprising.

Same Data, Same Server Configuration, Same Database Configuration.

Only difference is tempdb space. Production has around 4 GB free space in tempdb whereas in QA temp db free space is little less than 1 GB.

Thanks,
Aspire

So if you do a count(*) on the affected tables, on dev and on production, both value are identical ?

When you back up production server, make sure you choose to overwrite the existing file, if it exists. I wonder if you are choosing to append the backup to an existing file, and then when you restore onto dev server, you are restoring an out of date backup?