problem with UNION ALL query
-
I have a query that uses 'union all' and returns all the records (including the field 'BillingRateA') from the top query but not the 'BillingRateB' bottom (that field is just blank). If I run the bottom part alone 'BillingRateB' appears. Any clues?:
SELECT
c.clientname as 'Client',
c.BillingRateA as 'Rate',
b.billingtypeA as 'Unit',
m.SegmentName as 'Business Type'
FROM dbo.Client c
INNER JOIN dbo.MarketSegment m
ON c.MarketSegmentID = m.MarketSegmentID
inner join dbo.BillingType b
on c.BillingTypeA = b.BillingTypeid
UNION all
SELECT
c.clientname as 'Client',
c.BillingRateB as 'Rate',
b.billingtypeB as 'Unit',
m.SegmentName as 'Business Type'
FROM dbo.Client c
INNER JOIN dbo.MarketSegment m
ON c.MarketSegmentID = m.MarketSegmentID
inner join dbo.BillingType b
on c.BillingTypeB = b.BillingTypeid
where c.BillingRateB is not null -
Since you are aliasing BillingRateA as 'Rate' and BillingRateB as 'Rate', are you saying that you are seeing a populated Rate column when you run just the bottom half, but for the corresponding row you are seeing an empty string in Rate when run as part of the union all? c.BillingRateB as Rate can't be null in the results because the where clause is excluding those (where c.BillingRateB is not null)
What datatype is BillingRateB? I ask because I don't see how a number would appear to be blank.
How many rows are returned in the union all? Any chance you're just expecting the data to be sorted a certain way and overlooking the row?
-
BillingRateB and BillinRateA are decimal. 49 rows are returned and there are 41 records in the table of which 8 in question do have a BillingRateB (and BillingRateA), but all 41 have a BillingRateA. Double-checked all the data.
-
So the count returned is right.
Can you provide one or more sample source rows and corresponding output rows (obfuscated as appropriate) where the problem lies?
-
Her is what I am getting:
-
What I am expecting is happening is that the datatype of BillingRateB is going to be something like VARCHAR and the value is something along the lines of '' or ' ' or something like that. A BLANK value is NOT the same thing as NULL.
Chances are, like what ratbak said, when you run the whole query, you are getting the expected results, you are just not seeing them in the order you are expecting.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Both BillingRateA and BillingRateB are decimal
CREATE TABLE [dbo].[Client](
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[ClientName] [varchar](50) NOT NULL,
[MarketSegmentID] [int] NULL,
[BillingTypeA] [int] NULL,
[BillingRateA] [decimal](8, 2) NULL,
[BillingTypeB] [int] NULL,
[BillingRateB] [decimal](8, 2) NULL, -
This is working:
SELECT * FROM (SELECT
c.clientname as 'Client',
c.BillingRateA as 'Rate',
b.BillingTypeA as 'Unit',
m.SegmentName as 'Business Type'
FROM dbo.Client c
INNER JOIN dbo.MarketSegment m
ON c.MarketSegmentID = m.MarketSegmentID
inner join dbo.BillingType b
on c.BillingTypeA = b.BillingTypeid
UNION all
SELECT
c.clientname as 'Client',
c.BillingRateB as 'Rate',
b.billingtypeA as 'Unit',
m.SegmentName as 'Business Type'
FROM dbo.Client c
INNER JOIN dbo.MarketSegment m
ON c.MarketSegmentID = m.MarketSegmentID
inner join dbo.BillingType b
on c.BillingTypeB = b.BillingTypeid
where c.BillingRateB is not null) torder by Client -
You indicated BillingRateB was blank, but in the picture it is BillingTypeB that is blank -- Rate (which is either BillingRateA or BillingRateB) is always populated. But there are blank values for Unit (which is either billingTypeA or billingTypeB) for eight rows.
The results you're showing don't seem to match the DDL.
You're showing values of "Header", Leaf", "Shelf", and empty string for Unit -- which according to your query is an alias for BillingType.billingTypeA or BillingType.billingTypeB.
In the query provided, you are joining BillingType to Client on those columns, which appear to be returning strings, but your DDL indicates those are integers. I don't see how both of those things could be true -- you couldn't return any rows w/ the strings shown if BillingType.billingTypeA & BillingType.billingTypeB are integers. And you would probably get no results if BillingType.billingTypeA & BillingType.billingTypeB are varchar; you might even get a data type exception on the join.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply