Create table from Pass thru query

[Guest]

I'm having trouble creating a local table in the current database. I'm trying
to use a SQL pass through query. Could you look at this and tell me what I'm
doing wrong?

CREATE TABLE ActiveEmplyeeAnnualRate (last_name, first_name, annual_rt)

select a.emplid, b.last_name, b.first_name, a.annual_rt into Active Emplyee
Annual Rate
from hrwpv.ps_job a, hrwpv.ps_names b
where a.emplid = b.emplid
and a.empl_status in ('A', 'P', 'L')
and a.emplid > '000000001'
and a.company < '90'


and a.effdt = (select max(aa.effdt)
from hrwpv.ps_job aa
where a.emplid = aa.emplid)


and a.effseq = (select max(aaa.effseq)
from hrwpv.ps_job aaa
where a.emplid = aaa.emplid
and a.effdt = aaa.effdt)

;

 

[Guest]

I see three fields in your CREATE TABLE statement and four fields in your
SELECT...INTO statement

 

[Guest]

Bruce,

is that all?

 

[Guest]

If what you've got now doesn't work, try
(I changed syntax on table names)

SELECT a.emplid, b.last_name, b.first_name, a.annual_rt INTO
ActiveEmplyeeAnnualRate
FROM hrwpv_ps_job AS a LEFT JOIN hrwpv_ps_names AS b ON a.emplid = b.emplid
WHERE (((a.emplid)>'000000001') AND ((a.empl_status) In ('A','P','L')) AND
((a.company)<'90') AND ((a.effdt)=(select max(aa.effdt)
from hrwpv_ps_job aa
where a.emplid = aa.emplid)) AND ((a.effseq)=(select max(aaa.effseq)
from hrwpv_ps_job aaa
where a.emplid = aaa.emplid
and a.effdt = aaa.effdt)));

 

[Guest]

Thanks, I'll try that now and let you know.

 

[Guest]

Bruce,

I get the following error:

ODBC--CALL FAILED.

[IBM][CLI Driver][DB2] SQL0104N An unexpected token
"TBLACTIVEEMPLYEEANNUALRATE" was found following"". Expected tokens may
include: ":". SQLSTATE=4260 1 (#-104)

 

[Guest]

still not working

 

[Guest]

IT WORKED! I had to change the query to a data definition query

 

[Guest]

Thank you very much Bruce for your assistance.