SQL query help

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 :frowning_face:

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 :wink:

Great! :grinning: