sql-servert-sqlsql-server-2014-express
Ensuring that a comma is not added if only one record is found, or that both are added
Consider the follow piece of an SQL query that is designed to construct a complex return in the form of XML:
ISNULL(Logbook1 + ',', '') + ISNULL(Logbook2 + ',', '') + ISNULL(Logbook3 + ',', '') AS '@logBookNums',
In essence this will search the appropriate record. If it finds that no records exist for LogBook1, LogBook2 or LogBook3 it will return an empty string. If it finds one record in LogBook1 it will return that and one trailing comma.
Unfortunately the WCF service to which this XML is being sent is appallingly documented (for which in truth read none whatsoever) and it is rejecting a single logbook number with a trailing comma.
My guess is that it should either be submitted as:
logBookNums="12345,,"
Or
logBookNums="12345"
How should I alter the above line of SQL to output one or the other, and (if the query finds two logbook numbers) to produce output like this:
logBookNums="12345,12346,"
Or
logBookNums="12345,12346"
This is the entire query into which I would need to insert a solution to handle this predicament for both @logBookNums and @landingDecNums.
CREATE PROCEDURE dbo.CreateErsSalesAddSubmissionXmlByDateRange
-- Add the parameters for the stored procedure here
@uname VARCHAR(10) ,
@pword VARCHAR(10) ,
@sntype VARCHAR(1) ,
@action VARCHAR(10) ,
@salesContractRef VARCHAR(10),
@auctionId NCHAR(10) ,
@startDate DATE,
@endDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
RTRIM(@uname) AS '@uname',
RTRIM(@pword) AS '@pword',
(SELECT
@snType AS '@snType',
RTRIM(@action) AS '@action',
COALESCE(@salesContractRef, '') AS '@salesContractRef',
CONVERT(VARCHAR(10), DateOfPurchase, 112) AS '@saleDate',
RTRIM(COALESCE(@auctionID, '')) AS '@auctionID',
ISNULL(Logbook1 + ',', '') + ISNULL(Logbook2 + ',', '') + ISNULL(Logbook3 + ',', '') AS '@logBookNums',
ISNULL(LandingDecNumber1 + ',', '') + ISNULL(LandingDecNumber2 + ',', '') + ISNULL(LandingDecNumber3 + ',', '') AS '@landingDecNums',
COALESCE(VesselName, '') AS '@vesselName',
RTRIM(VesselPLN) AS '@vesselPln',
RTRIM(VesselOwner) AS '@vesselMasterOwner',
COALESCE(CONVERT(VARCHAR(10), LandingDate1, 112), '') AS '@landingDate1',
COALESCE(CONVERT(VARCHAR(10), LandingDate2, 112), '') AS '@landingDate2',
COALESCE(CONVERT(VARCHAR(10), LandingDate3, 112), '') AS '@landingDate3',
RTRIM(CountryOfLanding) AS '@countryOfLanding',
RTRIM(PortOfLanding) AS '@landingPortCode',
RTRIM(lh1.LandingId) AS '@internalRef',
(SELECT
COALESCE(RTRIM(SpeciesCode),'') AS '@speciesCode',
RTRIM(FishingArea) AS '@faoAreaCode',
COALESCE(RTRIM(IcesZone),'') AS '@ZoneCode',
COALESCE(RTRIM(ld.DisposalCode),'') AS '@disposalCode',
COALESCE(ld.FreshnessGrade,'') AS '@freshnessCode',
COALESCE(ld.ProductSize,'') AS '@sizeCode',
COALESCE(ld.PresentationCode,'') AS '@presentationCode',
COALESCE(ld.PresentationState,'') AS '@stateCode',
RTRIM(ld.NumberOfFish) AS '@numberOfFish',
FORMAT(ld.Quantity, 'N2') AS '@weightKgs',
FORMAT(Quantity * ld.UnitPrice, 'N2') AS '@value',
COALESCE(ld.Currency,'') AS '@currencyCode',
RTRIM(ld.WithdrawnDestinationCode) AS '@withdrawnDestinationCode',
RTRIM(ld.BuyersRegistrationCode) AS '@buyerReg',
RTRIM(ld.SalesContractRef) AS '@salesContractRef'
FROM LandingDetails ld
JOIN LandingHeaders lh
ON ld.LandingId = lh.LandingId
WHERE ld.LandingId = lh1.LandingId
FOR XML PATH ('salesline'), TYPE)
FROM LandingHeaders lh1
WHERE lh1.AllocatedErsId IS NULL AND lh1.LandingDate1 BETWEEN @startDate AND @endDate
ORDER BY VesselName,lh1.LandingId
FOR XML PATH ('salesnote'), TYPE)
FOR XML PATH ('ers')
END
GO
EDIT (what I have tried following answer below)
SELECT
RTRIM(@uname) AS '@uname'
,RTRIM(@pword) AS '@pword'
,(SELECT
@snType AS '@snType'
,RTRIM(@action) AS '@action'
,COALESCE(@salesContractRef, '') AS '@salesContractRef'
,CONVERT(VARCHAR(10), DateOfPurchase, 112) AS '@saleDate'
,RTRIM(COALESCE(@auctionID, '')) AS '@auctionID'
,(
SELECT ',' + CAST(LogbookX AS VARCHAR(100))
FROM
(
VALUES(Logbook1),(Logbook2),(Logbook3)
) AS x(LogbookX)
FOR XML PATH('')
),1,1,'')
AS NumList(Concatenated)
WHERE NumList.Concatenated IS NOT NULL AS '@logBookNums'
,ISNULL(LandingDecNumber1 + ',', '') + ISNULL(LandingDecNumber2 + ',', '') + ISNULL(LandingDecNumber3 + ',', '') AS '@landingDecNums'
,COALESCE(VesselName, '') AS '@vesselName'
,RTRIM(VesselPLN) AS '@vesselPln'
,RTRIM(VesselOwner) AS '@vesselMasterOwner'
,COALESCE(CONVERT(VARCHAR(10), LandingDate1, 112), '') AS '@landingDate1'
,COALESCE(CONVERT(VARCHAR(10), LandingDate2, 112), '') AS '@landingDate2'
,COALESCE(CONVERT(VARCHAR(10), LandingDate3, 112), '') AS '@landingDate3'
,RTRIM(CountryOfLanding) AS '@countryOfLanding'
,RTRIM(PortOfLanding) AS '@landingPortCode'
,RTRIM(lh1.LandingId) AS '@internalRef'
Additional edit to help clarify comments from the answer below
The full amended query now looks as follows:
CREATE PROCEDURE dbo.CreateErsSalesAddSubmissionXmlByDateRange
-- Add the parameters for the stored procedure here
@uname VARCHAR(10),
@pword VARCHAR(10),
@sntype VARCHAR(1),
@action VARCHAR(10),
@salesContractRef VARCHAR(10),
@auctionId NCHAR(10),
@startDate DATE,
@endDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
RTRIM(@uname) AS '@uname'
,RTRIM(@pword) AS '@pword'
,(SELECT
@snType AS '@snType'
,RTRIM(@action) AS '@action'
,COALESCE(@salesContractRef, '') AS '@salesContractRef'
,CONVERT(VARCHAR(10), DateOfPurchase, 112) AS '@saleDate'
,RTRIM(COALESCE(@auctionID, '')) AS '@auctionID'
,STUFF
((SELECT
',' + CAST(LogbookX AS VARCHAR(100))
FROM (
VALUES (Logbook1), (Logbook2), (Logbook3)
) AS x (LogbookX)
FOR XML PATH (''))
, 1, 1, '')
AS '@logBookNums'
,ISNULL(LandingDecNumber1 + ',', '') + ISNULL(LandingDecNumber2 + ',', '') + ISNULL(LandingDecNumber3 + ',', '') AS '@landingDecNums'
,COALESCE(VesselName, '') AS '@vesselName'
,RTRIM(VesselPLN) AS '@vesselPln'
,RTRIM(VesselOwner) AS '@vesselMasterOwner'
,COALESCE(CONVERT(VARCHAR(10), LandingDate1, 112), '') AS '@landingDate1'
,COALESCE(CONVERT(VARCHAR(10), LandingDate2, 112), '') AS '@landingDate2'
,COALESCE(CONVERT(VARCHAR(10), LandingDate3, 112), '') AS '@landingDate3'
,RTRIM(CountryOfLanding) AS '@countryOfLanding'
,RTRIM(PortOfLanding) AS '@landingPortCode'
,RTRIM(lh1.LandingId) AS '@internalRef'
,(SELECT
COALESCE(RTRIM(SpeciesCode), '') AS '@speciesCode'
,RTRIM(FishingArea) AS '@faoAreaCode'
,COALESCE(RTRIM(IcesZone), '') AS '@ZoneCode'
,COALESCE(RTRIM(ld.DisposalCode), '') AS '@disposalCode'
,COALESCE(ld.FreshnessGrade, '') AS '@freshnessCode'
,COALESCE(ld.ProductSize, '') AS '@sizeCode'
,COALESCE(ld.PresentationCode, '') AS '@presentationCode'
,COALESCE(ld.PresentationState, '') AS '@stateCode'
,RTRIM(ld.NumberOfFish) AS '@numberOfFish'
,FORMAT(ld.Quantity, 'N2') AS '@weightKgs'
,FORMAT(Quantity * ld.UnitPrice, 'N2') AS '@value'
,COALESCE(ld.Currency, '') AS '@currencyCode'
,RTRIM(ld.WithdrawnDestinationCode) AS '@withdrawnDestinationCode'
,RTRIM(ld.BuyersRegistrationCode) AS '@buyerReg'
,RTRIM(ld.SalesContractRef) AS '@salesContractRef'
FROM LandingDetails ld
JOIN LandingHeaders lh
ON ld.LandingId = lh.LandingId
WHERE ld.LandingId = lh1.LandingId
FOR XML PATH ('salesline'), TYPE)
FROM LandingHeaders lh1
WHERE lh1.AllocatedErsId IS NULL
AND lh1.LandingDate1 BETWEEN @startDate AND @endDate
ORDER BY VesselName, lh1.LandingId
FOR XML PATH ('salesnote'), TYPE)
FOR XML PATH ('ers')
END
GO
when there are logBookNums then we are getting correctly formatted xml as you see below.
However when there aren't we should get logBookNums="" but as you see we get nothing.
Solution
You might try it like this
You'll need an "IN"-search, there's no search like aNumber='123,345,678'
DECLARE @tbl TABLE(Logbook1 INT,Logbook2 INT,Logbook3 INT);
INSERT INTO @tbl VALUES
(12345,23456,56789)
,(234,NULL,NULL)
,(NULL,123,NULL)
,(NULL,NULL,NULL);
SELECT 'logbBookNums IN(' + NumList.Concatenated +')'
FROM @tbl
CROSS APPLY
(
SELECT STUFF
(
(
SELECT ',' + CAST(LogbookX AS VARCHAR(100))
FROM
(
VALUES(Logbook1),(Logbook2),(Logbook3)
) AS x(LogbookX)
FOR XML PATH('')
),1,1,'')
)AS NumList(Concatenated)
WHERE NumList.Concatenated IS NOT NULL
The result
logbBookNums IN(12345,23456,56789)
logbBookNums IN(234)
logbBookNums IN(123)
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?