I have this query, but don´t work.
Can someone help me??
select 'Armazém '+ltrim(str(armazem)) as armazem,sz.nome as nome,
sum(qtt) as qtt,sum(peso) as peso from (
select armazem,(case when cm<50 then qtt else -qtt end) as qtt,
(case when cm<50 or cm=77 then 1 else -1 end)*(case when origem='BO' then (select u_pesogr from bi
where sl.bistamp=bi.bistamp) else (case when origem='FT' then (select u_pesogr from fi
where sl.fistamp=fi.fistamp) else (case when origem='FO' then (select u_pesogr from fn
where sl.fnstamp=fn.fnstamp) else u_pesogr end) end) end) as peso from sl(nolock)
inner join sz(nolock) on sz.no=sl.armazem
where ref='1220401')x
group by 'Armazém '+ltrim(str(armazem)),nome
hi
please click arrow to the left for .. YOUR Sql formatted
SELECT 'Armazém ' + Ltrim(Str(armazem)) AS armazem,
sz.nome AS nome,
Sum(qtt) AS qtt,
Sum(peso) AS peso
FROM (
SELECT armazem
, ( CASE WHEN cm < 50 THEN qtt ELSE -qtt END ) AS qtt
, ( CASE WHEN cm < 50 OR cm = 77 THEN 1 ELSE -1 END )
* ( CASE WHEN origem = 'BO' THEN (SELECT u_pesogr FROM bi WHERE sl.bistamp = bi.bistamp)
ELSE
( CASE WHEN origem = 'FT' THEN (SELECT u_pesogr FROM fi WHERE sl.fistamp = fi.fistamp)
ELSE
( CASE WHEN origem = 'FO' THEN (SELECT FROM fn WHERE sl.fnstamp = fn.fnstamp) ELSE u_pesogr
END
) END
) END ) AS peso
FROM sl(nolock) INNER JOIN sz(nolock) ON sz.no = sl.armazem WHERE ref = '1220401') x
GROUP BY 'Armazém ' + Ltrim(Str(armazem)),
nome
one way to get it to work correctly
example let says you have ..
select *
from
(
select a ,
select b ,
select c ,
select d
)
first do select a .. get it to work
then add b
select *
from
(
select a ,
select b ,
)
get this to work
then add next c
select *
from
(
select a ,
select b ,
select c ,
)
get this to work ..
hi
i tried to clean the SQL .. .. to make it simple !!! please take a look
SELECT
'Armazém ' + Ltrim(Str(armazem)) AS armazem
, sz.nome AS nome
, Sum(CASE WHEN cm < 50 THEN qtt ELSE -qtt END )
, Sum(
( CASE WHEN cm < 50 OR cm = 77 THEN 1 ELSE -1 END )
*
( CASE origem WHEN 'BO' THEN bi.u_pesogr
WHEN 'FT' THEN fi.u_pesogr
WHEN 'FO' THEN fn.???? ELSE fn.u_pesogr
END )
)
FROM
sl(nolock)
INNER JOIN
sz(nolock)
ON sz.no = sl.armazem
INNER JOIN
bi(nolock)
ON sl.bistamp = bi.bistamp
INNER JOIN
fi(nolock)
ON sl.fistamp = fi.fistamp
INNER JOIN
fn(nolock)
ON sl.fnstamp = fn.fnstamp
WHERE
ref = '1220401'
GROUP BY
'Armazém ' + Ltrim(Str(armazem))
, nome
Here's my preferred approach to use. Whatever way you choose, don't use INNER JOIN for the bi, fi and fn tables, use LEFT OUTER JOIN instead. INNER JOIN would almost certainly cause you to lose rows in the final SELECT result (give that they are joined to only for specific conditions).
Also, the keyword "WITH" is now required for using NOLOCK, so it's far better to start coding it now, even if your version of SQL doesn't yet require it.
SELECT
'Armazém '+LTRIM(STR(x.armazem)) as armazem,
x.nome as nome,
SUM(x.qtt) as qtt,SUM(x.peso) as peso
FROM (
SELECT
armazem,
(CASE WHEN cm<50 THEN qtt ELSE -qtt END) AS qtt,
(CASE WHEN cm<50 or cm=77 THEN 1 ELSE -1 END) *
(CASE WHEN origem='BO' THEN (SELECT u_pesogr FROM dbo.bi WHERE sl.bistamp=bi.bistamp)
WHEN origem='FT' THEN (SELECT u_pesogr FROM dbo.fi WHERE sl.fistamp=fi.fistamp)
WHEN origem='FO' THEN (SELECT u_pesogr FROM dbo.fn WHERE sl.fnstamp=fn.fnstamp)
ELSE u_pesogr END) as peso
FROM dbo.sl WITH(NOLOCK)
INNER JOIN dbo.sz WITH(NOLOCK) ON sz.no=sl.armazem
WHERE ref='1220401'
) AS x
GROUP BY LTRIM(STR(x.armazem)),x.nome
1 Like
Thank you for your help, but don't work 
hi
did you try
slowly building piece by piece .. making sure each part works
select a .. ok this works
select a , select b ok this works ..... next part
select a , select b , select c+d+e ok this works ...... next part
Is that for both suggested solutions?
Thank you for your help, now works fine 
