March 11, 2004 at 1:59 pm
Hello
I have the following problem in my Tsql query :
select ref,armazem,sum(valor) from
(select(select ref,(case when armazem=1 then 'ARM1' else case when armazem=3 then 'ARM3' end end) as armazem,
(case when armazem=1 then sum(epcpond) else sum(-epcpond) end) as valor
from sl where sl.trfa=1 and origem='BO' and (sl.armazem=1 or sl.armazem=3)
and sl.datalc between '20031101' and '20031130' group by ref,armazem )) as derrivedtable
group by ref,armazem
error on query analiser :
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'derrivedtable'.
i want to return 2 columns with armazem1 and armazem 2 , and for each rows referencia(article) my query returns the sum of the field EPCPOND to columns armazem1 or armazem2.
Do you have best practice to make crosstab querys than this one with an sub-select ? could you give me sintaxes to make that ?
I don´t understand why, could you give me some help.
also i want to ask you if you know any programs to create easly crosstab query for SQL SERVER .
Best regards
Luis santos
March 11, 2004 at 3:09 pm
Working from your narrative rather than that interesting code, perhaps you want something like this:
SELECT Ref, SUM(CASE Armazen WHEN 1 THEN EpcPond END) ARM1, SUM(CASE Amazen WHEN 3 THEN -EpcPond END) ARM3
FROM SL
WHERE TrfA = 1 AND Origem = 'BO' AND Armazem IN (1,3) AND DataLC BETWEEN '20031101' and '20031130'
GROUP BY Ref
--Jonathan
March 11, 2004 at 3:27 pm
Thanks Jonathan, the query work ok
Luis Santos
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply