SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Clean up Union Double @pram issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

115 Posts

Posted - 12/06/2012 :  13:34:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  13:54:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000