Not Found Situation - 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

Not Found Situation

Author

Topic

lady_2000
Starting Member

4 Posts

Posted - 2008-12-16 : 14:43:59

I have a query where I would like to used the %not_found of ORACLE pl/sql in transact sql in sql query analizer.

How can I do this?

Need help urgent

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 14:45:24

Could you explain what %not_found does for those of us who have never touched Oracle before?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lady_2000
Starting Member

4 Posts

Posted - 2008-12-16 : 14:47:57

It means that if you are selecting a field where their is no data it displays an 'N/A' for instance.

What i need to do is when the data in a specific field is not found, return a 'N/A' like the isnull function.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 14:49:14

Then why can't you use the ISNULL (or COALESCE) function? Could you show us a data example to make this more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lady_2000
Starting Member

4 Posts

Posted - 2008-12-16 : 14:59:42

Can not use the null function, because the field does not exist..

table a
table b

select data1, data2
from table a,
table b
whee a.data3 = b.data3
and a.id = 2 and b.id = 2

table a
id data1 data2 data 3
1 one 4948 345
2 two 9877
3 three 4937 323

table b
id data1 data2 data 3
1 one 345
3 three 323

The id = 2 does not exist in table b.

I need to by pass this, in a way to tell the code
to by pass this select when the situation is not found.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 15:31:23

I still don't understand what you mean. Using your above data, what result set would you like to see returned?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lady_2000
Starting Member

4 Posts

Posted - 2008-12-17 : 09:47:19

the return record with that specific field in blank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 11:03:14

seems what you need is left join and use coalesce as suggested by Tara

select a.data1, COALESCE(CAST(b.data2 AS Varchar(10)),'N/A')
from table a
left join table b
on a.data3 = b.data3
and a.id = 2 and b.id = 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources