Pass-through query problems

[dan626mcl2]

So...I'm trying to run a pass-through query against a couple of Oracle tables and I'm not very familiar with the process. I've done this type of query against Teradata, just not Oracle. So I'm hoping someone out there may see the problem in this query.

Generally, I've been getting two errors:
1) invlaid field name
2) SQL not properly ended.

Suggestions???

SELECT
a.emplid,
SYSADM.PS_JOB.EMPL_RCD,
SYSADM.PS_JOB.EFFDT,
SYSADM.PS_JOB.EFFSEQ,
SYSADM.PS_JOB.DEPTID,
SYSADM.PS_JOB.JOBCODE,
SYSADM.PS_JOB.POSITION_NBR,
SYSADM.PS_JOB.POSN_CHANGE_RECORD,
SYSADM.PS_JOB.EMPL_STATUS,
SYSADM.PS_JOB.DEPT_ENTRY_DT,
SYSADM.PS_JOB.POSITION_ENTRY_DT,
SYSADM.PS_JOB.REG_TEMP,
SYSADM.PS_JOB.FULL_PART_TIME,
SYSADM.PS_JOB.SHIFT,
SYSADM.PS_JOB.EMPL_TYPE,
SYSADM.PS_JOB.EMPL_CLASS,
SYSADM.PS_JOB.SAL_ADMIN_PLAN,
SYSADM.PS_JOB.GRADE,
SYSADM.PS_JOB.GRADE_ENTRY_DT,
SYSADM.PS_JOB.GL_PAY_TYPE,
SYSADM.PS_JOB.BENEFIT_PROGRAM,
SYSADM.PS_JOB.UNION_FULL_PART,
SYSADM.PS_JOB.UNION_POS,
SYSADM.PS_JOB.UNION_FEE_START_DT,
SYSADM.PS_JOB.UNION_FEE_END_DT,
SYSADM.PS_JOB.UNION_CD,
SYSADM.PS_JOB.UNION_SENIORITY_DT,
SYSADM.PS_JOB.LABOR_AGREEMENT,
SYSADM.PS_JOB.EMPL_CTG

FROM

(select
sysadm.ps_upc_opayded_vq.emplid

from
sysadm.ps_upc_opayded_vq

where
SYSADM.PS_UPC_OPAYDED_VQ.PAY_END_DT Between '01/JAN/2002' And '31/MAY/2003' AND
SYSADM.PS_UPC_OPAYDED_VQ.DEDCD='401K' AND
SYSADM.PS_UPC_OPAYDED_VQ.EMPLID= '001448704'

group by sysadm.ps_upc_opayded_vq.emplid) a

where
SYSADM.PS_JOB.EMPLID = a.emplid;

 

[Turkbear]

Hi,
Th efirst thing I notice is that you are asking for fields that are not there:
You hav ecreated a subquery and aliased it as 'a'..It ONLY has sysadm.ps_upc_opayded_vq.emplid
as a field - you do not have any other Select..From that would produce all the SYSADM.* fields you are requesting...Redesign is needed...

 

[JohnMeli]

I think that the following query is right

SELECT
a.emplid,
SYSADM.PS_JOB.EMPL_RCD,
SYSADM.PS_JOB.EFFDT,
SYSADM.PS_JOB.EFFSEQ,
SYSADM.PS_JOB.DEPTID,
SYSADM.PS_JOB.JOBCODE,
SYSADM.PS_JOB.POSITION_NBR,
SYSADM.PS_JOB.POSN_CHANGE_RECORD,
SYSADM.PS_JOB.EMPL_STATUS,
SYSADM.PS_JOB.DEPT_ENTRY_DT,
SYSADM.PS_JOB.POSITION_ENTRY_DT,
SYSADM.PS_JOB.REG_TEMP,
SYSADM.PS_JOB.FULL_PART_TIME,
SYSADM.PS_JOB.SHIFT,
SYSADM.PS_JOB.EMPL_TYPE,
SYSADM.PS_JOB.EMPL_CLASS,
SYSADM.PS_JOB.SAL_ADMIN_PLAN,
SYSADM.PS_JOB.GRADE,
SYSADM.PS_JOB.GRADE_ENTRY_DT,
SYSADM.PS_JOB.GL_PAY_TYPE,
SYSADM.PS_JOB.BENEFIT_PROGRAM,
SYSADM.PS_JOB.UNION_FULL_PART,
SYSADM.PS_JOB.UNION_POS,
SYSADM.PS_JOB.UNION_FEE_START_DT,
SYSADM.PS_JOB.UNION_FEE_END_DT,
SYSADM.PS_JOB.UNION_CD,
SYSADM.PS_JOB.UNION_SENIORITY_DT,
SYSADM.PS_JOB.LABOR_AGREEMENT,
SYSADM.PS_JOB.EMPL_CTG

FROM

(select
sysadm.ps_upc_opayded_vq.emplid

from
sysadm.ps_upc_opayded_vq

where
SYSADM.PS_UPC_OPAYDED_VQ.PAY_END_DT Between '01/JAN/2002' And '31/MAY/2003' AND
SYSADM.PS_UPC_OPAYDED_VQ.DEDCD='401K' AND
SYSADM.PS_UPC_OPAYDED_VQ.EMPLID= '001448704'

group by sysadm.ps_upc_opayded_vq.emplid) a, SYSADM.PS_JOB

where
SYSADM.PS_JOB.EMPLID = a.emplid;

The only thing that I added was the SYSADM.PS_JOB table in the from statement

John