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 2008 Forums
 Transact-SQL (2008)
 Clean up Union Double @pram issue

Author  Topic 

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-12-06 : 13:34:56
Ok I got my code to work but I was wondering if their is a way instead of me having to used 2 separate Like @parameters for 2 separate select statements that are combined by a union. Below is the code I would like to set it up that it ties to 1 Where statement if I can.

SELECT joblabordetail.[Cost-Code] AS CCode, joblabordetail.[div-code] AS DIVCODE, job.[Job-no] AS JOBNO, job.Description, joblabordetail.EmployeeName,
[cost-code].Description AS Expr1, joblabordetail.TotalHours, joblabordetail.RegularHours, joblabordetail.OverTimeHours, joblabordetail.TotalEarnings,
joblabordetail.TotalFringes, joblabordetail.TotalPayroll, joblabordetail.RegularEarnings, joblabordetail.TransactionDate AS Date, NULL AS EstimateHours, NULL
AS JOBNO1
FROM [cost-code] AS [cost-code] LEFT OUTER JOIN
joblabordetail AS joblabordetail ON [cost-code].[Cost-code] = joblabordetail.[Cost-Code] LEFT OUTER JOIN
job AS job ON joblabordetail.[job-no] = job.[Job-no]
WHERE (joblabordetail.[div-code] = '10') AND (joblabordetail.TransactionDate BETWEEN @StartDate AND @EndDate) AND (joblabordetail.[job-no] LIKE @job)
UNION
SELECT CostCode AS CCode, NULL AS DIVCODE, [job-no] AS JOBNO1, NULL AS Description, NULL AS EmployeeName, NULL AS Expr1, NULL AS TotalHours, NULL
AS RegularHours, NULL AS OverTimeHours, NULL AS TotalEarnings, NULL AS TotalFringes, NULL AS TotalPayroll, NULL AS RegularEarnings, NULL AS Date,
EstimateHours, NULL AS JOBNO
FROM JobCostEstimate
WHERE ([job-no] LIKE @job2)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 13:54:30
You could conceivably make the result of the union into a subquery and apply the like clause on the result of that subquery - BUT, I would leave it the way it is. Making it into a subquery would be less efficient.
Go to Top of Page
   

- Advertisement -