INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Table or View does not exist

Table or View does not exist

(OP)
Hello! I'm newly back to writing SQL code, and I don't have a lot of experience writing queries using explicit join syntax. Here's my code:

CODE --> sql

SELECT
(
select sum(y.ded_cur)   
from    ps_pay_check x 
			INNER JOIN ps_pay_deduction y ON (x.company = y.company AND x.paygroup = y.paygroup AND x.pay_end_dt = y.pay_end_dt
				AND x.off_cycle = y.off_cycle AND x.page_nbr = y.page_nbr AND x.line_nbr = y.line_nbr AND x.sepchk = y.sepchk)
			INNER JOIN ps_pay_calendar z ON (x.company = z.company AND x.paygroup = z.paygroup AND x.pay_end_dt = z.pay_end_dt)
			LEFT JOIN gd ON (x.emplid = gd.EMPLID AND y.dedcd = gd.DEDCD)
WHERE z.calendar_year =  to_char(sysdate,'YYYY') AND
		x.pay_end_dt <= SYSDATE AND
		x.paycheck_status = 'F'
) AS YTD_DED_AMT,

gd.DED_ADDL_AMT,gd.DEDCD, gd.DEDUCTION_END_DT, gd.EFFDT, gd.EMPLID, gd.GOAL_AMT, gd.GOAL_BAL,
 gd.GOAL_LIMIT_NBR, gd.GOAL_START_DT,  ec.COMPANY,  ec.EMPL_STATUS,  ec.NAME, ec.EMAIL_ADDRESS, 
ec.COMPANY_NAME, ec.MAIL_DROP, ec.EMPL_CLASS, ec.EMPL_CLASS_DESC, ec.FULL_PART_TIME,
dp.DM_PROV_DESCR, ermc.EFF_STATUS as ERM_DED_STATUS, dp.FEDERAL_EIN, pv.STREET1, pv.STREET2, pv.CITY, pv.STATE, pv.ZIP,
ermc.DTTM_STAMP as  ENTRY_DATE, 
ermc.DM_ONE_TIME as ERM_ONE_TIME_DED

FROM

PS_DM_DED_PROV dp INNER JOIN PS_PROVIDR_TBL pv ON dp.dedcd = pv.provider 
	LEFT OUTER JOIN PS_GENL_DEDUCTION gd ON pv.provider = gd.dedcd
	LEFT OUTER JOIN PS_ERM_CHARITY_EE ermc ON gd.emplid = ermc.emplid
	LEFT OUTER JOIN PS_DM_EMPL_CURR_VW ec ON ermc.emplid = ec.emplid

WHERE
((

((gd.DEDCD LIKE 'C0%') OR
  (gd.DEDCD LIKE 'C1%') OR
  (gd.DEDCD LIKE 'C2%') OR
  (gd.DEDCD LIKE 'C3%') OR
  (gd.DEDCD LIKE 'C4%') OR
  (gd.DEDCD LIKE 'C5%') OR
  (gd.DEDCD LIKE 'C6%') OR
  (gd.DEDCD LIKE 'C7%') OR
  (gd.DEDCD LIKE 'C8%') OR
  (gd.DEDCD LIKE 'C9%'))   AND

((gd.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_GENL_DEDUCTION INNERALIAS 
    WHERE INNERALIAS.DEDCD = gd.DEDCD
    AND INNERALIAS.EMPLID = gd.EMPLID
    AND INNERALIAS.EFFDT <= SYSDATE))) AND
((dp.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_DM_DED_PROV INNERALIAS 
    WHERE INNERALIAS.DEDCD = dp.DEDCD
    AND INNERALIAS.EFFDT <= SYSDATE))) AND
((ermc.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM  PS_ERM_CHARITY_EE INNERALIAS 
    WHERE INNERALIAS.DEDCD = ermc.DEDCD
    AND INNERALIAS.EMPLID = ermc.EMPLID
    AND INNERALIAS.EFFDT <= SYSDATE)))  AND
((pv.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_PROVIDR_TBL INNERALIAS 
    WHERE INNERALIAS.PROVIDER = pv.PROVIDER
    AND INNERALIAS.EFFDT <= SYSDATE))) 
)) 

ORDER BY gd.DEDCD 

The error I'm getting is ORA-00942: table or view does not exist. I'm entering this as a sql command in Crystal Reports XI against an Oracle 9i DB. I've also tried the depricated Oracle syntax, and while that would run, it wasn't executing the outer joins properly. I have my theories about what the issue might be related to, but I'd rather let some of you take a look first.

Thanks!!

RE: Table or View does not exist

The most obvious one is to check that the user in question has select privilege on all the tables involved in the query.

Regards

T

RE: Table or View does not exist

(OP)
Thanks T, but yes...I do know that it's actually a problem with the code and not something related to the infrastructure, permissions, or anything else along those lines. The following query works fine:

CODE --> sql

SELECT
(select sum(y.ded_cur)   
from    ps_pay_check x,
           ps_pay_deduction y,
           ps_pay_calendar z
where x.company      = y.company
and     x.paygroup     = y.paygroup
and     x.pay_end_dt = y.pay_end_dt
and     x.off_cycle     = y.off_cycle
and     x.page_nbr     = y.page_nbr
and     x.line_nbr       = y.line_nbr
and     x.sepchk        = y.sepchk
and     x.company     = z.company
and     x.paygroup     = z.paygroup
and     x.pay_end_dt = z.pay_end_dt
and     x.emplid         = gd.EMPLID
and     y.dedcd         = gd.DEDCD 
and     z.calendar_year =  to_char(sysdate,'YYYY') 
and     x.pay_end_dt <= SYSDATE
and     x.paycheck_status = 'F'
)  AS YTD_DED_AMT,

gd.DED_ADDL_AMT,gd.DEDCD, gd.DEDUCTION_END_DT, gd.EFFDT, gd.EMPLID, gd.GOAL_AMT, gd.GOAL_BAL,
 gd.GOAL_LIMIT_NBR, gd.GOAL_START_DT,  ec.COMPANY,  ec.EMPL_STATUS,  ec.NAME, ec.EMAIL_ADDRESS, 
ec.COMPANY_NAME, ec.MAIL_DROP, ec.EMPL_CLASS, ec.EMPL_CLASS_DESC, ec.FULL_PART_TIME,
dp.DM_PROV_DESCR, ermc.EFF_STATUS as ERM_DED_STATUS, dp.FEDERAL_EIN, pv.STREET1, pv.STREET2, pv.CITY, pv.STATE, pv.ZIP,
ermc.DTTM_STAMP as  ENTRY_DATE, 
ermc.DM_ONE_TIME as ERM_ONE_TIME_DED

FROM
PS_GENL_DEDUCTION gd, PS_DM_EMPL_CURR_VW ec,  
PS_DM_DED_PROV dp, PS_ERM_CHARITY_EE ermc, PS_PROVIDR_TBL pv

WHERE
((

((gd.DEDCD LIKE 'C0%') OR
  (gd.DEDCD LIKE 'C1%') OR
  (gd.DEDCD LIKE 'C2%') OR
  (gd.DEDCD LIKE 'C3%') OR
  (gd.DEDCD LIKE 'C4%') OR
  (gd.DEDCD LIKE 'C5%') OR
  (gd.DEDCD LIKE 'C6%') OR
  (gd.DEDCD LIKE 'C7%') OR
  (gd.DEDCD LIKE 'C8%') OR
  (gd.DEDCD LIKE 'C9%'))   AND

((gd.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_GENL_DEDUCTION INNERALIAS 
    WHERE INNERALIAS.DEDCD = gd.DEDCD
    AND INNERALIAS.EMPLID = gd.EMPLID
    AND INNERALIAS.EFFDT <= SYSDATE))) AND
((dp.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_DM_DED_PROV INNERALIAS 
    WHERE INNERALIAS.DEDCD = dp.DEDCD
    AND INNERALIAS.EFFDT <= SYSDATE))) AND
((ermc.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM  PS_ERM_CHARITY_EE INNERALIAS 
    WHERE INNERALIAS.DEDCD = ermc.DEDCD
    AND INNERALIAS.EMPLID = ermc.EMPLID
    AND INNERALIAS.EFFDT <= SYSDATE)))  AND
((pv.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_PROVIDR_TBL INNERALIAS 
    WHERE INNERALIAS.PROVIDER = pv.PROVIDER
    AND INNERALIAS.EFFDT <= SYSDATE))) 
)) 
AND
(dp.dedcd = pv.provider) AND
(pv.provider = gd.dedcd) AND
(gd.emplid = ermc.emplid) AND
(gd.dedcd = ermc.dedcd) AND
(ermc.emplid = ec.emplid)
ORDER BY
gd.DEDCD 

As you'll notice, the only difference is that this version uses inner joins. The client wants the report to include entities that have no records in some of the tables, hence my effort to convert it to outer joins where necessary.

RE: Table or View does not exist

Dave,

Can you post create table statements and insert statements so I can try to reproduce your issue?

If not, then I suggest a divide and conquer approach. The second query has no join conditions in the from clause, so I'm guessing you're getting lots of unexpected records returned.
I suggest that you restrict the select clause to only use values from one table and comment the others out.
Run the query and make sure it returns what you expect. Then join to a second table in the from clause, and uncomment the values from that table.
Check to see that it returns what you expect.
Rinse and repeat until you find the offending table.

Is that making sense?

Regards

T

RE: Table or View does not exist

(OP)
No...the second query is returning exactly what I'd expect - the joins are handled implicitly in the WHERE clause. The problem is that the second query is not returning rows where there are non-matching rows in some tables, hence the change to outer joins.

I found the cause of the initial error, but I'm still having issues. The bad table name error was caused by this table reference from the first sub-query:

CODE --> sql

LEFT JOIN gd ON (x.emplid = gd.EMPLID AND y.dedcd = gd.DEDCD) 

I didn't realize that alias references like this wouldn't work properly in explicit join syntax. I put in the table name, and that rid me of the original error. I was also getting error in the numbers that that sub-query was meant to pull, so I went back to the original code for that bit, leaving me with this:

CODE --> sql

SELECT
(select sum(y.ded_cur)   
from    ps_pay_check x,
           ps_pay_deduction y,
           ps_pay_calendar z
where x.company      = y.company
and     x.paygroup     = y.paygroup
and     x.pay_end_dt = y.pay_end_dt
and     x.off_cycle     = y.off_cycle
and     x.page_nbr     = y.page_nbr
and     x.line_nbr       = y.line_nbr
and     x.sepchk        = y.sepchk
and     x.company     = z.company
and     x.paygroup     = z.paygroup
and     x.pay_end_dt = z.pay_end_dt
and     x.emplid         = gd.EMPLID
and     y.dedcd         = gd.DEDCD 
and     z.calendar_year =  to_char(sysdate,'YYYY') 
and     x.pay_end_dt <= SYSDATE
and     x.paycheck_status = 'F'
)  AS YTD_DED_AMT,

gd.DED_ADDL_AMT,gd.DEDCD, gd.DEDUCTION_END_DT, gd.EFFDT, gd.EMPLID, gd.GOAL_AMT, gd.GOAL_BAL,
 gd.GOAL_LIMIT_NBR, gd.GOAL_START_DT,  ec.COMPANY,  ec.EMPL_STATUS,  ec.NAME, ec.EMAIL_ADDRESS, 
ec.COMPANY_NAME, ec.MAIL_DROP, ec.EMPL_CLASS, ec.EMPL_CLASS_DESC, ec.FULL_PART_TIME,
dp.DM_PROV_DESCR, ermc.EFF_STATUS as ERM_DED_STATUS, dp.FEDERAL_EIN, pv.STREET1, pv.STREET2, pv.CITY, pv.STATE, pv.ZIP,
ermc.DTTM_STAMP as  ENTRY_DATE, 
ermc.DM_ONE_TIME as ERM_ONE_TIME_DED

FROM

PS_DM_DED_PROV dp INNER JOIN PS_PROVIDR_TBL pv ON dp.dedcd = pv.provider 
	LEFT OUTER JOIN PS_GENL_DEDUCTION gd ON pv.provider = gd.dedcd
	LEFT OUTER JOIN PS_ERM_CHARITY_EE ermc ON (gd.emplid = ermc.emplid AND gd.dedcd = ermc.dedcd)
	LEFT OUTER JOIN PS_DM_EMPL_CURR_VW ec ON ermc.emplid = ec.emplid

WHERE
((

((gd.DEDCD LIKE 'C0%') OR
  (gd.DEDCD LIKE 'C1%') OR
  (gd.DEDCD LIKE 'C2%') OR
  (gd.DEDCD LIKE 'C3%') OR
  (gd.DEDCD LIKE 'C4%') OR
  (gd.DEDCD LIKE 'C5%') OR
  (gd.DEDCD LIKE 'C6%') OR
  (gd.DEDCD LIKE 'C7%') OR
  (gd.DEDCD LIKE 'C8%') OR
  (gd.DEDCD LIKE 'C9%'))   AND

((gd.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_GENL_DEDUCTION INNERALIAS 
    WHERE INNERALIAS.DEDCD = gd.DEDCD
    AND INNERALIAS.EMPLID = gd.EMPLID
    AND INNERALIAS.EFFDT <= SYSDATE))) AND
((dp.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_DM_DED_PROV INNERALIAS 
    WHERE INNERALIAS.DEDCD = dp.DEDCD
    AND INNERALIAS.EFFDT <= SYSDATE))) AND
((ermc.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM  PS_ERM_CHARITY_EE INNERALIAS 
    WHERE INNERALIAS.DEDCD = ermc.DEDCD
    AND INNERALIAS.EMPLID = ermc.EMPLID
    AND INNERALIAS.EFFDT <= SYSDATE)))  AND
((pv.EFFDT = ( 
    SELECT MAX(INNERALIAS.EFFDT)
    FROM PS_PROVIDR_TBL INNERALIAS 
    WHERE INNERALIAS.PROVIDER = pv.PROVIDER
    AND INNERALIAS.EFFDT <= SYSDATE))) 
)) 

ORDER BY gd.DEDCD 

Now, I'm getting the proper numbers, but the joins are being executed as inner joins. Here are my theories as to what could be causing this:

1) The links to the gd.emplid and gd.dedcd fields in the top sub-query
2) Two of the four sub-queries at the bottom link to tables that are part of the outer joins.

Could either of these cause the joins to revert to inner joins? Any thoughts on how I'd revise the code to avoid that?

RE: Table or View does not exist

(OP)
Well, yes...thanks. That's what I thought. The problem I'm having is that I've been unable to find a way to rework the code to avoid that issue. When I tried to move the subqueries up into the joins, I get an error telling me that I can't use a subquery in an outer join. I've seen posts elsewhere that mention using a UNION, but none give examples that work with a complex query like mine.

Do you have any thoughts on how I would fix the problem?

RE: Table or View does not exist

(OP)
You're right, the first bit (looking for the dedcd's that start with 'Cx' could be replaced with another table. The rest, though - the ones that are looking for the max dates - cannot. Those are my real concern - I'm looking for a way to include those in the joins somehow, or perhaps use a UNIION.

RE: Table or View does not exist

(OP)
Thanks...I see where you're going, and I'll give it a try.

Also...do you think the top sub-query would have the same effect of knocking the whole thing back to inner joins?

RE: Table or View does not exist

I see where you're going, and I'll give it a try
So, where did YOU go ?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close