SQL Query With Variables

Question

SQL Query With Variables

lbarron

Grasshopper

Points: 24
More actions
May 12, 2016 at 4:05 am

#326302

Hi,
I have an SQL query which is looking at the hours booked against jobs and operations between 2 dates. The main query runs through current open jobs and it has a sub-query to sum up the hours booked for the correlated jobs and operations in the outer query. I have some manipulation to do based the result returned from the sub-query. Are you able to store the sub query results in a variable yet still maintain the correlation between the outer and sub query? My query as it stands is shown below.
Thanks
Lee
select joboper.jobnum as 'Job Number',
joboper.oprseq as 'Op Sequence',
joboper.opcode as 'Operation',
joboper.estprodhours as 'Std Hours',
joboper.actprodhours as 'Booked to Date',
(select sum(laborhrs)
from labordtl
where labordtl.company = 'SF03'
and labordtl.jobnum = jobhead.jobnum
and labordtl.opcode = joboper.opcode
and labordtl.oprseq = joboper.oprseq
and labordtl.payrolldate between '2016-04-01' and '2016-05-10'
) as 'Booked In Period'
from jobhead
join joboper
on jobhead.company = joboper.company
join jobopdtl
on jobhead.company = jobopdtl.company
and jobhead.jobnum = joboper.jobnum
and jobhead.jobnum = jobopdtl.jobnum
and jobopdtl.assemblyseq = joboper.assemblyseq
AND jobopdtl.oprseq = joboper.oprseq
wherejobhead.jobclosed = 0
and jobhead.jobcomplete = 0
andjobhead.jobreleased = 1
and jobhead.jobnum like '5%'
and jobhead.company = 'SF03'
AND subcontract = 0
order
by joboper.jobnum, joboper.opcode

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

{edit}

whoops no you cannot, because that is a correlated subquery...it's returning more than one value, joining the inner data and the outer data .

i would move the query to a proper join instead

does this produce the same results?

SELECT joboper.jobnum AS 'Job Number',

joboper.oprseq AS 'Op Sequence',

joboper.opcode AS 'Operation',

joboper.estprodhours AS 'Std Hours',

joboper.actprodhours AS 'Booked to Date',

Sum(labordtl.laborhrs) AS 'Booked In Period'

FROM jobhead

JOIN joboper

ON jobhead.company = joboper.company

JOIN jobopdtl

ON jobhead.company = jobopdtl.company

AND jobhead.jobnum = joboper.jobnum

AND jobhead.jobnum = jobopdtl.jobnum

AND jobopdtl.assemblyseq = joboper.assemblyseq

AND jobopdtl.oprseq = joboper.oprseq

JOIN labordtl

ON labordtl.company = 'SF03'

AND labordtl.jobnum = jobhead.jobnum

AND labordtl.opcode = joboper.opcode

AND labordtl.oprseq = joboper.oprseq

AND labordtl.payrolldate BETWEEN '2016-04-01' AND '2016-05-10'

WHERE jobhead.jobclosed = 0

AND jobhead.jobcomplete = 0

AND jobhead.jobreleased = 1

AND jobhead.jobnum LIKE '5%'

AND jobhead.company = 'SF03'

AND subcontract = 0

GROUP BY joboper.jobnum,

joboper.oprseq,

joboper.opcode,

joboper.estprodhours,

joboper.actprodhours

ORDER BY joboper.jobnum,

joboper.opcode

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lbarron Grasshopper Points: 24 More actions · Answer 2

Thanks for the reply.

I'll test the query and come back to you.

Thanks again.

Lee