Help view join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help view join problem

Author

Topic

Ixalmida
Starting Member

4 Posts

Posted - 2009-04-02 : 14:02:12

Hi. I am having a problem when I join two views together with some mysterious rows/values being added. The database is Spectrum Construction Management, so don't blame me for poor design.

Here is the query for view #1:

SELECT TOP 100 PERCENT SUBSTRING(LTRIM(Job_Number), 1, 3) AS Location, SUBSTRING(LTRIM(Job_Number), 5, 1) AS JobType, Cost_Type, Phase_Code, SUM(Est_Cost) AS EstCost
FROM         dbo.JC_PHASE_MASTER_MC
WHERE     (SUBSTRING(LTRIM(Job_Number), 6, 1) BETWEEN '7' AND '9') AND (SUBSTRING(LTRIM(Job_Number), 1, 3) LIKE '[^-WBF][^-]%') AND       (SUBSTRING(LTRIM(Job_Number), 5, 1) LIKE '[^0]')
GROUP BY SUBSTRING(LTRIM(Job_Number), 5, 1), Cost_Type, SUBSTRING(LTRIM(Job_Number), 1, 3), Phase_Code
ORDER BY SUBSTRING(LTRIM(Job_Number), 1, 3), SUBSTRING(LTRIM(Job_Number), 5, 1), Cost_Type, Phase_Code

Here is the query for view #2:

SELECT     TOP 100 PERCENT SUBSTRING(LTRIM(Job_Number), 1, 3) AS Location, SUBSTRING(LTRIM(Job_Number), 5, 1) AS JobType, Phase_Code, Cost_Type, SUM(Begin_Cost) + SUM(Cost_Period1) + SUM(Cost_Period2) + SUM(Cost_Period3) AS BeginCost
FROM         dbo.JC_PHASE_COST_BALANCE_MC
WHERE     ([Year] = '09') AND (SUBSTRING(LTRIM(Job_Number), 6, 1) BETWEEN '7' AND '9') AND (Phase_Code <> '') AND (SUBSTRING(LTRIM(Job_Number), 1, 3) LIKE '[^W]%')
GROUP BY SUBSTRING(LTRIM(Job_Number), 5, 1), Cost_Type, SUBSTRING(LTRIM(Job_Number), 1, 3), Phase_Code
ORDER BY SUBSTRING(LTRIM(Job_Number), 1, 3), SUBSTRING(LTRIM(Job_Number), 5, 1), Cost_Type, Phase_Code

Here is the JOIN view query:

SELECT     TOP 100 PERCENT A.Location, A.JobType, A.Cost_Type, A.Phase_Code, A.EstCost, B.BeginCost
FROM         dbo.AA_EST_VS_ACTUAL_VW_1 A LEFT OUTER JOIN dbo.AA_EST_VS_ACTUAL_VW_2 B ON A.Location = B.Location AND A.JobType = B.JobType AND A.Phase_Code = B.Phase_Code
ORDER BY A.Location, A.JobType, A.Cost_Type, A.Phase_Code

When I run View #1 and View #2 by themselves, I get one result from each for a particular Location/CostType/JobType/Phase_Code combination:

View #1:
Location  CostType  JobType     Phase_Code      EstCost
101	  N	    F	        011000      	329166.88

View #2:
Location  CostType  JobType     Phase_Code      BeginCost
101	  N	    F      	011000	        107246.84

When I run the JOIN view, I get:

Location  CostType  JobType   Phase_Code        EstCost         BeginCost
101	  N	    F	      011000      	329166.88	107246.84
101	  N	    F	      011000      	329166.88	10520478.27

Where the heck is that second HUGE number coming from??? The extra value seems to show up randomly in various rows, and isn't always the same value.

I tried combining the Location/CostType/JobType/Phase_Code into a single field index, but SQL gave me grouping errors. I also tried using DISTINCT in all my queries, but that didn't help.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-02 : 14:12:59

Are you forgetting joining basis cost type in the third view by any chance ?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-02 : 14:20:47

at first look your problem might be
SUM(Begin_Cost) + SUM(Cost_Period1) + SUM(Cost_Period2) + SUM(Cost_Period3) AS BeginCost. I would change that alias AS BeginCost to somethnig else like DarthVader and try it. Because you already have a field named BeginCost in one of those views.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Ixalmida
Starting Member

4 Posts

Posted - 2009-04-03 : 16:07:44

I ended up starting from scratch and creating 5 views and it is now working properly.

I think I could have done as much with three, but five views made it easier to track fields.

I'd still be interested in ways to make it faster, but this might be as good as it gets...

AA_PHASE_MASTER_VW (Query #1a):

SELECT DISTINCT 
     dbo.JC_PHASE_MASTER_MC.Phase_Code, dbo.JC_PHASE_MASTER_MC.Cost_Type, SUBSTRING(LTRIM(dbo.JC_PHASE_MASTER_MC.Job_Number), 
     1, 3) AS Location, SUBSTRING(LTRIM(dbo.JC_PHASE_MASTER_MC.Job_Number), 4, 1) AS Bseg, 
     SUBSTRING(LTRIM(dbo.JC_PHASE_MASTER_MC.Job_Number), 5, 1) AS JobType, dbo.JC_PHASE_MASTER_MC.Est_Cost, 
     dbo.JC_PHASE_MASTER_MC.Job_Number, dbo.JC_JOB_MASTER_MC.Status_Code
FROM
     dbo.JC_PHASE_MASTER_MC INNER JOIN
     dbo.JC_JOB_MASTER_MC ON dbo.JC_PHASE_MASTER_MC.Job_Number = dbo.JC_JOB_MASTER_MC.Job_Number
WHERE
     (dbo.JC_PHASE_MASTER_MC.Company_Code = 'U01') AND (SUBSTRING(LTRIM(dbo.JC_PHASE_MASTER_MC.Job_Number), 6, 1) LIKE '[78]')
     AND (dbo.JC_JOB_MASTER_MC.Status_Code = 'I')

AA_PHASE_COST_BALANCE_VW (Query #1b):

SELECT DISTINCT 
     SUBSTRING(LTRIM(dbo.JC_PHASE_COST_BALANCE_MC.Job_Number), 1, 3) AS Location, 
     SUBSTRING(LTRIM(dbo.JC_PHASE_COST_BALANCE_MC.Job_Number), 5, 1) AS JobType, 
     SUBSTRING(LTRIM(dbo.JC_PHASE_COST_BALANCE_MC.Job_Number), 4, 1) AS Bseg, dbo.JC_PHASE_COST_BALANCE_MC.Phase_Code, 
     dbo.JC_PHASE_COST_BALANCE_MC.Cost_Type, 
     dbo.JC_PHASE_COST_BALANCE_MC.Begin_Cost + dbo.JC_PHASE_COST_BALANCE_MC.Cost_Period1 +
       dbo.JC_PHASE_COST_BALANCE_MC.Cost_Period2 + dbo.JC_PHASE_COST_BALANCE_MC.Cost_Period3 AS ActualCost,
     dbo.JC_PHASE_COST_BALANCE_MC.Job_Number
FROM
     dbo.JC_PHASE_COST_BALANCE_MC INNER JOIN
     dbo.JC_JOB_MASTER_MC ON dbo.JC_PHASE_COST_BALANCE_MC.Job_Number = dbo.JC_JOB_MASTER_MC.Job_Number
WHERE
     (dbo.JC_PHASE_COST_BALANCE_MC.[Year] = '09') AND (dbo.JC_PHASE_COST_BALANCE_MC.Company_Code = 'U01') AND 
     (dbo.JC_JOB_MASTER_MC.Status_Code = 'I') AND (SUBSTRING(LTRIM(dbo.JC_PHASE_COST_BALANCE_MC.Job_Number), 6, 1) LIKE '[78]')

AA_ACTUAL_COST_VW (Query #2a, based on #1a):

SELECT     Location, JobType, Bseg, Phase_Code, Cost_Type, SUM(ActualCost) AS Actual
FROM         dbo.AA_PHASE_COST_BALANCE_VW
WHERE     (JobType = 'N') AND (Bseg LIKE '[FISBM]')
GROUP BY Location, JobType, Bseg, Phase_Code, Cost_Type

AA_EST_COST_VW (Query #2b, based on #1b):

SELECT     Location, JobType, Bseg, Cost_Type, Phase_Code, SUM(Est_Cost) AS EstCost
FROM         dbo.AA_PHASE_MASTER_VW
WHERE     (JobType = 'N') AND (Bseg LIKE '[FISBM]')
GROUP BY Location, JobType, Bseg, Cost_Type, Phase_Code

AA_EST_VS_ACTUAL_VW (joins #2a & #2b):

SELECT TOP 100 PERCENT
     dbo.AA_ACTUAL_COST_VW.Location, dbo.AA_ACTUAL_COST_VW.Bseg, dbo.AA_ACTUAL_COST_VW.JobType, 
     dbo.AA_ACTUAL_COST_VW.Phase_Code, dbo.AA_ACTUAL_COST_VW.Cost_Type, dbo.AA_ACTUAL_COST_VW.Actual, 
     dbo.AA_EST_COST_VW.EstCost
FROM
     dbo.AA_ACTUAL_COST_VW INNER JOIN
     dbo.AA_EST_COST_VW ON dbo.AA_ACTUAL_COST_VW.Location = dbo.AA_EST_COST_VW.Location AND 
     dbo.AA_ACTUAL_COST_VW.JobType = dbo.AA_EST_COST_VW.JobType AND dbo.AA_ACTUAL_COST_VW.Bseg = dbo.AA_EST_COST_VW.Bseg AND 
     dbo.AA_ACTUAL_COST_VW.Phase_Code = dbo.AA_EST_COST_VW.Phase_Code AND 
     dbo.AA_ACTUAL_COST_VW.Cost_Type = dbo.AA_EST_COST_VW.Cost_Type
ORDER BY 
     dbo.AA_ACTUAL_COST_VW.Location, dbo.AA_ACTUAL_COST_VW.Bseg, dbo.AA_ACTUAL_COST_VW.JobType, 
     dbo.AA_ACTUAL_COST_VW.Phase_Code, dbo.AA_ACTUAL_COST_VW.Cost_Type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources