if statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if statement

Author

Topic

kd15223
Starting Member

4 Posts

Posted - 2008-07-09 : 12:07:24

hello i'm new at this and i ran into a little problem. i have to tables i need data from 1 i need everything the other just 2 fields - i used a inner join which give me what i needed now i need a IF statement. here is the problem Exp..
i need this IF LOC field = 'LOBBY' then i need to subtract whatever is in the 'LOCBIN' field from the 'ONHAND' field and rename as 'without lobby' and if nothing is there do nothing but show ONHAND

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-09 : 12:34:21

What you're looking for is the CASE statement. As a rough example (since I don't know your table structure

SELECT CASE LOC WHEN 'Lobby' THEN OnHand-LocBin ELSE OnHand END AS Stock FROM < rest of sql statement here >

--
Gail Shaw
SQL Server MVP

kd15223
Starting Member

4 Posts

Posted - 2008-07-09 : 15:03:29

ok that worked thank you but the problem is i only want to see the Stock not all totals here is what i have

SELECT CASE inonhd.flocation WHEN 'TRAILER 1'
THEN "inmast"."fonhand"-"inonhd"."fonhand" ELSE "inonhd"."fonhand" END AS Stock,"inmast"."fpartno","inmast"."fcusrchr2",
"inmast"."fonorder",
"inmast"."fproqty",
"inmast"."fonhand" ,
--"inonhd"."fonhand" as On_Trailer,
"inonhd"."flocation",
"inmast"."fbook",
"inmast"."fdescript",
"inmast"."fac",
"inmast"."fytdiss",
"inmast"."fmtdiss"
FROM dbo.inmast
Inner Join dbo.inonhd on inmast.fpartno =inonhd.fpartno
and "inmast"."fcusrchr2"<>'' and inmast.fpartno='aamb02'

i get this
Stock fpartno (others) fonhand flocation
120.00 aamb 136.00 overstock
11.00 aamb 136.00 shipping
131.00 aamb 136.00 trailer 1

all we're trying to do is get what is in stock without trailer by part number

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-09 : 15:19:42

Can you post the results that you want?

--
Gail Shaw
SQL Server MVP

kd15223
Starting Member

4 Posts

Posted - 2008-07-09 : 15:33:50

stock fpartno fonhd
and afew other fields that are listed

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-10 : 02:14:53

An example please. You say that you're getting this:

Stock fpartno (others) fonhand flocation
120.00 aamb 136.00 overstock
11.00 aamb 136.00 shipping
131.00 aamb 136.00 trailer 1

so, what does the result set you want look like?

--
Gail Shaw
SQL Server MVP

kd15223
Starting Member

4 Posts

Posted - 2008-07-10 : 12:20:38

sorry everybody is putting their input in over here making me crazy – but what they want to see -- is for every part number what we have is stock after the trailer is subtracted out. if it is in the trailer loc(inonhd"."fonhand") take just the trailer loc and show that one not all three if it's not in trailer loc then take it from "inmast"."fonhand" and show that total thanks for your help on this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 14:09:46

May be this. your requirement is still not clear enough though

SELECT  fpartno,
CASE WHEN SUM(CASE WHEN  inonhd.flocation='TRAILER 1' THEN 1 ESLE ) END) >0 THEN SUM(CASE WHEN  inonhd.flocation='TRAILER 1' THEN Stock ELSE 0 END)
ELSE SUM(CASE WHEN  inonhd.flocation<>'TRAILER 1' THEN Stock ELSE 0 END) AS Stock
FROM dbo.inmast 
Inner Join dbo.inonhd on inmast.fpartno =inonhd.fpartno
and "inmast"."fcusrchr2"<>'' and inmast.fpartno='aamb02'
group by inmast.fpartno

if this is still not what you want,please explain with some data what you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources