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

Transact-SQL (2000)

Dynamic select

Author

Topic

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 05:37:58

Hi,

I want to make a statement with a if like function.

The table is like
id column1 column2
int Nvarchar Nvarchar

I need a statement like this
If column1 is null then select column2 as column1

How can i do this?
Maybe its posts already but the search engine times out.

Thanks
Ferry

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-05 : 05:41:02

this helps?

select isnull(column1, column2) as col1
from MyTable

Go with the flow & have fun! Else fight the flow

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 05:45:24

Then I have an other problem the column2 has to be set to 0 then.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-05 : 06:04:07

select coalesce(column1, column2, 0) as col1
from MyTable

Go with the flow & have fun! Else fight the flow

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 06:19:44

Sorry i meant something like
If isnull(column1, column2) as column1, if column1 is null then 0 else column2 as column2

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-05 : 06:23:31

what???

isnull(column1, column2) means that if column1 is null column2 will be returned.
coalesce(column1, column2, 0) will return first non null paramter.
so if both column1 and column2 are null 0 will be returned.

Go with the flow & have fun! Else fight the flow

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 07:20:06

I will explain the problem I have, maybe you know what i mean then:

I am making a query on the registration program for time and billing. The problem is that there is a column for hours and a column for minutes. If the hours and minutes field als inserted everything goed fine but when a user only fills in the hours those hours are insert in the minutes field. (I don't have the source of the program so i can't change that) So i need to check if the hours column is null, if its null the minutes column has to become the hours and the minutes have to be set to 0.

So i need a query that checks if the hours are null, if the are not null the hours column needs to be unchanged. If the hours are NULL the minutes column need to become the hours column and the minutes column needs to be set to 0.

I can't change the time and billing program so i need to make a view that solves this.

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-05 : 07:40:56

will this do?

update table1
set hours = minutes,
minutes = 0
where hours is null

Go with the flow & have fun! Else fight the flow

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 07:47:07

It needs to be on the fly in a select statement because when hours and minute are filled in, the hours are in the hours column and minutes in the minutes column. So only if the hours column is NULL the minutes need to be in the hours column and the minutes column set to NULL or 0.

It stucks it programmed bad. But i need to deal with it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-05 : 08:32:35

how bout:

select isnull(hours, minutes) as hours, case when hours is not null then minutes else 0 end as minutes
from table

Go with the flow & have fun! Else fight the flow

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 08:54:00

YAY! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources