quotes in SQL Statement
I have a select statement in a SP in an SQL 2008 database that contains the following:
SUM(case when b.TrxType = 'R' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received
If I have this outside the stored procedure, it works fine...but inside the procedure it states that there is incorrect syntax near 'R'.
If I change the 'R' to ' 'R' ' the error goes away , but the procedure does not return the value of R.
Any ideas?
SUM(case when b.TrxType = 'R' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received
If I have this outside the stored procedure, it works fine...but inside the procedure it states that there is incorrect syntax near 'R'.
If I change the 'R' to ' 'R' ' the error goes away , but the procedure does not return the value of R.
Any ideas?
Last Comment
PortletPaul
are you using dynamic SQL?
ASKER
Full statement:
(
@chkInventory int, @txtCustomerNumber varchar(MAX), @strGrade varchar(max), @strRodSize varchar(max), @strSecondaryRodSize varchar(MAX), @strHeat varchar(max)
)
as
BEGIN
SET NOCOUNT ON;
DECLARE @return_value int,
@strAllocateSQL varchar(max)
SET @strHeat = NULLIF(@strHeat,'')
set @strAllocateSQL = 'SELECT x.* ,(Received-Shipped-Transfe
,(Received-Allocated) AS Available
,case when Received>0 then cast(Weight/ case when (Received-Shipped-Transfer
FROM (SELECT b.HeatID,b.Customer AS Customer,a.GradeType,a.Rod
SUM(case when b.TrxType = 'R' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received,
SUM(case when b.TrxType = 'S' AND b.TransferToCustomer = 0 then b.CoilsShipped else 0 end) As Shipped,
SUM(case when b.TrxType = 'A' then b.NoOfCoils else 0 end) As Allocated
,SUM(case when b.TrxType = 'R' AND b.LbsTransferred > 0 then b.NoOfCoils else 0 end) As TransferredIn
,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As TransferredOut
,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As Transferred
,SUM(b.LbsRecd-b.LBSShippe
,MIN(case when b.TrxType = 'R' then b.TrxDate end) As EarlyDate
from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
a.ID = b.HeatID
WHERE 1 = 1 '
if( @chkInventory = 0 )
begin
set @strAllocateSQL = @strAllocateSQL + ' AND (b.TransferFromCustomer IN (70,79,74,81,97) OR b.CustomerNumber IN (70,79,74,81,97) OR b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) ) ' -- cast( ' + @txtCustomerNumber + 'as int) '
if (@strGrade != '')
begin
set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
end
if( @strRodSize != '')
begin
SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
end
end
set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,b.Customer,a.RodS
EXEC(@strAllocateSQL )
PRINT @strAllocateSQL
END
(
@chkInventory int, @txtCustomerNumber varchar(MAX), @strGrade varchar(max), @strRodSize varchar(max), @strSecondaryRodSize varchar(MAX), @strHeat varchar(max)
)
as
BEGIN
SET NOCOUNT ON;
DECLARE @return_value int,
@strAllocateSQL varchar(max)
SET @strHeat = NULLIF(@strHeat,'')
set @strAllocateSQL = 'SELECT x.* ,(Received-Shipped-Transfe
,(Received-Allocated) AS Available
,case when Received>0 then cast(Weight/ case when (Received-Shipped-Transfer
FROM (SELECT b.HeatID,b.Customer AS Customer,a.GradeType,a.Rod
SUM(case when b.TrxType = 'R' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received,
SUM(case when b.TrxType = 'S' AND b.TransferToCustomer = 0 then b.CoilsShipped else 0 end) As Shipped,
SUM(case when b.TrxType = 'A' then b.NoOfCoils else 0 end) As Allocated
,SUM(case when b.TrxType = 'R' AND b.LbsTransferred > 0 then b.NoOfCoils else 0 end) As TransferredIn
,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As TransferredOut
,SUM(case when b.TrxType = 'S' AND b.TransferToCustomer > 0 then b.CoilsShipped else 0 end) As Transferred
,SUM(b.LbsRecd-b.LBSShippe
,MIN(case when b.TrxType = 'R' then b.TrxDate end) As EarlyDate
from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
a.ID = b.HeatID
WHERE 1 = 1 '
if( @chkInventory = 0 )
begin
set @strAllocateSQL = @strAllocateSQL + ' AND (b.TransferFromCustomer IN (70,79,74,81,97) OR b.CustomerNumber IN (70,79,74,81,97) OR b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) ) ' -- cast( ' + @txtCustomerNumber + 'as int) '
if (@strGrade != '')
begin
set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
end
if( @strRodSize != '')
begin
SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
end
end
set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,b.Customer,a.RodS
EXEC(@strAllocateSQL )
PRINT @strAllocateSQL
END
As alluded to previously (no points please) since you are using dynamic sql you have to escape any single quotes. This is true in a Stored Procedure as well as SQL Script.
ASKER
If I change
SUM(case when b.TrxType = 'R' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received,
to
SUM(case when b.TrxType =' 'R'' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received,
the R never sums
SUM(case when b.TrxType = 'R' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received,
to
SUM(case when b.TrxType =' 'R'' AND b.LbsTransferred = 0 then b.NoOfCoils else 0 end) As Received,
the R never sums
Incidentally, there is no need to use Dynamic SQL here. There are three reasons for that:
1. Typically bad performance.
2. Lousy security. Aside from the risk of SQL Injection you will have to GRANT SELECT permissions to the user that runs this Stored Procedure. This is a bad idea.
3. Nightmare to maintain.
1. Typically bad performance.
2. Lousy security. Aside from the risk of SQL Injection you will have to GRANT SELECT permissions to the user that runs this Stored Procedure. This is a bad idea.
3. Nightmare to maintain.
This is how you would do it without using Dynamic SQL (and yes you could combine those two queries into one, however I would not recommend it):
CREATE PROCEDURE usp_YourStoredProedure
@chkInventory int,
@txtCustomerNumber varchar(MAX), -- Don't use varchar(MAX) where it is not appropriate:
@strGrade varchar(MAX), -- Are you really going to pass in up to 2 billion characters?
@strRodSize varchar(MAX),
@strSecondaryRodSize varchar(MAX), -- not used
@strHeat varchar(MAX) -- not used
AS
SET NOCOUNT ON;
--DECLARE @return_value int, -- not used
-- @strAllocateSQL varchar(max)
--SET @strHeat = NULLIF(@strHeat, '')
IF @chkInventory = 0
SELECT x.*, -- Don't use *, columns should be explicitly named
x.Received - x.Shipped - x.Transferred AS OnHand,
x.Received - x.Allocated AS Available,
CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
ELSE x.Received - x.Shipped - x.Transferred
END AS int)
ELSE 0
END AS AvgWt
FROM (SELECT b.HeatID,
b.Customer AS Customer,
a.GradeType,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END) AS Received,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer = 0 THEN b.CoilsShipped
ELSE 0
END) AS Shipped,
SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END) AS Allocated,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred > 0 THEN b.NoOfCoils
ELSE 0
END) AS TransferredIn,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS TransferredOut,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS Transferred,
SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
END) AS EarlyDate
FROM tblHeat AS a
LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
WHERE (b.TransferFromCustomer IN (70, 79, 74, 81, 97)
OR b.CustomerNumber IN (70, 79, 74, 81, 97)
OR b.CustomerNumber = CAST(@txtCustomerNumber AS int)
)
AND (@strGrade = ''
OR a.Grade = @strGrade
)
AND (@strRodSize = ''
OR a.RodSize = @strRodSize
)
GROUP BY b.HeatID,
b.Customer,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.GradeType,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod
HAVING Received - Allocated > 0
) AS x
ELSE
SELECT x.*, -- Don't use *, columns should be explicitly named
x.Received - x.Shipped - x.Transferred AS OnHand,
x.Received - x.Allocated AS Available,
CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
ELSE x.Received - x.Shipped - x.Transferred
END AS int)
ELSE 0
END AS AvgWt
FROM (SELECT b.HeatID,
b.Customer AS Customer,
a.GradeType,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END) AS Received,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer = 0 THEN b.CoilsShipped
ELSE 0
END) AS Shipped,
SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END) AS Allocated,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred > 0 THEN b.NoOfCoils
ELSE 0
END) AS TransferredIn,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS TransferredOut,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS Transferred,
SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
END) AS EarlyDate
FROM tblHeat AS a
LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
GROUP BY b.HeatID,
b.Customer,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.GradeType,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod
HAVING Received - Allocated > 0
) AS x
As acperkins said, if you can avoid using dynamic SQL, don't use it. That said, dynamic SQL being outside the context of upper level caller procedure, you can get variables back using sp_executesql stored proc. For more info:
http://technet.microsoft.com/en-us/library/ms188001.aspx
http://technet.microsoft.com/en-us/library/ms188001.aspx
ASKER
Just tried to run your example (thanks BTW!), but I'm getting an "Invalid Column names" in the HAVING clause. Any thoughts?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Initial testing are giving expected results. I really appreciate the quick response and explanation. Super job!
ASKER
I have the following SP I am using.
All my "S" records come back as "0"
the routine:
select a.HeatNumber,b.RodSize,b.C
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer = 0 THEN b.CoilsShipped
ELSE 0
END) AS Shipped
FROM tblHeatTransaction AS b
JOIN tblHeat AS a ON a.ID = b.HeatID
WHERE (b.TransferFromCustomer IN (70, 79, 74, 81, 97,10)
OR b.CustomerNumber IN (70, 79, 74, 81, 97,10)) and a.RodSize = '1/4' AND a.Grade = '1022'
GROUP BY
b.HeatNumber,
a.HeatNumber,
b.Customer,
b.RodSize,
b.Grade,
a.Consignment
( I reveresed tblHeat and tblHeatTransaction) in this example, I get values in the shipped column. If
I put it all together, I get zip.
All my "S" records come back as "0"
USE [OrderEntry_prodSQL]
GO
/****** Object: StoredProcedure [dbo].[usp_YourStoredProedure2] Script Date: 10/16/2013 18:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_YourStoredProedure2]
@chkInventory int,
@txtCustomerNumber varchar(10),
@strGrade varchar(10),
@strRodSize varchar(8)
AS
SET NOCOUNT ON;
IF @chkInventory = 0
SELECT x.*,
x.Received - x.Shipped - x.Transferred AS OnHand,
x.Received - x.Allocated AS Available,
CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
ELSE x.Received - x.Shipped - x.Transferred
END AS int)
ELSE 0
END AS AvgWt
FROM (SELECT b.HeatID,
b.Customer AS Customer,
a.GradeType,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END) AS Received,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer = 0 THEN b.CoilsShipped
ELSE 0
END) AS Shipped,
SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END) AS Allocated,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred > 0 THEN b.NoOfCoils
ELSE 0
END) AS TransferredIn,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS TransferredOut,
SUM(CASE WHEN b.TrxType = 'R'
AND b.TransferToCustomer > 0 THEN b.NoOfCoils
ELSE 0
END) AS Transferred,
SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
END) AS EarlyDate
FROM tblHeat AS a
LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
WHERE (b.TransferFromCustomer IN (70, 79, 74, 81, 97,10)
OR b.CustomerNumber IN (70, 79, 74, 81, 97,10)
)
AND (@strGrade = ''
OR a.Grade = '1022'
)
AND (@strRodSize = ''
OR a.RodSize = '1/4'
)
GROUP BY b.HeatID,
b.Customer,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.GradeType,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod
HAVING SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END) -
SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END) > 0
) AS x
ELSE
SELECT x.*, -- Don't use *, columns should be explicitly named
x.Received - x.Shipped - x.Transferred AS OnHand,
x.Received - x.Allocated AS Available,
CASE WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
ELSE x.Received - x.Shipped - x.Transferred
END AS int)
ELSE 0
END AS AvgWt
FROM (SELECT b.HeatID,
b.Customer AS Customer,
a.GradeType,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END) AS Received,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer = 0 THEN b.CoilsShipped
ELSE 0
END) AS Shipped,
SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END) AS Allocated,
SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred > 0 THEN b.NoOfCoils
ELSE 0
END) AS TransferredIn,
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS TransferredOut,
SUM(CASE WHEN b.TrxType = 'R'
AND b.TransferToCustomer > 0 THEN b.NoOfCoils
ELSE 0
END) AS Transferred,
SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight,
MIN(CASE WHEN b.TrxType = 'R' THEN b.TrxDate
END) AS EarlyDate
FROM tblHeat AS a
LEFT JOIN tblHeatTransaction AS b ON a.ID = b.HeatID
GROUP BY b.HeatID,
b.Customer,
a.RodSize,
a.HeatNumber,
a.Grade,
a.VendorNumber,
a.Consignment,
a.GradeType,
a.Vendor,
a.CustomerNumber,
a.CustomerPO,
a.PremiumRod
HAVING SUM(CASE WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END) -
SUM(CASE WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END) > 0
) AS x
GO
the routine:
select a.HeatNumber,b.RodSize,b.C
SUM(CASE WHEN b.TrxType = 'S'
AND b.TransferToCustomer = 0 THEN b.CoilsShipped
ELSE 0
END) AS Shipped
FROM tblHeatTransaction AS b
JOIN tblHeat AS a ON a.ID = b.HeatID
WHERE (b.TransferFromCustomer IN (70, 79, 74, 81, 97,10)
OR b.CustomerNumber IN (70, 79, 74, 81, 97,10)) and a.RodSize = '1/4' AND a.Grade = '1022'
GROUP BY
b.HeatNumber,
a.HeatNumber,
b.Customer,
b.RodSize,
b.Grade,
a.Consignment
( I reveresed tblHeat and tblHeatTransaction) in this example, I get values in the shipped column. If
I put it all together, I get zip.
I don't see much point in the left join (perhaps there is, just that I'm not seeing it) as all case expressions are based on values in tblHeatTransaction. So either you just move to an inner join and/or reverse the tables (I did both below).
If the left join really is required, then your outer query should probably use ISNULL() and/or you may have to reconsider all the case expressions to cater for nulls.
Also:
You could remove the IF/ELSE test on @chkInventory by joining that condition into the where clause and hence reduce the amount of code being tested.
I have also moved the case expressions around so tests on b.TrxType are in A,R,S sequence (not a necessary thing I just found it easier to read)
If the left join really is required, then your outer query should probably use ISNULL() and/or you may have to reconsider all the case expressions to cater for nulls.
Also:
You could remove the IF/ELSE test on @chkInventory by joining that condition into the where clause and hence reduce the amount of code being tested.
I have also moved the case expressions around so tests on b.TrxType are in A,R,S sequence (not a necessary thing I just found it easier to read)
CREATE PROCEDURE usp_YourStoredProedure @chkInventory int,
@txtCustomerNumber varchar(MAX), -- Don't use varchar(MAX) where it is not appropriate:
@strGrade varchar(MAX), -- Are you really going to pass in up to 2 billion characters?
@strRodSize varchar(MAX),
@strSecondaryRodSize varchar(MAX), -- not used
@strHeat varchar(MAX) -- not used
AS
SET NOCOUNT ON;
--DECLARE @return_value int, -- not used
-- @strAllocateSQL varchar(max)
--SET @strHeat = NULLIF(@strHeat, '')
SELECT
x.HeatID
, x.Customer
, x.GradeType
, x.RodSize
, x.HeatNumber
, x.Grade
, x.VendorNumber
, x.Consignment
, x.Vendor
, x.CustomerNumber
, x.CustomerPO
, x.PremiumRod
, x.Allocated --<< if left joining use ISNULL(x.Allocated) as Allocated
, x.Received
, x.TransferredIn
, x.Transferred
, x.EarlyDate
, x.Shipped
, x.TransferredOut
, x.Weight
, x.Received - x.Shipped - x.Transferred AS OnHand
, x.Received - x.Allocated AS Available
, CASE
WHEN x.Received > 0 THEN CAST(x.[Weight] / CASE
WHEN x.Received - x.Shipped - x.Transferred = 0 THEN NULL
ELSE x.Received - x.Shipped - x.Transferred
END AS int)
ELSE 0
END AS AvgWt
FROM (
SELECT
b.HeatID
, b.Customer
, a.GradeType
, a.RodSize
, a.HeatNumber
, a.Grade
, a.VendorNumber
, a.Consignment
, a.Vendor
, a.CustomerNumber
, a.CustomerPO
, a.PremiumRod
, SUM(CASE
WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END) AS Allocated
, SUM(CASE
WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END) AS Received
, SUM(CASE
WHEN b.TrxType = 'R'
AND b.LbsTransferred > 0 THEN b.NoOfCoils
ELSE 0
END) AS TransferredIn
, SUM(CASE
WHEN b.TrxType = 'R'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS Transferred
, MIN(CASE
WHEN b.TrxType = 'R' THEN b.TrxDate
END) AS EarlyDate
, SUM(CASE
WHEN b.TrxType = 'S'
AND b.TransferToCustomer = 0 THEN b.CoilsShipped
ELSE 0
END) AS Shipped
, SUM(CASE
WHEN b.TrxType = 'S'
AND b.TransferToCustomer > 0 THEN b.CoilsShipped
ELSE 0
END) AS TransferredOut
, SUM(b.LbsRecd - b.LBSShipped + b.LbsTransferred) AS Weight
FROM tblHeatTransaction AS b
INNER JOIN tblHeat AS a ON a.ID = b.HeatID
WHERE ( @chkInventory = 0
AND (
b.TransferFromCustomer IN (70, 79, 74, 81, 97)
OR b.CustomerNumber IN (70, 79, 74, 81, 97)
OR b.CustomerNumber = CAST(@txtCustomerNumber AS int)
)
AND (@strGrade = '' OR a.Grade = @strGrade)
AND (@strRodSize = '' OR a.RodSize = @strRodSize)
)
OR
( @chkInventory <> 0 OR @chkInventory IS NULL )
GROUP BY
b.HeatID
, b.Customer
, a.RodSize
, a.HeatNumber
, a.Grade
, a.VendorNumber
, a.Consignment
, a.GradeType
, a.Vendor
, a.CustomerNumber
, a.CustomerPO
, a.PremiumRod
HAVING SUM(CASE
WHEN b.TrxType = 'R'
AND b.LbsTransferred = 0 THEN b.NoOfCoils
ELSE 0
END)
- SUM(CASE
WHEN b.TrxType = 'A' THEN b.NoOfCoils
ELSE 0
END)
> 0
) AS x
GO
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
