SQL Server Performance Forum – Threads Archive

Problem With Multiple Query

select
———-1 col
case when sum(case when curr_code is null then 1 else 0 end) > 0
then 989796959
else
CASE WHEN (SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN ISNULL(user.fn_1(rpt.reg_id, rpt.v_id,
rpt.p_id, rpt.mon_yr, 1,’AMT1′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id,
rpt.p_id, rpt.mon_yr, 1,’AMT2′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id,
rpt.p_id, rpt.mon_yr, 1,’AMT3′), 0)))
ELSE
0 END)) = 0 THEN
0
ELSE
(POWER((SUM((
CASE WHEN SUBSTRING(CONVERT(VARCHAR(10),
rpt.mon_yr), 5, 2) = substring(convert(varchar,rpt.mon_yr),5,6) THEN
(
CASE WHEN ‘200306’ >=
rpt.mon_yr THEN
ISNULL(AMT1, ISNULL(AMT2,
ISNULL(AMT3, 0)))
ELSE
ISNULL(AMT1, ISNULL(AMT2, 0))
END) ELSE
0
END)) / (SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2)
WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN
ISNULL(user.fn_1(rpt.reg_id,
rpt.v_id, rpt.p_id, rpt.mon_yr,
1,’AMT1′), ISNULL(user.fn_1(rpt.reg_id,
rpt.v_id, rpt.p_id, rpt.mon_yr,
1,’AMT2′), ISNULL(user.fn_1(rpt.reg_id,
rpt.v_id, rpt.p_id, rpt.mon_yr,
1,’AMT3′), 0)))
ELSE
0
END)) ), (12.0)) – 1) * 100
END end
,
——–2 col
case when sum(case when ref_region.currency_code is null then 1 else 0 end) > 0 then 989796959 else CASE WHEN (SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’AC_NO_ACCT’),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’FC_NO_ACCT’),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’BD_NO_ACCT’), 0)))
ELSE
0
END)) = 0 THEN
0
ELSE
(POWER((SUM((
CASE WHEN SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) = substring(convert(varchar,rpt.mon_yr),5,6) THEN
(
CASE WHEN ‘200306’ >= rpt.mon_yr THEN
ISNULL(ACCT1, ISNULL(ACCT2, ISNULL(ACCT3, 0)))
ELSE
ISNULL(ACCT1, ISNULL(ACCT2, 0))
END)
ELSE
0
END)) / (SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT1′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT2′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT3′), 0)))
ELSE 0 END)) ), (12.0)) – 1) * 100
END end FROM ………………………
The above query returns two columns, each column has some case..end conditions..
when i execute removing one of the columns it works fine..but when i execute both the columns combined, the second column returns a wrong value.
I had just modified the query by giving ‘0’(in single quotes) or any other number other than 0 in the else condition, then it worked fine..
I am using SQL Server2000 and
i have a group by clause on mon_yr and p_id… Also..If i change 0 to any other number(1 or 0.0 or null….) then it works fine…or if i change the 0 in the isNull function to 0.0 then also it works..my function fn_1 returns a numeric(21,6). In the below three isNull function if i comment atleast one of them it executes properly..
Why is it so? Can someone help me out solving this????

---

Could you post table definition if possible with a few lines, somthing we can work on? Bambola.

---

Thanks for ur suggestion…. I am using SQL Server2000 and
i have a group by clause on mon_yr and p_id… Below code snippet..If i change 0 to any other number(1 or 0.0 or null….) then it works fine…or if i change the 0 in the isNull function to 0.0 then also it works..my function fn_1 returns a numeric(21,6). In the below three isNull function if i comment atleast one of them it executes properly.. SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT1′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT2′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT3′), 0)))
ELSE 0 END)) ), (12.0)) – 1) * 100
I had modified the above code by giving different function names(all the functions have the same code) then it is working fine.. SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT1′),
ISNULL(user.fn_2(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT2′),
ISNULL(user.fn_3(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT3′), 0)))
ELSE 0 END)) ), (12.0)) – 1) * 100 i know it is not a good way to program.. Is there a solution for this.??? Thanks in advance

---

]]>