Failure 3802 Database does not exist.

[cibersjk]

I'm a long-time (>17yr) Oracle developer/manager who's a total newbie to TD. I've got a question/problem...

I'm doing a query that works in Queryman (a pretty neat tool!), but does not work in a UNIX script. I've got the env. vars set up OK (I think!), but the query returns the dreaded "Failure 3802". The frippin' thing works if we take out the highlighted correlated subquery, but of course the answer set is wrong...

Here's the query text...

select n.bill_acct_id      ,o.bill_acct_id
      ,n.wrls_svc_id       ,n.process_dt
      ,n.process_end_dt    ,o.process_dt
      ,o.process_end_dt    ,wsh.sts_name
      ,wsh.sts_eff_dt      ,wsh.process_dt
      ,wsh.process_end_dt
 from ${O23GV}.av_bill_sbscrp_hst     n
     ,${O23GV}.av_bill_sbscrp_hst o
     ,${O23GV}.av_wrls_svc_hst    wsh
where n.process_dt = '2003-10-21'
  and o.wrls_svc_id = n.wrls_svc_id
  and o.bill_acct_id <> n.bill_acct_id
  and o.process_dt=[COLOR=red](select max(x.process_dt)
                      from ${O23GV}.av_bill_sbscrp_hst x
                     where process_dt < '2003-10-21'
                       and x.wrls_svc_id=n.wrls_svc_id)[/color]
  and wsh.wrls_svc_id = n.wrls_svc_id
  and '2003-10-21' between wsh.process_dt 
                       and wsh.process_end_dt

Any (and all!) ideas (brilliant or otherwise!) would be appreciated

Thanks
SteveK

 

[tdatgod]

(select max(x.process_dt)
from ${O23GV}.av_bill_sbscrp_hst x
where process_dt < '2003-10-21'
and x.wrls_svc_id=n.wrls_svc_id

Is process_dt supposed to be qualified or is that just a typo in your post?

where process_dt < '2003-10-21'

to

where x.process_dt < '2003-10-21'
^^^^


Have you tried the subquery by itself from a script? Does it work by itself?

 

[cibersjk]

I found the answer!

Apparently, you have to be v e r y careful in the use of column name alises in a correlated subquery. If you use the alias names instead of the tabname.colname format, it works fine...

Whew! Thanks for all your help!