July 26, 2012 at 7:15 pm
COULD YOU PLEASE HELP ME? PLEASE NO DYNAMIC SQL
CREATE TABLE STATEOFUSA
(
STATEUS CHAR(2)
)
INSERT INTO STATEOFUSA VALUES ('TX'),('CA'),('NY'),('FL'),('NM'),('AZ'),('AR')
IF I SET @VALUE = 101, IT SHOULD DISPLAY ALL STATE WHICH TOUCH MAXICO COUNTRY MEAN -> WHERE STATEUS IN ('TX','CA','NM','AZ')
OUTPUT SHOULD BE 'TX','CA','NM','AZ'
IF I SET @VALUE = 102, IT SHOULD DISPLAY ALL STATE WHICH DO NOT TOUCH MAXICO COUNTRY MEAN -> WHERE STATEUS NOT IN ('TX','CA','NM','AZ')
OUTPUT SHOULD BE 'NY','FL','AR'
IF I SET @VALUE = 103, IT SHOULD DISPLAY ALL STATE (NO NEED WHERE CONDITION)
OUTPUT SHOULD BE 'TX','CA','NY','FL','NM','AZ','AR'
DECLARE @VALUE INT
SET @VALUE = 101 --
SELECT STATEUS from STATEOFUSA WHERE STATEUS in ..........
July 26, 2012 at 7:21 pm
how to identify which states touch wich values?
July 26, 2012 at 7:48 pm
I would add a column isAdjacent bit and instead of IS IN I would set the condition
WHERE isAdjacent = 'true' or 'false' or omit the test.
July 26, 2012 at 9:07 pm
do you have to use case ? In these query conditions you can use temp variables to do the comparisons..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
July 26, 2012 at 9:45 pm
yes I do have to use case
July 26, 2012 at 10:55 pm
SELECT
STATEUS
from
STATEOFUSA
WHERE
case
when @VALUE = 103
then 1
when @VALUE in 101 and STATEUS in ('TX','CA','NM','AZ')
then 1
when @VALUE in 102 and STATEUS not in ('TX','CA','NM','AZ')
then 1
else 0 end = 1
July 27, 2012 at 6:58 am
Michael, I did try following way but still showing error before running. this is what I was looking for
DECLARE @VALUE INT
SET @VALUE = 101
SELECT
STATEUS
from
STATEOFUSA
WHERE STATEUS =
(case
when @VALUE = 103
then 1
when @VALUE in 101 and STATEUS in ('TX','CA','NM','AZ')
then 1
when @VALUE in 102 and STATEUS not in ('TX','CA','NM','AZ')
then 1
else 0 end = 1)
July 27, 2012 at 7:20 am
based on the code above:
DECLARE @VALUE INT
SET @VALUE = 101
SELECT
*
from
Apprentices
WHERE
1 = case
when @VALUE = 103
then 1
when @VALUE = 101 and id in ('TX','CA','NM','AZ')
then 1
when @VALUE = 102 and id not in ('TX','CA','NM','AZ')
then 1
else 0 end
July 27, 2012 at 7:36 am
July 27, 2012 at 7:57 am
No it is not hw
July 27, 2012 at 7:57 am
Thank you it works now
July 27, 2012 at 7:58 am
Thanks michale it works now.
July 27, 2012 at 3:23 pm
Munabhai (7/27/2012)
Michael, I did try following way but still showing error before running. this is what I was looking forDECLARE @VALUE INT
SET @VALUE = 101
SELECT
STATEUS
from
STATEOFUSA
WHERE STATEUS =
(case
when @VALUE = 103
then 1
when @VALUE in 101 and STATEUS in ('TX','CA','NM','AZ')
then 1
when @VALUE in 102 and STATEUS not in ('TX','CA','NM','AZ')
then 1
else 0 end = 1)
I really hate it when I post something that works, they change is to something else (like the obvious error above), and then complain they got an error.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply