Conditional Selects with different row number

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 2000 Forums

Transact-SQL (2000)

Conditional Selects with different row number

Author

Topic

Skidster
Starting Member

6 Posts

Posted - 2005-01-18 : 13:42:37

Hey all,

I'm new to this forum, and I really hope you could help me.

I've encountered the following problem:

I need to perform a select, which will result in a different amount of rows, meaning, I have a table.. and I SELECT from it, and if I meet condition A (for example, it's monday, 1=1, or the selected value is larger than 10), then I need to select rows X,Y,Z .. otherwise I need to select rows X,Y.

I need it to be in a single query.

I thought of doing SELECT * from (if <xyz> select x,y,z else select x,y) or something of that sort.

could you help me with syntax?

thanks,

Irene

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-18 : 14:05:05

>> if <xyz> select x,y,z else select x,y

You already answered your own question.

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-18 : 14:05:48

OK, I'll put it down as a sniped

BUT: I believe they are trying to use only 1 SQL Statement. Not 2.

What you're trying to do is dynamic sql....what other rdbms's do you have a background in?

You should simply write 2 SQL Styatements


IF xyz
    SELECT x,y,z FROM mytable99 WHERE yada yada yada
  ELSE
    SELECT x,y FROM mytable99 WHERE yada yada yada

Brett

8-)

Skidster
Starting Member

6 Posts

Posted - 2005-01-18 : 14:20:44

I need to stick it with a union, so I can't start it with an "if".

I need to do "select X,Y,Z from abc union select <sophisticated query> and it doesn't work...

I need syntax help.

Thanks,

Irene

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-18 : 14:26:03

If you are using a union all parts of the union must
have the same no of columns and compatible datatypes anyway.
You need to rethink your approach to this problem.

rockmoose

Skidster
Starting Member

6 Posts

Posted - 2005-01-18 : 14:36:22

rockmoose - there's a reason I'm asking. I know unions must have the same number of columns, but I need this anyway, for a l-o-n-g story...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-18 : 14:44:52

>> rockmoose - there's a reason I'm asking

Please, enlighten me.
You want to do A, you can accomplish it with method a, b, c, d, e, f, g, h or x
But, No, that will not work, You have to use method Z, which is not possible.
Why ?

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-18 : 14:50:26

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff

Skidster
Starting Member

6 Posts

Posted - 2005-01-18 : 14:55:38

rockmoose - I'm sorry, my bad, I'm reading this and I've asked my question in the wrong way.

let me rephrase it..

take for example the following - I am doing SELECT name, hobby FROM employees
now, say I wanna do union select to it, and do the following -
SELECT name, hobby FROM employees UNION SELECT lunch.* from(employees.name,<if today is monday select employees.mondaylunch> elseif <today is any other day select employees.justlunch>) lunch;
so I need to select something one if today is monday, and something else if today is saturday (for example).

the amount of fields really doesn't matter for this syntax issue, shouldn't have brought it up.

If you're saying this cannot be done as a single query, I accept.
otherwise, I'd love anybody's help.

Thanks,
Irene

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-18 : 15:03:36

Is this helpful?

SELECT name, hobby FROM employees
UNION
SELECT name, mondaylunch FROM employees WHERE <today is monday>
UNION
SELECT name, justlunch FROM employees WHERE NOT <today is monday>

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-18 : 15:53:01

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff

Skidster
Starting Member

6 Posts

Posted - 2005-01-18 : 15:53:40

rockmoose - nope, sorry, it doesn't help. I need that particular syntax, if it can't work than it doesn't matter (Im researching something, so I need that specific thing)..

generally - can I put an IF inside a FROM?

Skidster
Starting Member

6 Posts

Posted - 2005-01-18 : 16:01:54

Jeff - thanks, but it's not what I need. I've worked with SQL before, I am researching on a topic related to nested queries and IF's.

I suppose the specific examples I've presented can be solved in more than one way, but I just need this specific one.

sorry guys, I know I sound stubborn, but I need this for a reason, it's sql-.net/stuff-algorithmic programming related..

thanks again,

Irene

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-18 : 16:21:20

>> generally - can I put an IF inside a FROM?

No.

>> I suppose the specific examples I've presented can be solved in more than one way, but I just need this specific one.

So if it is impossible, how will you do it

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources