error-An expression of non-boolean type specified in a context where a condition is expected

Question

Post reply

error-An expression of non-boolean type specified in a context where a condition is expected

Anamika

SSCertifiable

Points: 6202
More actions
December 15, 2008 at 2:57 am

#77158

Hi,
i'm getting error
'An expression of non-boolean type specified in a context where a condition is expected'
when i use the following query
where locationkey in (case when @StoreAttribute ='%' then (select locationkey from dbo.ClientTbl_DimStores )
else ( select locationkey from dbo.ClientTbl_DimStores
where (case when @StoreAttribute ='Country' then [Country]
when @StoreAttribute ='State' then [State]
when @StoreAttribute ='City' then [CityName]
when @StoreAttribute ='Region' then [Region]
when @StoreAttribute ='Store Name' then [StoreName]
end))
end)
= @StoreAttributeValue
)
i couldn't find out whtz the error!
early reply s highly appreciated!
Thanks,
Regards
Viji

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

There are too many errors in the code to list them all, this should work:

[font="Courier New"]WHERE locationkey IN (SELECT locationkey

FROM dbo.ClientTbl_DimStores

WHERE (CASE @StoreAttribute

WHEN 'Country' THEN [Country]

WHEN 'State' THEN [State]

WHEN 'City' THEN [CityName]

WHEN 'Region' THEN [Region]

WHEN 'Store Name' THEN [StoreName]

WHEN '%' THEN @StoreAttributeValue

END) = @StoreAttributeValue)

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Anamika SSCertifiable Points: 6202 More actions · Answer 2

sorry, let me explain the scene fully.

it is something like if it is '%' then select all the locationkey from stores.

if it is 'country' then select the attributevalue of that country say 'australia' is the attribute value.

then locationkeys belonging to that particular country will be taken.

sameway if it is state then select the attributevalue of that state say 'queensland' is the attribute value.

then locationkeys belonging to that particular country will be taken.

thanks,

regards

viji

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Did you test the code I posted, Viji?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Anamika SSCertifiable Points: 6202 More actions · Answer 4

sorry i didn't because, for us '%' means 'all' and if storeattribute is '%' the storeattributevalue will also be '%' and there is no column in the database to deal with this.

I have to select all the locationkeys.

thanks,

regards,

viji

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

viji (12/15/2008)
sorry i didn't because, for us '%' means 'all' and if storeattribute is '%' the storeattributevalue will also be '%' and there is no column in the database to deal with this.
I have to select all the locationkeys.
thanks,
regards,
viji

It's okay Viji, the condition "if storeattribute is '%'" is accounted for in my code...it's the last condition. It evaluates to WHERE @StoreAttributeValue = @StoreAttributeValue. It selects all of the locationkeys.

Go on...I know you want to...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Anamika SSCertifiable Points: 6202 More actions · Answer 6

amazing chris it works.

but i couldn't understand how it works. could you please explain me how it works.!!!

thanks

regards

viji

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

If you remove the unwanted conditions from the expression, then it's easier to see how it works...

WHERE locationkey IN (

SELECT locationkey

FROM dbo.ClientTbl_DimStores

WHERE (CASE @StoreAttribute WHEN '%' THEN @StoreAttributeValue END) = @StoreAttributeValue)

Although the statement works, I think it's unlikely to give good performance.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Anamika SSCertifiable Points: 6202 More actions · Answer 8

Thanks chris. nice explanation.

Could you please suggest some thing to get best performance on this??

thanks

regards

viji

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

viji (12/15/2008)
Thanks chris. nice explanation.
Could you please suggest some thing to get best performance on this??
thanks
regards
viji

It's impossible to tell without knowing more about your tables, viji. Here's an alternative method which may be more performant...

DECLARE @Country VARCHAR(20), @State VARCHAR(20), @City VARCHAR(20), @Region VARCHAR(20), @Store_Name varchar(20)

SET @Country = CASE @StoreAttribute WHEN 'Country' THEN @StoreAttributeValue ELSE NULL END

SET @State = CASE @StoreAttribute WHEN 'State' THEN @StoreAttributeValue ELSE NULL END

.

WHERE locationkey IN (SELECT locationkey

FROM dbo.ClientTbl_DimStores

WHERE (@Country IS NULL OR [Country] = @Country)

WHERE (@State IS NULL OR [State] = @State)

.

)

then there's always good ol' dynamic sql. Try this method and the one posted earlier first.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Anamika SSCertifiable Points: 6202 More actions · Answer 10

Anamika

SSCertifiable

Points: 6202

December 15, 2008 at 7:37 pm

#912895

Thanks Chris. i'll try both

Take care

Regards

viji