Need SQL Query Help

[Steve Thomas]

I need to write a similiar SQL query on a SQL server.

This query determines if the "Weekday" = Monday. If it does then the DayImport result = MONDAY if it equals something other than 2 it supplies a result of OTHER. This was written in Access97 and works great. I am trying to convert my application to SQL server and can't figure this one out.

SELECT tMaster.DT_Import, IIf(Weekday([dt_import])=2,"MONDAY","OTHER") AS DayImport
FROM tMaster;

Can it be done?

[svskumar]

Instead of Weekday try DateName(wk,date)
see Transact SQL Help

[JHausmann]

You won't be able to use IIF as part of a sql statement on SQL server. You can, though, create a stored procedure to to the same thing...

[Steve Thomas]

Can you give me a really simple example?

Hey you seem to be using SQL server. Can I ask you anopther question. I am using a Data Control to access the SQL server. However, when I try and click on the control to move it forward, backward, etc, it just freezes on me?

Thanks

[JHausmann]

Try this:

if exists (select * from sysobjects where id = object_id('dbo.cp_getWeekDay') and sysstat & 0xf = 4)
	drop procedure dbo.cp_getWeekDay
GO
CREATE PROCEDURE cp_getWeekDay
as


create table #tempWkDay (
       DT_Import smallint not null,
	DayImport char(6))

insert into #tempWkDay SELECT DT_Import, "Monday" FROM tMaster where dt_import=2
insert into #tempWkDay SELECT DT_Import, "Other" FROM tMaster where dt_import not = 2


SELECT * from #tempWkDay
GO

WRT your other question, I don't use controls against SQL server.

[Edited by JHausmann on 04-05-2000 at 02:25 PM]

[Steve Thomas]

Thanks for this code, I will look at it.

Why don't you use controls? I have a database that has about 25 fields associated with a customer such as name, addr, city, state, etc. It seems much easier for me to bind these to a data control rather than populate them.

I am curious how you get around this without a bunch of code. Or maybe you are writing different type of app.

Thanks

[JHausmann]

The main database I support has over 100 tables, some with hundreds of fields. The data gets moved from client apps to the servers (multiple locations) which then replicates to a central server. The central server passes data through to a single MVS DB2 database, which feeds a unix based system. The data transfer from the client to the servers is handled via a C++ based .dll. It compares the client database to the server database and, if the structures are compatible, it sends the data.

I have a handful of VB applications that access some of the data but none use a data control because I find them too restrictive. I may have to code more but my code does what I want when I want.