Strange Problem 1

[engineer2100]

Hi,

I have query inside a package which takes a form as below

select x,
       y,
       (SELECT
               a, b
        From P inner join
          (select c,d from Q where q.k = a.k) xx
        ON P.C = xx.C) z,
    t,
    u
from
  a 
....

Now this code works fine on our local dev database but fails on client database with an "invalid Identifier" on "a.k".

What seems to be the problem? I like spent 1 hr breaking my head over the issue, but in vain.

Thanks
Engi

 

[Dagon]

I assume you've checked that table definitions match exactly between the local dev database and the client database ?

 

[engineer2100]

They match 100%. The sanity checks were made prior to posting here!!! :-(

 

[Welshbird]

And tables are owned by the same owner?

(I did the same once, and realised that I had to prefix the table name on the other server).

Probably not the issue, but thought anything worth a try.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[engineer2100]

Same owner; infact running from the same owner on both the databases

Now I made a function and calling the function instead of the inline view passing it (from the above code snippet) "a.k"

 

[engineer2100]

Some more information thought mentioning

Column k in Table a is FKey referring to Column k of a Table in a separate schema i.e I have inter schema referntial integrity here with the parent table in a different schema than table 'a'.

 

[Welshbird]

I think I would be tempted to go back a step - how is the second database created? Is it a complete copy of the first? From a script?

It just seems like there MUST be some difference, so maybe beginning from how it was created would allow you to find the issue.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

The only other thing that I can think of is that it might be an access problem. If they are in different schemas and the access of one user to the other user's tables is through a role rather than a direct grant, you may have problems seeing the other user's tables in a stored proc. You will be able to see them through SQL*Plus but stored procedures could have problems.

 

[engineer2100]

Dagon, I get the issue even when I run directly and not through the Package.

 

[Dagon]

It could still be a privilege issue if you don't have access to all the tables and columns in the other user's schema.

 

[engineer2100]

Dagon,

(Weeping!!) The other schema has only one table and that is this one. And I checked it out.

 

[Dagon]

Is there any difference in the versions of the databases ?

 

[engineer2100]

Yeah one is on Windows and the other is on Unix.

 

[Dagon]

What are the actual Oracle versions (8, 9i, 10g) ?

 

[engineer2100]

10gR2 both ends and the query fails on Unix box.

Now the query also fails on another local database on Windows too (no kidding!)

 

[Dagon]

I get the same problem on my system:

create table p (a number, b number, c number)
  
create table q (k number, c number, d number)
  
create table a (k number, t number, u number, x number, y number);

select x,
       y,
       (SELECT
               a, b
        From P inner join
          (select c,d from Q where q.k = a.k) xx
        ON P.C = xx.C) z,
    t,
    u
from
  a 

ORA-00904: "A"."K": invalid identifier

It looks as if you can can't refer to a column from the outer query within a subquery of the online select. This may be different in 10g - I only have 9i at the moment. If I rewrite it as:

select x,
       y,
       (SELECT
               a, b
        From P inner join Q 
        ON P.C = q.C where q.k = a.k) z,
    t,
    u
from
  a

it still doesn't work because I'm trying to select two columns in the inline select. However:

select x,
       y,
       (SELECT
               a
        From P inner join Q 
        ON P.C = q.C where q.k = a.k) z,
    t,
    u
from
  a

does work. Check that you really are running the same code on both systems and that it really does work.

 

[engineer2100]

Dagon,

I will try to see what you suggested.

Actually the inner most query uses a aggregate function based on the which the immediate outer query fetches a value.

Thanks for the help. I will update you once does. For now I have asked my folks to create a function that would return the required value and the query works.

Thanks
Engi

 

[engineer2100]

on second thoughts how come the query is working fine on one database. It should behave the same everywhere.. right?

 

[Dagon]

If the query is exactly like that, I would not expect it to work anywhere. You can't bring back two columns in one inline select.

 

[engineer2100]

Here is the query

Select
....
contact_flag,
                          (SELECT v_risk.name
                                       FROM dealer_hist dlr_risk
                                            INNER JOIN
                                            (SELECT   hist.dlr_org_id,
                                                      MAX
                                                         (hist.seq_num
                                                         )
                                                         AS max_seq_num
                                                 FROM dealer_hist hist
                                                WHERE hist.dlr_org_id = ap.dlr_org_id
                                             GROUP BY hist.dlr_org_id) dlr_shp
                                            ON dlr_risk.dlr_org_id =
                                                         dlr_shp.dlr_org_id
                                          AND dlr_risk.seq_num =
                                                           dlr_shp.max_seq_num
                                            INNER JOIN v_dealer_risk_level v_risk
                                            ON v_risk.dlr_risk_lvl_id =
                                                  dlr_risk.dr_risk_lvl_lkup_id
                                            ) dlrrisk, v_app_status.name statdesc,
                                            ..
                                            ..
                                            ..                                            
from
application ap INNER JOIN .....
..
..
..

And this does work on one DB and doesnot on the other. Here are DB details

Database where things Don't work

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Database where things work

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE	10.1.0.2.0	Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production