September 18, 2014 at 10:07 am
Greetings, this is my first post on this message board.
I’m running a code on our database and I’m getting an error message. I would like to see if anybody could take a look at the code I am using and provide some feedback?
I am attempting to merge a dimension table (key) into my main data table. Problem I am having is that the main table has 106k rows and after I run the script without the else statement, it only returns 85k. I wanted to add the else statement as a catch all. Any thoughts?
Select a.Pbcat,b.*
From [dbo].[DIMObject] a, [dbo].[ObjectClass] b
Where A.BOC4 = B.BOC and left(b.BOC,2) <> '25'
else B.BOC = 'Unmapped' ---** Errors right here.
Union all
Select a.Pbcat,b.*
From [dbo].[DIMObject] a, [dbo].[ObjectClass] b
Where A.BOC4 = B.BOC and left(b.BOC,2) = '25' and a.cc = b.[Cost Center]
Code End:
September 18, 2014 at 10:26 am
ELSE cannot be used that way.
I can only be used in CASE statements.
I guess you need OR here.
What are you trying to achieve with that "ELSE"?
-- Gianluca Sartori
September 18, 2014 at 10:31 am
I needed to add some sort of Catch all statement that will force the query to grab all 106k rows and just fill in the column PBCAT with "Unmapped" if it cannot match it to anything. At this time it's only pulling 85k rows, and I'm missing approx 21k rows of data
September 18, 2014 at 10:47 am
I modified the code slightly to add an or statement with an additional criteria and I am now getting closer to the number I want, but I'm still not there. I need some sort of catch all for everything else
Select a.pbcat,b.*
From [dbo].[DIMObject] a, [dbo].[ObjectClass] b
Where A.BOC4 = B.BOC and left(b.BOC,2) <> '25'
Union all
Select a.Pbcat,b.*
From [dbo].[DIMObject] a, [dbo].[ObjectClass] b
Where A.BOC4 = B.BOC and left(b.BOC,2) = '25' and a.cc = b.[Cost Center] or A.BOC4 = B.BOC and left(b.BOC,2) = '32' and a.cc = b.[Cost Center]
September 18, 2014 at 3:23 pm
I have been scratching my head over this all day... Cause I do not understand the logic behind the way the query is running. I've scaled down the size of the database tables to make it easier to problem solve. I'm dealing with a dimension table which contains 33 Rows and 2 Columns and my Datatable which contains 17,785 Rows and 11 Columns.
The Dimension Table and the Datatable have a shared column with the same data BOC=BOC, if they match I want to Join (or Union) the data to label it properly using the key. What I don't understand is if all I am trying to do is label my data using the key, why am I ending up with 18,605 Rows after I run the operation. Shouldn't 17,785 be the maximum amount of Rows since all I am trying to do is label the original data?
September 18, 2014 at 3:31 pm
Doesn't the UNION ALL include duplicates. Could that be why you are getting more rows than you thought. UNION removes the duplicates.
Good luck
Kathy
September 18, 2014 at 3:35 pm
Yes you are right. But I did run the code with both Union and Union all. With Union I had slightly less rows returned, but still basically the same result.
Maybe I taking the wrong approach. I basically am trying to bring over Labels for the Data, so the amount of rows should not exceed the amount of rows inside the data table. Any thoughts?
September 19, 2014 at 1:12 pm
Took me forever to finally solve the problem. The issue was both that I was using a Union All rather than a join and that I had duplicate records in my dimension table resulting in duplication of the output. I cleaned up the data source and used the following code:
SELECT
,b.[PBCat]
,a.[Station]
,a.[Facility]
,a.[CC]
,a.[Office]
,a.[BOC]
,a.[BOCName]
,left(a.[Appro],4) as [Appro]
,a.[FY]
,a.[Obs]
,a.[FTE]
,a.[Month]
FROM [BudgetFormulation].[dbo].[ObjectClass] a
Left Join [BudgetFormulation].[dbo].[DIMObject] b
On a.[BOC]=b.[BOC4]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply