using if condition - 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

using if condition

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 09:16:29

hi first i will tell what i want to accomplish

I want to display certain records from a table UST_TASK_ALLOCATION_HISTORY which have these columns
TASK_ID ,
ASSOC_ID ,
TASK_ALLOC_DATE ,
TASK_ACTUAL_EFFORT ,
TASK_START_DATE ,
TASK_END_DATE ,
TASK_STATUS ,
TASK_DETAILS

based on the associate id from ust_associate_master table and taskcreateddate and status from task table

but the status has 4 values namely -pending,open,close,redone

based on anyone selection of these values the records must displayas am going to keep the status as drop down list box in front end

i have written like this but some where wrong can anyone help me in this

SELECT
TASK_ID ,
ASSOC_ID ,
TASK_ALLOC_DATE ,
TASK_ACTUAL_EFFORT ,
TASK_START_DATE ,
TASK_END_DATE ,
TASK_STATUS ,
TASK_DETAILS

FROM
UST_TASK_ALLOCATION_HISTORY
WHERE
ASSOC_ID='123'
AND TASK_CREATE_DATE='12345'
AND (IF STATUS=PENDING
OR STATUS=COMPLETED
OR STATUS=ABANDONED
OR STATUS=OPENED
OR STATUS=REASSIGNED)

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:18:45

[code]SELECT TASK_ID,
ASSOC_ID,
TASK_ALLOC_DATE,
TASK_ACTUAL_EFFORT,
TASK_START_DATE,
TASK_END_DATE,
TASK_STATUS,
TASK_DETAILS
FROM UST_TASK_ALLOCATION_HISTORY
WHERE ASSOC_ID = '123'
AND TASK_CREATE_DATE = '12345'
AND STATUS IN('PENDING', 'COMPLETED', 'ABANDONED', 'OPENED', 'REASSIGNED')[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 09:18:54

Create a parameter and use it

SELECT
TASK_ID ,
ASSOC_ID ,
TASK_ALLOC_DATE ,
TASK_ACTUAL_EFFORT ,
TASK_START_DATE ,
TASK_END_DATE ,
TASK_STATUS ,
TASK_DETAILS

FROM
UST_TASK_ALLOCATION_HISTORY
WHERE
ASSOC_ID='123'
AND TASK_CREATE_DATE='12345'
AND (STATUS=@STATUS)

Madhivanan

Failing to plan is Planning to fail

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 09:21:16

THANKU SO MUCH

susan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 09:23:44

quote:
Originally posted by susan_151615

THANKU SO MUCH

susan

Which one gives you the result you wanted?

Madhivanan

Failing to plan is Planning to fail

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 09:25:24

BUT PESO IN OPERATOR WILL DISPLAY ALL THE VALUES OF STATUS BUT I WANT A STATEMENT FOR EITHER IF THE USER CHOOSES THE STATUS AS PENDING FROM DROPDOWN VALUES (IN FRONT END) SHOULD BE DISPLAYED OR OPEN OR CLOSE EYTC..

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:28:17

Ah, now your explanation was much clearer to me.
I apologize.

E 12°55'05.63"
N 56°04'39.26"

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 09:32:54

SELECT ALLO.TASK_ID,
ALLO.TASK_ALLOC_DATE,
ALLO.TASK_ACTUAL_EFFORT,
ALLO.TASK_START_DATE,
ALLO.TASK_END_DATE,
ALLO.TASK_STATUS,
ALLO.TASK_DETAILS
FROM
UST_TASK_ALLOCATION_HISTORY ALLO,
UST TASK_HIST HIST
WHERE
ASSOC_ID = '123'

AND HIST.TASK_CREATE_DATE = '12345'

AND ALLO.STATUS IN('PENDING', 'COMPLETED', 'ABANDONED', 'OPENED', 'REASSIGNED')

AND ALLO.TASK_ID=HIST.TASK_ID
IS THIS CORRECT ITS THROWING ERROR ( UST TASK_HIST HIST)IN THIS LINE LIKE

INCORRECT SYNTAX NEAR HIST

susan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 09:33:18

quote:
Originally posted by susan_151615

BUT PESO IN OPERATOR WILL DISPLAY ALL THE VALUES OF STATUS BUT I WANT A STATEMENT FOR EITHER IF THE USER CHOOSES THE STATUS AS PENDING FROM DROPDOWN VALUES (IN FRONT END) SHOULD BE DISPLAYED OR OPEN OR CLOSE EYTC..

susan

Then refer my first reply

Madhivanan

Failing to plan is Planning to fail

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 09:35:21

ACTUALL Y AM FIRST TESTING THE QUERY IF THE QUERY WORKS I CAN PASS IN THE SP WITH PARAMETER AT THAT TIME UR CODE WILL BE HELPFULLNOW CAN U TELL WHAT WRONG IN THIS AND HELP ME SO THAT ICAN PASS ON TO SP

susan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 09:35:22

UST TASK_HIST HIST

should be

UST_TASK_HIST HIST

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:37:39

And use AS for aliasing names, and use ANSI style joins.

SELECT		ALLO.TASK_ID,
		ALLO.TASK_ALLOC_DATE,
		ALLO.TASK_ACTUAL_EFFORT,
		ALLO.TASK_START_DATE,
		ALLO.TASK_END_DATE,
		ALLO.TASK_STATUS,
		ALLO.TASK_DETAILS
FROM		UST_TASK_ALLOCATION_HISTORY AS ALLO
INNER JOIN	UST_TASK_HIST AS HIST ON HIST.TASK_ID = ALLO.TASK_ID
WHERE		ASSOC_ID = '123'
		AND HIST.TASK_CREATE_DATE = '12345'
		AND ALLO.STATUS = @Status

E 12°55'05.63"
N 56°04'39.26"

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 13:35:42

what is ansi type of joing can u explain me .Actuall am very sorry i didnt tell some dats i will clearly tell u about the datas which i really require can u sort out it for me

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 13:41:55

quote:
Originally posted by susan_151615

what is ansi type of joing can u explain me .Actuall am very sorry i didnt tell some dats i will clearly tell u about the datas which i really require can u sort out it for me

susan

ANSI type join is what Peso showed you in posted query ie using INNER JOIN , LEFT JOIN instead of =*,*=,...
It wont be supported in future versions of sql and hence should not be used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources