Cant Compile this SP - Help
-
I cannot figure out why I can't update this SP. I am building a query dynamically by concatenating several lines based on whether or not I have a zero in total, because otherwise I get a divide by zero error.
Line 28 is the line that says set @query = " "
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--/****** Object: Stored Procedure sp_Segmentation_Stats ******/
--/****** This is used watch DS4 Segementation Statistics ****/
ALTER PROCEDURE sp_Segmentation_Stats AS
declare @query varchar(4000)
/* NIBS cust survey segmentation percentages */
declare @total decimal(9,2) , @hits decimal(9,2)
set @total = (select count(*) from nibs_customer_survey with (nolock))
set @hits = (select count(*) from nibs_customer_survey with (nolock) where minor_segment_code is not null)
/* NIBS Workorders segmentation percentages */
declare @total5 decimal(9,2) , @hits5 decimal(9,2)
set @total5 = (select count(*) from NIBS_WorkOrders with (nolock))
set @hits5 = (select count(*) from NIBS_WorkOrders with (nolock) where minor_segment_code is not null)
/* ECID hit percentages */
declare @total2 decimal(9,2) , @hits2 decimal(9,2)
set @total2 = (select count(*) from ECID_Customer_Identifier with (nolock))
set @hits2 = (select count(*) from ECID_Customer_Identifier with (nolock) where ECID <> '')
/************** IMS ********************/
/* ECID IMS hit percentages */
declare @total3 decimal(9,2) , @hits3 decimal(9,2)
set @total3 = (select count(*) from IMS_ISSUES with (nolock))
set @hits3 = (select count(*) from IMS_ISSUES with (nolock) where ECID <> '')
/* IMS issues segmentation percentages */
declare @total4 decimal(9,2) , @hits4 decimal(9,2)
set @total4 = (select count(*) from IMS_ISSUES with (nolock) )
set @hits4 = (select count(*) from IMS_ISSUES with (nolock) where minor_segment_code is not null)
begin
set @query = " "
begin
if @total4 <> 0
set @query = " select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent "
else
set @query = " select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total3 <> 0
set @query = @query + " select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent "
else
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total2 <> 0
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent "
else
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total <> 0
set @query = @query + " select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, (@hits / @total) as Hit_Percent "
else
set @query = @query + " select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total5 <> 0
set @query = @query + " select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, (@hits5 / @total5) as Hit_Percent "
else
set @query = @query + " select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, Hit_Percent = 0 "
end
set @query = @query + " order by hit_percent "
end
EXEC @QUERY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
=================================
Errors are;
=================================
Server: Msg 207, Level 16, State 3, Procedure sp_Segmentation_Stats, Line 28
Invalid column name ' '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 31
Invalid column name ' select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 33
Invalid column name ' select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 36
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 38
Invalid column name ' select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 40
Invalid column name ' select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 43
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 45
Invalid column name ' select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 47
Invalid column name ' select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 50
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 52
Invalid column name ' select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, (@hits / @total) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 54
Invalid column name ' select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 57
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 59
Invalid column name ' select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, (@hits5 / @total5) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 61
Invalid column name ' select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 3, Procedure sp_Segmentation_Stats, Line 63
Invalid column name ' order by hit_percent '.
-
I thought I figured it out! As of matter of habit I create SP with the following lines in the begining
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
When I commented out the
SET QUOTED_IDENTIFIER ON
my SP compiled correctly
but now I get
Server: Msg 203, Level 16, State 2, Procedure sp_Segmentation_Stats, Line 65
The name ' select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent union select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent union select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent union select Source=...
-
Try using single quotes for your strings.
Mike Gercevich
-
if only it was so easy.
As you can see I have single quotes for the fake column I am creating for the SOURCE field. I can execute the single statements
select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent
select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent
select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent
select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, (@hits / @total) as Hit_Percent
select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, (@hits5 / @total5) as Hit_Percent
without any problems, however I need to be able to use the double to quotes to create the string.
-
ghughes,
If you need to put a single quote in your string, simply use two single quotes in a row. For example (and those are not double quotes, those are two single quotes in a row):
Declare @String VarChar(100)
Set @String = 'Horace ''K'' Greely'
Print @String
Result:
Horace 'K' Greely
-
Ok, I changed it so I dont need the double quotes, made every single quote, however it is giving me this error now. It looks like the string is being used as the name of the column which does not make much sense. Also if I change the query so it have only 1 if statement (no unions) it works fine. I am just try to get 5 rows of data.
Server: Msg 203, Level 16, State 2, Line 61
The name ' select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent union select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent union select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent union select Source=...
This is the query now
declare @query varchar(4000)
/* NIBS cust survey segmentation percentages */
declare @total decimal(9,2) , @hits decimal(9,2)
set @total = (select count(*) from nibs_customer_survey with (nolock))
set @hits = (select count(*) from nibs_customer_survey with (nolock) where minor_segment_code is not null)
/* NIBS Workorders segmentation percentages */
declare @total5 decimal(9,2) , @hits5 decimal(9,2)
set @total5 = (select count(*) from NIBS_WorkOrders with (nolock))
set @hits5 = (select count(*) from NIBS_WorkOrders with (nolock) where minor_segment_code is not null)
/* ECID hit percentages */
declare @total2 decimal(9,2) , @hits2 decimal(9,2)
set @total2 = (select count(*) from ECID_Customer_Identifier with (nolock))
set @hits2 = (select count(*) from ECID_Customer_Identifier with (nolock) where ECID <> '')
/************** IMS ********************/
/* ECID IMS hit percentages */
declare @total3 decimal(9,2) , @hits3 decimal(9,2)
set @total3 = (select count(*) from IMS_ISSUES with (nolock))
set @hits3 = (select count(*) from IMS_ISSUES with (nolock) where ECID <> '')
/* IMS issues segmentation percentages */
declare @total4 decimal(9,2) , @hits4 decimal(9,2)
set @total4 = (select count(*) from IMS_ISSUES with (nolock) )
set @hits4 = (select count(*) from IMS_ISSUES with (nolock) where minor_segment_code is not null)
begin
set @query = ' '
begin
if @total4 <> 0
set @query = " select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent "
else
set @query = " select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total3 <> 0
set @query = @query + " select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent "
else
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total2 <> 0
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent "
else
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total <> 0
set @query = @query + " select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, (@hits / @total) as Hit_Percent "
else
set @query = @query + " select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total5 <> 0
set @query = @query + " select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, (@hits5 / @total5) as Hit_Percent "
else
set @query = @query + " select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, Hit_Percent = 0 "
end
set @query = @query + " order by hit_percent "
end
EXEC @QUERY
-
ghughes,
set @query = " select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent "
should be:
set @query = ' select Source=''IMS Issues Segmentation Percentages'',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent '
And, remember, those are two single quotes in a row, not double quotes.
-
just a thought...
declare @myPercent numeric(9,2)
declare @myNum int
set @myNum = 1
declare @myDenom int
set @myDenom = 0
set @myPercent = isnull((@myNum/nullif(@myDenom,0)),0)
select @myPercent
[font="Courier New"]ZenDada[/font]
-
Great thought! It cleaned up my code in a massive way, and made this problem go AWAY! And I learned (again) that there is always a better way! Now on to new things.
Thank you for your help.
This is what I ended up with;
--/****** Object: Stored Procedure sp_Segmentation_Stats ******/
--/****** This is used watch DS4 Segementation Statistics ****/
ALTER PROCEDURE sp_Segmentation_Stats AS
declare @query varchar(4000)
/* NIBS cust survey segmentation percentages */
declare @total decimal(9,2) , @hits decimal(9,2)
set @total = (select count(*) from nibs_customer_survey with (nolock))
set @hits = (select count(*) from nibs_customer_survey with (nolock) where minor_segment_code is not null)
/* NIBS Workorders segmentation percentages */
declare @total5 decimal(9,2) , @hits5 decimal(9,2)
set @total5 = (select count(*) from NIBS_WorkOrders with (nolock))
set @hits5 = (select count(*) from NIBS_WorkOrders with (nolock) where minor_segment_code is not null)
/* ECID hit percentages */
declare @total2 decimal(9,2) , @hits2 decimal(9,2)
set @total2 = (select count(*) from ECID_Customer_Identifier with (nolock))
set @hits2 = (select count(*) from ECID_Customer_Identifier with (nolock) where ECID <> '')
/************** IMS ********************/
/* ECID IMS hit percentages */
declare @total3 decimal(9,2) , @hits3 decimal(9,2)
set @total3 = (select count(*) from IMS_ISSUES with (nolock))
set @hits3 = (select count(*) from IMS_ISSUES with (nolock) where ECID <> '')
/* IMS issues segmentation percentages */
declare @total4 decimal(9,2) , @hits4 decimal(9,2)
set @total4 = (select count(*) from IMS_ISSUES with (nolock) )
set @hits4 = (select count(*) from IMS_ISSUES with (nolock) where minor_segment_code is not null)
select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, Hit_Percent = isnull((@hits4/nullif(@total4,0)),0)
union
select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, Hit_Percent = isnull((@hits3/nullif(@total3,0)),0)
union
select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = isnull((@hits2/nullif(@total2,0)),0)
union
select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, Hit_Percent = isnull((@hits/nullif(@total,0)),0)
union
select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, Hit_Percent = isnull((@hits5/nullif(@total5,0)),0)
order by hit_percent
-
HTH! That looks much prettier.
[font="Courier New"]ZenDada[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply