December 13, 2008 at 4:19 pm
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.
December 13, 2008 at 4:44 pm
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/
December 13, 2008 at 5:07 pm
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]
December 13, 2008 at 5:12 pm
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.
December 13, 2008 at 5:18 pm
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?
December 13, 2008 at 5:25 pm
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]
December 13, 2008 at 5:30 pm
Because I need to be able to retrieve data from one table or the other from inside a view that will be later used.
December 13, 2008 at 5:32 pm
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.
December 13, 2008 at 5:40 pm
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]
December 13, 2008 at 5:49 pm
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.
December 13, 2008 at 6:25 pm
washingtonen (12/13/2008)
This is the original SQLSELECT
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/
December 13, 2008 at 6:31 pm
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.
December 13, 2008 at 6:38 pm
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]
December 13, 2008 at 6:44 pm
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!
December 13, 2008 at 7:19 pm
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]
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply