Inconsistent results in Query Analyzer and ASP page 1

[achmo]

Hello,

I'm using this query on an ASP page (the database is SQL Server):

SELECT g.groupcode,g.groupname,isnull(sum(p.vallastyear),0) AS Kamutlastyear,
isnull(sum(p.valthisyear),0) AS Kamutthisyear FROM tblgroups g, tbltrufot t, tblparetob p WHERE t.newtechnology=0 AND g.groupcode=t.groupcode AND p.trufacode=t.trufacode GROUP BY g.groupname,g.groupcode ORDER By g.groupcode DESC

The query works without "t.newtechnology=0"; after adding it, the query still works on the SQL Server's Qyery Analyzer but on the asp page I get an empty recordset. On the database, the field in question (newtechnology) contains 0 or 1, and when I change the query to "t.newtechnology=1" it works just fine.

What on earth can cause this inconsistency?

Thanks for your help

Yael

 

[tlbroadbent]

Try this query.

SELECT
g.groupcode,
g.groupname,
isnull(sum(p.vallastyear),0) AS Kamutlastyear,
isnull(sum(p.valthisyear),0) AS Kamutthisyear

FROM tblgroups g
INNER JOIN (SELECT groupcode, trufacode
FROM tbltrufot WHERE newtechnology=0) As t
ON g.groupcode=t.groupcode
INNER JOIN tblparetob p
ON t.trufacode=p.trufacode

GROUP BY g.groupname, g.groupcode
ORDER By g.groupcode DESC

----------------------------------

If that doesn't work then I suspect you'll need to use at least one OUTER JOIN to get the result you want. The following example uses a LEFT OUTER JOIN.

SELECT
g.groupcode,
g.groupname,
isnull(sum(p.vallastyear),0) AS Kamutlastyear,
isnull(sum(p.valthisyear),0) AS Kamutthisyear

FROM (tblgroups g
INNER JOIN (SELECT groupcode, trufacode
FROM tbltrufot WHERE newtechnology=0) As t
ON g.groupcode=t.groupcode)
LEFT JOIN tblparetob p
ON t.trufacode=p.trufacode

GROUP BY g.groupname, g.groupcode
ORDER By g.groupcode DESC Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Madsam]

Hello! Differences in ASP from the Query tool are often related to SET OPTIONS. The best way to begin digging through the differences is to fire-up SQL Trace and point to the website you are trying to debug. Run (or refresh) the page in question and capture the actual SQL that is executed on the web. Then, paste that into the Query tool and begin examining the differences.

If you are using Stored Procedures executed from the web (which is *nearly always* the best idea), you can reset the offending SET OPTIONS to off inside of the SP and be back to good.

Hint: ALWAYS use your trace facility when trying to determine what's going on between the web and the DB. This will give you the EXACT code that runs, and help you debug any problems.

 

[tlbroadbent]

Madsam,

Excellent advice. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[achmo]

Thanks! I will try your advices and let you know what was the result

Yael

 

[achmo]

ok, this was my bad... I've misinterpereted the error message - there was nothing wrong with the query itself and I didn't get an empty recordset. What I did with the recordset was causing the problem...

Sorry for the bother and thanks for your help.

Yael