retrieving a summed value from a recordset.. very odd one!

[Pete]

I have a simple sql query which contains a sum function, this is then given
a column name.

If i run it in query analyser i get a correct result.
Yet if i do a response.write rec("columnname") i get no result

There is no asp error saying cannot find item .... etc.. so i know it is
finding the item - it just has no value in there.
Anyone ever seen this?

query as below:
Select S.Name, I.start,I.nights, S.City, R.Name,
sum(CASE WHEN A.Occ = 4 then 4 * S4
WHEN A.OCC = 3 then 3 * S3
WHEN A.OCC = 2 then 2 * S2
WHEN A.OCC = 1 then S1
END) TotalC
from itinerary I
left outer join allocation A on I.ID = A.Itineraryid and A.Occ > 0
inner join supplier S on I.SupplierID = S.ID
inner join region r on r.id=s.cid
where quoteid =91 And I.Qty > 0
group by I.QuoteID, S.ID, S.Name, S.City, R.Name, I.start, I.nights
order by S.Name

output asp as below:

rsprop.open sql, Con
...
...

<snip>
...

<%=rsprop("TotalC")%>

All other values returned display fine on my page.

[Aaron Bertrand - MVP]

Try changing your query to:

SUM(...) AS TotalC
TotalC = SUM(...)

or refer to rs(5) instead of rs("TotalC")

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Pete" <pbarnsley@discovertravelandtours.com> wrote in message
news:3fcf518b$1@news.star.co.uk...

> I have a simple sql query which contains a sum function, this is then

given

> a column name.
>
> If i run it in query analyser i get a correct result.
> Yet if i do a response.write rec("columnname") i get no result
>
> There is no asp error saying cannot find item .... etc.. so i know it is
> finding the item - it just has no value in there.
> Anyone ever seen this?
>
> query as below:
> Select S.Name, I.start,I.nights, S.City, R.Name,
> sum(CASE WHEN A.Occ = 4 then 4 * S4
> WHEN A.OCC = 3 then 3 * S3
> WHEN A.OCC = 2 then 2 * S2
> WHEN A.OCC = 1 then S1
> END) TotalC
> from itinerary I
> left outer join allocation A on I.ID = A.Itineraryid and A.Occ > 0
> inner join supplier S on I.SupplierID = S.ID
> inner join region r on r.id=s.cid
> where quoteid =91 And I.Qty > 0
> group by I.QuoteID, S.ID, S.Name, S.City, R.Name, I.start, I.nights
> order by S.Name
>
> output asp as below:
>
> rsprop.open sql, Con
> ..
> ..
>
> <snip>
> ..
>
> <%=rsprop("TotalC")%>
>
> All other values returned display fine on my page.
>
>

[Pete]

Heh...

no it was much simpler than that, my recordset on the asp page was using a
different value than the query analyser which was wrong... oops!

sorry to waste time!.
"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:O1l4lFouDHA.3536@tk2msftngp13.phx.gbl...

> Try changing your query to:
>
> SUM(...) AS TotalC
> TotalC = SUM(...)
>
> or refer to rs(5) instead of rs("TotalC")
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "Pete" <pbarnsley@discovertravelandtours.com> wrote in message
> news:3fcf518b$1@news.star.co.uk...

> > I have a simple sql query which contains a sum function, this is then

> given

> > a column name.
> >
> > If i run it in query analyser i get a correct result.
> > Yet if i do a response.write rec("columnname") i get no result
> >
> > There is no asp error saying cannot find item .... etc.. so i know it is
> > finding the item - it just has no value in there.
> > Anyone ever seen this?
> >
> > query as below:
> > Select S.Name, I.start,I.nights, S.City, R.Name,
> > sum(CASE WHEN A.Occ = 4 then 4 * S4
> > WHEN A.OCC = 3 then 3 * S3
> > WHEN A.OCC = 2 then 2 * S2
> > WHEN A.OCC = 1 then S1
> > END) TotalC
> > from itinerary I
> > left outer join allocation A on I.ID = A.Itineraryid and A.Occ > 0
> > inner join supplier S on I.SupplierID = S.ID
> > inner join region r on r.id=s.cid
> > where quoteid =91 And I.Qty > 0
> > group by I.QuoteID, S.ID, S.Name, S.City, R.Name, I.start, I.nights
> > order by S.Name
> >
> > output asp as below:
> >
> > rsprop.open sql, Con
> > ..
> > ..
> >
> > <snip>
> > ..
> >
> > <%=rsprop("TotalC")%>
> >
> > All other values returned display fine on my page.
> >
> >

>
>