Case statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Case statement

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-16 : 05:42:47

Hi All,

I have 1 SP which has dynamic SQL. Here I have to write 1 qry where i need to check the specific situation. Suppose I have two tables Tab1 and Tab2. Initially I need to retrive records from Tab2, If I get Null value from this qry I have to get it from Tab1.

Can anybody tell me, how to overcome such situation. Or do u need soome more info? If yes, let me know.

Basically, my qry is

Select Tab3.F_1, IsNull((Select Tab2.F_2 From Tab2 Where ....), 0) From
Tab3 Where ...

This is the situation. Here i have to get the another value from another table instead of checking Null.

Hopes i have cleared all the things,

Thanks in advance,

Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-16 : 07:30:21

what is the link between, TAB3, TAB2 and TAB1 Table??

Chirag

http://chirikworld.blogspot.com/

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-16 : 07:39:53

Hi Chirag,

There is no any link between Tab3 and Tab2 or Tab3 and Tab1. but Tab1 and Tab2 are related through Parent Table. Tab2 is history table whose parent table is TAB4 and also for Tab1, Tab4 is parent table,

Tab4's PK is in Tab1 and Tab2

thx,
Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-16 : 07:42:34

then what you are asking, will give you CROSS JOIN, is this what you want??

Chirag

http://chirikworld.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 07:49:58

Where did Tab4 comes from ? It is not in your query at all.

I am not sure what do you want, but sounds to me you need a LEFT JOIN.

Stop us guessing what you want. Post your table DDL, sample data and the result that you want

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-16 : 11:16:35

let me make more easy.

i hv qry like ...

Select Field_1, IsNull((Select Field_xyz From xyz where <where criteria>), 0) As Field_2

this is my first select statement. now what i want to do is, i need to pick up value from another table, if i got NULL from this inline view. so i wants to implement like

if isnull(<select statement>)
select field_x from tab1
else
select field_y from tab2.

i hv dynamic SQL in my SP, so here, in this case i need to retrive one value out of 1 table, say (tab1 or tab2).

Apart from this, can we write case statement in this case, i mean can we write select statements in case statements?

thanks in advance,

Mahesh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-16 : 11:50:16

Are you trying to conditionally join to one table or another? Don't use CASE.

See:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-17 : 02:04:59

Thank You Jeff, Its very usefull blog. Here it will retrive the non null value. But What, if there r values in both the tables. Then how to retrive the latest value out of them? I have date field in both the tables.

Thanks again,

Mahesh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-17 : 08:11:09

You need to give us your table structure and some sample data to demonstrate clearly what you are trying to do.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources