March 24, 2008 at 3:00 pm
I'm writing an script that will create a table and run a stored procedure to populate the table with data.
It works fine, but the vendor who will eventually import my flat file says to me that he can't receive any column values = 'NULL'.
In the case of my database, it is unavoidable but I was wondering if anyone knew how to update my set statements with some
sort of "else if" clause for inserting a true "" (blank) value if the column contains a NULL value.
Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotel_export]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hotel_export]
GO
CREATE TABLE [dbo].[hotel_export] (
[room_code] [varchar] (15) NOT NULL ,
[guest_lname] [varchar] (50) NULL ,
[guest_fname] [varchar] (50) NULL ,
[freq_guest_num] [varchar] (30) NULL ,
[addr1] [varchar] (100) NULL ,
[addr2] [varchar] (100) NULL ,
[city] [varchar] (50) NULL ,
[state] [varchar] (40) NULL ,
[zip] [varchar] (20) NULL ,
[country] [varchar] (30) NULL ,
[company_name] [varchar] (100) NULL ,
[varchar] (200) NULL ,
[adults_num] [varchar] (20) NULL ,
[room_type] [varchar] (50) NULL ,
[crs] [varchar] (12) NULL ,
[phone] [varchar] (40) NULL ,
[share1_lname] [varchar] (50) NULL ,
[share1_fname] [varchar] (50) NULL ,
[share2_lname] [varchar] (50) NULL ,
[share2_fname] [varchar] (50) NULL ,
[share3_lname] [varchar] (50) NULL ,
[share3_fname] [varchar] (50) NULL ,
[m_message] [varchar] (20) NULL ,
[rand] [varchar] (8) NULL ,
[shareacct] [varchar] (10) NULL ,
[account] [varchar] (12) NULL ,
[id] [varchar] (30) NULL,
[date_from] [varchar] (20) NULL ,
[date_to] [varchar] (20) NULL ,
[cxl_acct] [varchar] (14) NULL ,
[special1] [varchar] (6) NULL ,
[status] [varchar] (1) NULL ,
[status_desc] [varchar] (8) NULL ,
[arr_time] [varchar] (8) NULL ,
[add_type] [varchar] (8) NULL ,
[group_id] [varchar] (8) NULL ,
[group_name] [varchar] (8) NULL ,
[child] [varchar] (2) NULL ,
[honor] [varchar] (8) NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotel_export_proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[hotel_export_proc]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE hotel_export_proc AS
DECLARE @outoforder varchar (12)
DECLARE @ooofrom varchar (20)
DECLARE @oooto varchar (20)
DECLARE @room_code varchar (6)
DECLARE @guest_name varchar (70)
DECLARE @guest_fname varchar (1)
DECLARE @account varchar(14)
DECLARE @date_from varchar (20)
DECLARE @date_to varchar (20)
DECLARE @freq_guest_num varchar (12)
DECLARE @addr1 varchar (40)
DECLARE @addr2 varchar (40)
DECLARE @city varchar (20)
DECLARE @state varchar (6)
DECLARE @zip varchar (10)
DECLARE @country varchar (6)
DECLARE @company_name varchar (70)
DECLARE @adults_num varchar (2)
DECLARE @phone varchar (15)
DECLARE @special1 varchar (6)
DECLARE @special2 varchar (6)
DECLARE @special3 varchar (6)
DECLARE @special4 varchar (6)
DECLARE @special5 varchar (6)
DECLARE @room_type varchar (6)
DECLARE @out_time varchar (20)
DECLARE @share1_lname varchar (50)
DECLARE @share1_fname varchar (50)
DECLARE @share2_lname varchar (50)
DECLARE @share2_fname varchar (50)
DECLARE @share3_lname varchar (50)
DECLARE @share3_fname varchar (50)
DECLARE @email varchar (40)
DECLARE @shareacct varchar (12)
DECLARE @checked_out varchar (20)
DECLARE @crs varchar (12)
DECLARE @m_message varchar (20)
DECLARE @rand varchar (8)
DECLARE @id varchar (30)
DECLARE @cxl_acct varchar (14)
DECLARE @status varchar (1)
DECLARE @status_desc varchar (8)
DECLARE @arr_time varchar (8)
DECLARE @add_type varchar (8)
DECLARE @group_id varchar (8)
DECLARE @group_name varchar (8)
DECLARE @child varchar (2)
DECLARE @honor varchar (8)
declare curse cursor
for select code from z_rooms where property='DELSOL'
open curse
fetch next from curse into @room_code
while (@@fetch_status<>-1)
begin
if (@@fetch_status<>-2)
begin
set @account=(select TOP 1 min(g.account) from guest g, z_property z where (g.room=@room_code and g.status='I') or (g.room=@room_code and g.status='O' and CONVERT (datetime,departure,3)+ CAST (out_time as datetime) >=Dateadd(mi,-5,GetDate())) group by account)
set @guest_name=isnull(isnull((select name from guest where (room=@room_code and account=@account and status='I')or (room=@room_code and account=@account and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) ),@outoforder) ,'VACANT')
set @guest_fname=isnull(isnull((select name from guest where (room=@room_code and account=@account and status='I')or (room=@room_code and account=@account and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) ),@outoforder) ,'VACANT')
set @date_from=isnull((select CONVERT (varchar,arrival,3) from guest where (account=@account and room=@room_code and status='I') or (account=@account and room=@room_code and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) group by arrival),@ooofrom)
set @date_to=isnull((select CONVERT (varchar,departure,3) from guest where (account=@account and room=@room_code and status='I') or (account=@account and room=@room_code and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) group by departure),@oooto)
set @freq_guest_num=( select ghacct from guest where account=@account and room=@room_code group by ghacct)
set @addr1=(select address from guest where account=@account and room=@room_code group by address)
set @addr2=(select address2 from guest where account=@account and room=@room_code group by address2)
set @city=(select city from guest where account=@account and room=@room_code group by city)
set @state=(select state from guest where account=@account and room=@room_code group by state)
set @zip=(select zip from guest where account=@account and room=@room_code group by zip)
set @country=(select country from guest where account=@account and room=@room_code group by country)
set @company_name=(select company from guest where account=@account and room=@room_code group by company)
set @adults_num=(select adult from guest where account=@account and room=@room_code group by adult)
set @phone=(select phone from guest where account=@account and room=@room_code group by phone)
set @room_type=(select roomtype from z_rooms where code=@room_code group by roomtype)
set @email=(select email from guest where account=@account and room=@room_code group by email)
set @share1_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account)),'')
set @share2_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_lname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_lname)),'')
set @share3_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_lname and g.name<>@share2_lname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_lname and n.name<>@share2_lname)),'')
set @share1_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account)),'')
set @share2_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_fname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_fname)),'')
set @share3_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_fname and g.name<>@share2_fname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_fname and n.name<>@share2_fname)),'')
set @special1=(select TOP 1 min(s.special) from guest_specials s,z_property z where s.account=@account and s.status='A' and z.hotel_date between s.from_date and s.to_date group by s.special)
set @shareacct=( select shareacct from guest where account=@account and room=@room_code group by shareacct)
set @crs=( select crs from guest where account=@account and room=@room_code group by crs)
set @m_message=(select m_message from guest where account=@account and room=@room_code group by m_message)
set @cxl_acct=( select cxl_acct from guest where account=@account and room=@room_code group by cxl_acct)
set @arr_time=( select arr_time from guest where account=@account and room=@room_code group by arr_time)
set @child=( select child from guest where account=@account and room=@room_code group by child)
INSERT INTO hotel_expert_room_registry (room_code,guest_lname,guest_fname,freq_guest_num,addr1,addr2,city,state,zip,country,company_name,email,adults_num,room_type,crs,phone,share1_lname,share1_fname,share2_lname,share2_fname,share3_lname,share3_fname,m_message,rand,shareacct,account,id,date_from,date_to,cxl_acct,special1,status,status_desc,arr_time,add_type,group_id,group_name,child,honor)
VALUES (@room_code, @guest_name,@guest_fname,@freq_guest_num,@addr1,@addr2,@city,@state,@zip,@country,@company_name,@email,@adults_num,@room_type,@crs,@phone,@share1_lname,@share1_fname,@share2_lname,@share2_fname,@share3_lname,@share3_fname,@m_message,@rand,@shareacct,@account,@id,@date_from,@date_to,@cxl_acct,@special1,@status,@status_desc,@arr_time,@add_type,@group_id,@group_name,@child,@honor)
end
fetch next from curse into @room_code
end
close curse
deallocate curse
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 24, 2008 at 3:08 pm
You're already doing it in some other cases, like the quest_name line ...
but anyway ...
set @phone=ISNULL((select phone from guest where account=@account and room=@room_code group by phone),'')
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2008 at 3:20 pm
thank you...you're totally right...i did not know that function did that and bc the data in the columns already using that function did not have any "blanks" (nulls) it kind of threw me.
March 24, 2008 at 4:04 pm
Jason Selburg (3/24/2008)
You're already doing it in some other cases, like the quest_name line ...but anyway ...
set @phone=ISNULL((select phone from guest where account=@account and room=@room_code group by phone),'')
could you verify how to form this particular set statement using the isnull function?
set @special1=isnull(isnull((select TOP 1 min(s.special) from guest_specials s,z_property z where s.account=@account and s.status='A' and z.hotel_date between s.from_date and s.to_date group by s.special)),'')
when i use this...i get the error: The isnull function requires 2 arguments.
thanks for your help
March 24, 2008 at 4:14 pm
ISNULL does require two arguments and you have 2 ISNULL functions in that statement (only the outer one has the second argument). I suspect that you do not need the second ISNULL.
[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]
March 24, 2008 at 4:53 pm
rbarryyoung (3/24/2008)
ISNULL does require two arguments and you have 2 ISNULL functions in that statement (only the outer one has the second argument). I suspect that you do not need the second ISNULL.
awesome yea...this is what i left it at...i didn't need the additional function...
set @special1=isnull((select TOP 1 min(s.special) from guest_specials s,z_property z where s.account=@account and s.status='A' and z.hotel_date between s.from_date and s.to_date group by s.special),'')
March 24, 2008 at 10:55 pm
Let's see if I counted this right...
1 Cursor
30 separate SELECTs (many nearly identical) not including the cursor
1 Insert
... per row...
I wonder how long this puppy is going to take to insert several thousand rows compared to a set based solution... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2008 at 11:03 am
Did anyone else notice what he called "CURSE"
:w00t:
March 26, 2008 at 12:13 pm
Heh, yeaup... thought I'd let that one go 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2008 at 12:19 pm
Actually - it's one of my favored names for those....:D
'cause that's usually what I end up doing when I find them.....:Whistling:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply