December 29, 2014 at 1:10 pm
Hello all, I was wondering if someone can help me with a query that I'm working on. What I want to do is return a row of data when my query doesn't return a record. I have two tables:
CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY
GO
CREATE TABLE dbo.def(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY
GO
INSERT INTO abc SELECT 1, 500.00 UNION SELECT 1, 200.00
INSERT INTO def SELECT 2, 300.00 UNION SELECT 3, 400.00
So when I run the following query:
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00
as you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
abc-1-200.00
abc-1-500.00
def-0-0.00
Any help would be greatly appreciated.
December 29, 2014 at 2:45 pm
Easiest might be to add a UNION [ALL] with a "NOT EXISTS" clause. Btw, I don't see any reason to do UNION rather than UNION ALL so I've used UNION ALL in the code below.
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION ALL
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
UNION ALL
SELECT 'def-0-0.00'
WHERE NOT EXISTS( SELECT 1 FROM def WHERE SeqNo = 1 )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 29, 2014 at 4:43 pm
Thanks for the post. Yeah, I noticed that I was using UNION instead of UNION ALL as well.
December 30, 2014 at 4:14 pm
see below
declare @seqNo int =1;
;with cte as
(
select 'abc' as tName,* from abc
union all
select 'def' as tName,* from def
),
cte2 as
(
select c.tName,isnull(t.SeqNo,0) SeqNo,isnull(t.Payment,0.00) Payment from cte c left join (select * from cte where seqNo=@seqNo) t on c.SeqNo=t.SeqNo and c.Payment=t.Payment
)
select tName+'-'+ltrim(str(seqNo))+'-'+cast(Payment as varchar(99)) from cte2
January 5, 2015 at 6:04 pm
Here's one that I believe will work with the desired record missing in either table:
CREATE TABLE #abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
);
GO
CREATE TABLE #def(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
);
GO
INSERT INTO #abc SELECT 1, 500.00 UNION SELECT 1, 200.00;
INSERT INTO #def SELECT 2, 300.00 UNION SELECT 3, 400.00;
DECLARE @SeqNo SMALLINT = 1;
SELECT ISNULL('abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR), x)
FROM
(
SELECT 'abc-0-0.00'
) a (x)
OUTER APPLY
(
SELECT SeqNo, Payment
FROM #abc
WHERE SeqNo = @SeqNo
) b
UNION ALL
SELECT ISNULL('def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR), x)
FROM
(
SELECT 'def-0-0.00'
) a (x)
OUTER APPLY
(
SELECT SeqNo, Payment
FROM #def
WHERE SeqNo = @SeqNo
) b
GO
DROP TABLE #abc, #def;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply