SQL - Query with if/case statments

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

SQL - Query with if/case statments

Author

Topic

deco10
Starting Member

28 Posts

Posted - 2010-02-03 : 23:52:17

[code]
SELECT....... WHERE
[Manuf] != "XYZ" AND
[Manuf] != "ABC" AND
([Manuf] = "userinput" OR
[PartNo] = "userinput" OR
[Desc] = "userinput") AND
[PartNo] LIKE ____________
[/code]
and this is where I get stuck.

Basically if the Manuf is ABC, AAZ, AXX I need to check to make sure that the PartNo starts with ABC (or AAZ or AXX respectively).

(usually all part numbers start with the the manuf code, but in the case that they don't they shouldn't be returned.)

Suggestions?
Thanks!

rajpes
Starting Member

13 Posts

Posted - 2010-02-04 : 00:13:49

select * from table where charindex(Manuf,PartNo)>=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:27:38

[code]select * from table where ... AND PartNo LIKE Manuf + '%'[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 03:29:14

quote:
Originally posted by rajpes

select * from table where charindex(Manuf,PartNo)>=1

This will not make use of Index if available. Use Visakh's query

Madhivanan

Failing to plan is Planning to fail

deco10
Starting Member

28 Posts

Posted - 2010-02-04 : 11:42:55

Right, but I only want to do that test for a small portion of the manufacturer codes. The database could have been set up differently that would have made this easier, but thats long in the past and far beyond my control.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 11:44:36

quote:
Originally posted by deco10

Right, but I only want to do that test for a small portion of the manufacturer codes. The database could have been set up differently that would have made this easier, but thats long in the past and far beyond my control.

ok then identify only those codes and apply the condition over them

deco10
Starting Member

28 Posts

Posted - 2010-02-04 : 11:47:32

I'm not trying to be a pain, but I'm not sure how to do that.

IF statements?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 11:55:02

quote:
Originally posted by deco10

I'm not trying to be a pain, but I'm not sure how to do that.

IF statements?

no in where using AND conditions

deco10
Starting Member

28 Posts

Posted - 2010-02-04 : 12:44:39

I don't see how I can use AND conditions for that. If the record doesn't meet the criteria entered by the user then I don't want to check if
PartNo LIKE Manuf + '%'
If do that I'll end up filtering out records that I want. At least thats my understanding.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 12:47:12

what are the codes which you want to consider for applying above condition?

deco10
Starting Member

28 Posts

Posted - 2010-02-04 : 12:54:30

quote:
Originally posted by visakh16

what are the codes which you want to consider for applying above condition?

ABX, VBA for example

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 13:00:12

[code]SELECT....... WHERE
other conditions... AND
(PartNo LIKE [Manuf] + '%' OR [Manuf] NOT IN ('ABX','VBA'))[/code]

deco10
Starting Member

28 Posts

Posted - 2010-02-10 : 14:12:53

Lets forget about the other conditions for the moment as this is the way I am testing it.

I have a record with the following fields:
Manuf = ABX
PartNo = ABX12-345

So the query:
SELECT * ..... WHERE (PartNo LIKE [Manuf] + '%' OR [Manuf] NOT IN ('ABX','VBA'))

So this record should be returned by this query, correct? This record is not returned.

Any further suggestions?

deco10
Starting Member

28 Posts

Posted - 2010-02-10 : 14:22:55

Sorry, my fault. Blame the co-op student who entered the data. The ABX code has a space after it in our tables. Thank you so much for helping me with this, it made me realize I need to review my basic programming skills :)

Kindest regards.

EDIT: Actually, the Manuf field is a VARCHAR(4)
Lesson learned :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:23:26

quote:
Originally posted by deco10

Lets forget about the other conditions for the moment as this is the way I am testing it.

I have a record with the following fields:
Manuf = ABX
PartNo = ABX12-345

So the query:
SELECT * ..... WHERE (PartNo LIKE [Manuf] + '%' OR [Manuf] NOT IN ('ABX','VBA'))

So this record should be returned by this query, correct? This record is not returned.

Any further suggestions?

it should return unless your Manuf or PartNo have leading or trainiling spaces.
you could try below too

SELECT * ..... WHERE (LTRIM(PartNo) LIKE LTRIM(RTRIM([Manuf])) + '%' OR [Manuf] NOT IN ('ABX','VBA'))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deco10
Starting Member

28 Posts

Posted - 2010-02-10 : 14:26:57

Thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:35:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-10 : 15:30:56

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tanks for your good info

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 00:06:43

quote:
Originally posted by persiangulf098

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tanks for your good info

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources