Select with If/then logic - 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

Select with If/then logic

Author

Topic

sbhegel
Starting Member

19 Posts

Posted - 2005-12-01 : 14:58:14

I need to do something like the following logic: (What I am trying to do is a weight conversion statement)

select
part,
desc,
net_wt,
net_wt_um,
pack_um,
pack_wt,
@somevariable = net_wt / (if net_wt_um <> pack_um then
(if exist(select conversion)
from table1
where field2 = pack_um)
then pack_wt / conversion
else pack_wt

Any thougts? I am basically just looking for a way to do if/else in select statements.

Thanks,
Scott

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-12-01 : 15:11:40

something like

select
part,
desc,
net_wt,
net_wt_um,
pack_um,
pack_wt,
CASE
WHEN net_wt_um <> pack_um AND EXISTS (select conversion from table1 where field2 = pack_um)
THEN pack_wt / conversion
ELSE pack_wt
END as SomeVariable

though I'm quite sure that our more enlightened members can write the code much more elegantly

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

sbhegel
Starting Member

19 Posts

Posted - 2005-12-01 : 15:53:24

Thanks,

That mostly works, but I won't be able to use the conversion field in the calculation because it is outside the EXISTS statement, is that correct?

I am getting an error of an unkown field.

Scott

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-12-02 : 08:25:58

I think it may be the FROM part of your statement that needs to be fixed. Would be useful to have more info - see

quote:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

sbhegel
Starting Member

19 Posts

Posted - 2005-12-02 : 11:22:15

Here is the code I am trying to get to work. I commented the part that is giving me the error. Basically from the if exists I am trying to get the um_conv field and then using this in a calculation. I believe since the um_conv is out of the scope of the if exists select that it doesn't know this variable.

SELECT
pt_mstr.pt_part as part,
pt_mstr.pt_desc1 as desc1,
dbo.udf_GetEntry(cd_det.cd_cmmt,1,';') as custdesc,
dbo.udf_GetEntry(cd_det.cd_cmmt,2,';') as caformat,
dbo.udf_GetEntry(cd_det.cd_cmmt,3,';') as plformat,
dbo.udf_GetEntry(cd_det.cd_cmmt,6,';') as eaformat,
(pt_mstr.pt__qad24 * pt_mstr.pt__qad25) as pallqty,
case
when pt_net_wt_um <> pt_drwg_size and exists(
select um_conv
from mfgprod..pub.um_mstr um_mstr
where (um_mstr.um_um = pt_mstr.pt_net_wt_um
and um_mstr.um_alt_um = pt_drwg_size
and um_mstr.um_part = ''))
then (round((pt_net_wt / (cast(pt_drwg_loc as numeric) /um_conv)),0)) /**** IT DOESN'T LIKE THE UM_CONV HERE */
else
round((pt_net_wt / cast(pt_drwg_loc as numeric)),0)
end as packqty,
pt_mstr.pt_drwg_loc as packsize,
pt_mstr.pt_drwg_size as packum,
dbo.udf_GetEntry(cd_det.cd_cmmt,4,';') as dist1,
dbo.udf_GetEntry(cd_det.cd_cmmt,5,';') as dist2,
pt_mstr.pt_user2 as brand,
pt_mstr.pt__qad24 as ti,
pt_mstr.pt__qad25 as hi,
pt_mstr.pt_status as status
INTO #ttsku
FROM mfgprod..pub.pt_mstr pt_mstr
left join mfgprod..pub.cd_det cd_det on
(cd_det.cd_ref = pt_mstr.pt_part
and cd_det.cd_type = 'MK'
and cd_det.cd_lang = 'US'
and cd_det.cd_seq = 0)
WHERE pt_part_type = 'FG'
and (pt_status = 'A' or pt_status = 'AMTO')
and (pt_drwg_loc <> '' and pt_drwg_loc <> '0')

Any way to either assign um_conv to a variable so I can use it in the calculation or am I doing this sub query wrong all together?

Thanks,
Scott

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-03 : 13:35:36

What is the error that you are getting?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

cfederl
Starting Member

26 Posts

Posted - 2005-12-03 : 18:37:56

You cannot do what you are attempting by nesting a select within the select portion of a SQL statement.

Easiest is to "LEFT OUTER JOIN" to the UnitOfMeasure Conversion table and include "pt_mstr.pt_net_wt_um <> pt_drwg_size" as part of the join condition.

Omitting non relevant columns:

SELECT pt_mstr.pt_part as part
, round((pt_net_wt / (cast(pt_drwg_loc as numeric)
-- when um_mstr.um_conv is null, replace with 1
/ COALESCE(um_mstr.um_conv,1)
as packqty
FROM mfgprod..pub.pt_mstr pt_mstr
left join
mfgprod..pub.cd_det cd_det
on cd_det.cd_ref = pt_mstr.pt_part
and cd_det.cd_type = 'MK'
and cd_det.cd_lang = 'US'
and cd_det.cd_seq = 0)
LEFT OUTER JOIN
mfgprod..pub.um_mstr um_mstr
ON um_mstr.um_um = pt_mstr.pt_net_wt_um
and um_mstr.um_alt_um = pt_drwg_size
and um_mstr.um_part = ''
and pt_mstr.pt_net_wt_um <> pt_drwg_size

P.S.
I noticed that you have UDF in your SQL. I am in the process of writing an article on why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard.

Carl Federl

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-03 : 19:31:04

quote:
Originally posted by cfederl
I noticed that you have UDF in your SQL. I am in the process of writing an article on why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard.

LOL.. This should be funny..

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-03 : 22:01:41

quote:
Originally posted by cfederl
I noticed that you have UDF in your SQL. I am in the process of writing an article on why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard.

Carl Federl

Looking forward to this as well .. be sure to let us know when and where we can read it!

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-04 : 03:43:22

quote:
I noticed that you have UDF in your SQL. I am in the process of writing an article on why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard.

I, too, am interested in this. While I understand their downsides, I have several UDF's that are used in 30+ stored procedures. It seems that duplicating the code in the UDF into each of those SP's would cause much greater process and code management problems than the performance and tuning issues they introduce. But I'm open to being convinced!

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources