SQL Server, Creating a View

Question

Post reply

SQL Server, Creating a View

washingtonen

Old Hand

Points: 380
More actions
December 13, 2008 at 4:19 pm

#197181

Hi, I need your help!
I need to create a SQL Server 2000 view with this format:
SELECT
case when failed = 'N' then
(select * from aaa)
else
(select * from bbb)
end
FROM
dimstatus
The command succeed but when I try to execute I get this message:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I also tried to create it with an IF statement, but I figured out that the IF is not allowed, can you please help me?
Thanks SO much in advance,
Joe.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 1

Depending on exactly what you are trying to do you may want to look at one of two approaches:

You may wish to consider a table valued function instead of a view.

If you want to stay with a view, you can always make it with two select statements, one for table a and one for table b, with appropriate where clauses and then merge them using a UNION ALL.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Note that you can only get this to work with a View (or a table-valued function) if table A and table B have the same number of columns and the same data type for those columns in the same order.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

washingtonen Old Hand Points: 380 More actions · Answer 3

Why to I get an error when I execute the view mentioned?

Im missing something, it says about an Exist but I can figure out how to combine it, sorry, silly question, bust just starting.

Thanks again.

washingtonen Old Hand Points: 380 More actions · Answer 4

It has to be one table or the other and unfortnately I need it to create a view that I wll later use.

both tables are the same, same number of records but a Union is not an option.

Based on the failure message, that I need an Exist,where do I have to place it on the sql i posted before?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

And why, pray tell, is UNION not an option?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

washingtonen Old Hand Points: 380 More actions · Answer 6

Because I need to be able to retrieve data from one table or the other from inside a view that will be later used.

washingtonen Old Hand Points: 380 More actions · Answer 7

This is the original SQL

SELECT

case when failed = 'N' then

(select * from dim_concept)

else

(select * from vw_dim_concept)

end

FROM

flagsta

and those the errors when i execute it

Server: Msg 116, Level 16, State 1, Line 1

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Server: Msg 116, Level 16, State 1, Line 1

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

am I missing something to generate the error message?

Sorry again it this is to silly, but as I need some help, that's why I am posting the problem.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Like this:

Select * from dim_concept Where failed = 'N'

UNION ALL

Select * from vw_dim_concept Where Not (failed = 'N')

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

washingtonen Old Hand Points: 380 More actions · Answer 9

The failed field is on the flagsta table,

SELECT

case when failed = 'N' then

(select * from dim_concept)

else

(select * from vw_dim_concept)

end

FROM

flagsta

neither dim_concept or vw_dim_concept have the failed field.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 10

washingtonen (12/13/2008)
This is the original SQL
SELECT
case when failed = 'N' then
(select * from dim_concept)
else
(select * from vw_dim_concept)
end
FROM
flagsta
and those the errors when i execute it
Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
am I missing something to generate the error message?
Sorry again it this is to silly, but as I need some help, that's why I am posting the problem.

Using an exists will not get you the results you want.

What you could do is something like:

select * from dim_concept

where exists ( select * from flagsta where failed = 'N' )

UNION ALL

select * from vw_dim_concept

where not exists ( select * from flagsta where failed = 'N' )

and then place that into a view.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

washingtonen Old Hand Points: 380 More actions · Answer 11

This is what I need:

if(select failed from dimstatus) = 'N'

(select * from dim_region_mgr)

else

(select * from vw_dim_region_mgr)

I need to be able to choose one table or the other, not a union.

The reason, the table executes in 1 second while the view takes 20 minutes.

1) Can I use an IF condition in SQL 2000?

2) If not, what will be the CASE condition.

I need to pull data from only one table, a UNION will be a combination that I dont want and I can not do, it has to be one or the other.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

How does the Flagsta table relate to the dim_concept table?

In other words how do you determine which row in dim_concept or vw_dim_concept to dispaly if a row in Flagsta based on its "falied" value?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

washingtonen Old Hand Points: 380 More actions · Answer 13

There is no relation at all, I just want to select all from one table or the other upon condition on the dimstatus table (aka flagsta).

The below sql, does what I need, but I dont know how to create a view with an if, I dont know if its possible and I assume that a case will do it, but, it is not working as expected.

The dimstatus table is just a single record table used as a flag.

It may have a Y or a N. That field is update with a DTS package, Y upon failure, N upon succeed.

If one or the other condition, will determine, If my view will pull data from a view or from a fact table.

if(select failed from dimstatus) = 'N'

(select * from dim_region_mgr)

else

(select * from vw_dim_region_mgr)

THANKS ALL for your help!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Ah, well in that case then timothy has the right solution. If that performs slowly, then that is a different problem.

You cannot use an IF statement in a view. A view can only consist of a Select statement (UNION is an operator, so UNION [ALL] SELECT.. does not count against this). If you feel that you really need the IF, then you will have to make a table-valued function instead.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SQL Server, Creating a View

Cookies on SQLServerCentral