SQL Native Client and Excel

[lbarron]

Dear All,

I write some simple SQL reports in Excel by embedding the SQL within the command text box.

I have some SQL which works outside of Excel but when added to Excel with a parameter I get the following error:-

[Microsoft][SQL Native Client] The multi-part identifier "jobopdtl.oprseq" cannot be bound."

The query is as follows:-

select joboper.jobnum,
joboper.opcode,
joboper.estprodhours,
joboper.prodlabrate,
(joboper.estprodhours * joboper.prodlabrate)+joboper.estunitcost,
joboper.actlabcost,
opcomplete,
prodcode,
resourceid,
(select top 1 employeenum
from labordtl
where joboper.jobnum = labordtl.jobnum
and joboper.opcode = labordtl.opcode
and not employeenum = 'DUMMY'
order
by labordtlseq desc
) as 'Employee'
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
where jobhead.closeddate = ?
and joboper.jobnum like '5%'
and jobhead.company = 'SF03'
AND subcontract = 0
order by joboper.jobnum, joboper.opcode

If I remove the nested query it seems to work and if I remove the parameter it seems to work!

Anybody have any ideas?

Thanks

Lee

 

[SQLSister]

I don't know why you are getting the error as your query appears to be syntactically correct, but correlated subqueries are a very bad query technique and should almost never be used. Replace it with a join to a derived table and who knows it might fix your problem (since removing it seems to make your query work), but it will certainly improve performance.

"NOTHING is more important in a database than integrity." ESquared

 

[lbarron]

SQLSister,

Thanks for your reply.

I'm trying to give the derived table a go but I'm not sure I getting the syntax right!

My query is currently as follows:-

select joboper.jobnum,
joboper.opcode,
joboper.estprodhours,
joboper.prodlabrate,
(joboper.estprodhours * joboper.prodlabrate)+joboper.estunitcost,
joboper.actlabcost,
opcomplete,
prodcode,
resourceid,
dLaborDtl.employeenum
from jobhead
join (select top 1 jobnum,opcode,employeenum
from labordtl
where joboper.jobnum = labordtl.jobnum
and joboper.opcode = labordtl.opcode
and not employeenum = 'DUMMY'
order
by labordtlseq desc) as dLaborDtl
on joboper.jobnum = dLabordtl.jobnum
and joboper.opcode = dLaborDtl.opcode
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
where jobhead.jobclosed = 0
and jobhead.jobcomplete = 0
and left(jobhead.jobnum, 3) IN ('127', '147')
and jobhead.company = 'SF03'
AND subcontract = 0
order by joboper.jobnum, joboper.opcode

but when I run it I get the following errors : -

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "joboper.jobnum" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "joboper.opcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "joboper.jobnum" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "joboper.opcode" could not be bound.

Any ideas?

In the mean time I'll keep trying!!!

Thanks

Lee

 

[lbarron]

Hi,

I'm not sure if I'm going about thisthe correct way!

The reason I originally had the sub-query was becuase if I join the joboper and labordtl tables I get multiple rows returned for the same job. This is because we could have multiple labour bookings for the same job. All I am interested in is the latest booking to take place this is why I used the top function in the sub query.

The only information I need from the labordtl table is the employeenum of the latest labour transaction.

Can this be achieved in any other way other than a sub-query?

thanks

Lee