Create table from Pass thru query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)

;
 
I see three fields in your CREATE TABLE statement and four fields in your
SELECT...INTO statement
 
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)));
 
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)
 
Back
Top