Select Statement Self Reference? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Select Statement Self Reference?

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2012-02-16 : 05:02:24

Hi All,

I have a situation here and i am quite stuck on what i can do. So here goes my situation.

I have one table called MyTable with fields:
1. type_code
2. reset_pay_ind
3. reset_rcv_ind
4. ref1
5. parentref

if type_code=11 Then
Do a select type_code from MyTable Where parentref=ref1

if type_code is 222 Then
Return reset_pay_ind as 'abc'
End Nested If

End if

Anyone knows how i should go about approaching this? I hope i do make sense.

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2012-02-16 : 05:13:43

So the output data can be like below. Hopefully, it makes more sense:

select type_code,ref1,parentref from MyTable Where type_code=11
type_code ref1 parentref
11 222 0

select type_code,ref1,parentref from MyTable Where parentref=222
type_code ref1 parentref
55 0 222

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-16 : 09:21:47

Make this a stored procedure, and just check the value of the parameter when it comes in. Then you can execute whichever select you want.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 09:50:36

[code]
DECLARE @type_code int, @parentref int

SET @parentref = 222

SELECT type_code,ref1,parentref
FROM MyTable
WHERE 1 =1
AND type_code = COALESCE(@type_code, type_code)
AND parentref = COALESCE(@parentref, parentref)
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2012-02-16 : 18:27:49

Thanks for your code.

But what does below do? I tried Googling Coalesc uses and what i have read that it is similar to isnull and it uses the next non-null value or somewhere along this line?

type_code = COALESCE(@type_code, type_code)

The code does not cater to help update reset_pay_ind cell value?

Should i consider using Cursor?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources