SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case when question		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

musclebreast
Yak Posting Veteran

56 Posts
Posted - 08/23/2012 :  15:40:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3832 Posts
Posted - 08/23/2012 :  16:47:08  Show Profile  Reply with Quote
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)
Go to Top of Page

musclebreast
Yak Posting Veteran

56 Posts
Posted - 08/28/2012 :  08:50:46  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3832 Posts
Posted - 08/28/2012 :  12:28:39  Show Profile  Reply with Quote
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.
Go to Top of Page

musclebreast
Yak Posting Veteran

56 Posts
Posted - 08/30/2012 :  18:26:21  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/30/2012 :  21:27:40  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000