case when question - 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

case when question

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-08-23 : 15:40:36

Hello,

my problem is difficult to explain, but I'm going to try it:)

I've got a form with one field:

Unit

In that field a user can enter a unit.

In my following SQL the value of the field is used through the term %1

for example, if the value of field unit is test
then in my SQL %1 would be 'test'

and here is my SQL:



SELECT 

ID, 

MAX(Case WHEN AttrID = '2' AND DefID = '475909' THEN VALSTR END) as Unit, 
MAX(Case WHEN AttrID = '5' AND DefID = '475806' THEN VALSTR END) as Location 

 

FROM 

	dee A1 
	inner join 
		data A2 on A1.DataID = A2.ID 
		AND 	
		MAX ( Case When A2.AttrID = '2' AND A2.DefID = '475909' THEN A2.VALSTR END)  like ‘ % %1%’

GROUP BY ID

allright some words to the table data and why I use the function MAX.

The table data consists of 4 columns

ID....DefID....AttrID.....version...Valstr
123...475909....2.........1.........test
123...475909....2.........2.........test1
123...475909....2.........2.........test3
345...475806....5.........1.........test
345...475806....5.........2.........test

As you can see to every set of ID,DefID and AttrID exists many versions...I only need the value of the last version...That's why I use the Max function...

What my problem?

I enter the value test in my form....and now I want a list of all items for which the column valstr of the table date is equal the value of my field:

it's this part:

MAX ( Case When A2.AttrID = '2' AND A2.DefID = '475909' THEN A2.VALSTR END) like ‘ % %1%’

if the field is empty...the And condition should not be considered...is that possibne? my attempt doesn'T work...I think a group by is missing as well...i hope you can understand what I want..otherwise just ask:)

Thanks and kind regards,

Lara

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-23 : 16:47:08

I don't get what %1 is. Is that supposed to be a string format location?

As a WAG, I'd say you need to replace %1 with a variable. Here is a sample with data in a consumable format:

DECLARE @Foo TABLE (ID INT, DefID INT, AttrID INT, version INT, Valstr VARCHAR(100))
INSERT @Foo
VALUES
(123, 475909, 2, 1, 'test'),
(123, 475909, 2, 2, 'test1'),
(123, 475909, 2, 2, 'test3'),
(345, 475806, 5, 1, 'test'),
(345, 475806, 5, 2, 'test')

DECLARE @Bar VARCHAR(100)
SET @Bar = 'test'

SELECT 
    MAX(VALSTR)
FROM 
    @Foo
WHERE 
    AttrID = 2  
    AND DefID = 475909 
    AND (Valstr like '%' + @Bar + '%' OR @Bar IS NULL)

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-08-28 : 08:50:46

Hi,

thanks for your help...the where clause is correct...thanks..but I'vegot still problem that my vaiable is in ''

look at my query:

WHERE VALSTR like '%'+'RxGMP'+'%'

the value of my variable is RxGMP

the problem...the value is in '' and I can't change it.

so in the end I get an error because the synthax is wrong...

In addition to that..If I want to seach for RxG it's a problem because the whole value would be in 'RxG' ..

is it possible to cut the '' in a like query or do you have another hint for me?

kind regards,

Lara

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-28 : 12:28:39

quote:
thanks for your help...the where clause is correct...thanks..but I'vegot still problem that my vaiable is in ''

look at my query:

WHERE VALSTR like '%'+'RxGMP'+'%

You are talking about variables and values? Also, what can't you change? Why is the "variable" in single-quotes? Is the real issue that you are using dynamic SQL?

Sorry, I'm confused by your description of the issue and I don't even know what to ask you to help clarify. Perhaps you can refine your question a bit or try to tell us what you are really doing, assuming reality is different from your orignal question.

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-08-30 : 18:26:21

HI,:)

I know it's not easy to describe..bit as I wrote in my initial posting..I've got a web form with one field...behin the form is a report, which is a SQL Statement...so far so good...The value of the field on the form is a dynamic part in the SQL...the term %1 is used to get the value of the field.

example...in my field of the form:

value: test

my where condition of the query:




WHERE VALSTR like '%'+%1+'%'

translated the databse gets this request from my report:



WHERE VALSTR like '%'+'test'+'%'

as you can see the value is always in ''

now it's not easy for me..lets assume a simple SQL




Select * from Table

WHERE name like  '%'+'test'+'%'

or maybe I want just search for all names which start with tes%

the thing is that the value of my filed is always in ''...how can I work with that...is it possible to get rid of the '' before I use in in my where clause?

I hope it's better explained....thanks for your patience...

Kind regards,

Lara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 21:27:40

sounds like you need to use replace to replace 's on your value before sending it to where

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources