Oracle correlated subqueries-CodePudding

Table 2 (product propaganda chart and table), according to the product to find the main propaganda figure,
SQL:
SELECT p. *, (the SELECT INNER_TAB usepic FROM (
SELECT the SAP. Attpic_name usepic, ROWNUM RN_
The FROM T_CLSH_ATTPIC SAP
WHERE SAP. Prod_id=p. prod_id
AND SAP. Is_use='1'
AND SAP. Is_del='0'
The ORDER BY
SAP. ATTPIC_ID DESC
) INNER_TAB
WHERE
INNER_TAB. RN_=1
) usepic
The FROM
T_CLSH_PROD P
In the local environment Windows oracle 10 g to be able to run normally,
On Linux oracle11G execution error.
Error: [Err] ORA - 00904: "P". "PROD_ID" : invalid identifier,
Baidu under some, subqueries wear alias layer too deep in the main table, or said oracle11g does not support,
Don't know how to solve this problem, to do the configuration or only by changing the SQL (project involves written as there are many)
Consult everybody!

CodePudding user response:

Does not support double nested relation, think other ways

CodePudding user response:

I also just saw oracle official documentation, horse, it was 11 g does not support double nesting child query,

CodePudding user response:

This writing does not support, can you can put a in the subquery T_CLSH_PROD table, or in other ways

CodePudding user response:

I use Max instead of rownum=1 this kind of situation, reduce the straton query
SELECT P. * (SELECT Max (SAP) attpic_name) usepic
The FROM T_CLSH_ATTPIC SAP
WHERE SAP. Prod_id=p. prod_id
AND SAP. Is_use='1'
AND SAP. Is_del='0') usepic
The FROM
T_CLSH_PROD P

CodePudding user response:

reference 4 floor aibrooks response:

I use Max instead of rownum=1 this kind of situation, reduce the straton query
SELECT P. * (SELECT Max (SAP) attpic_name) usepic
The FROM T_CLSH_ATTPIC SAP
WHERE SAP. Prod_id=p. prod_id
AND SAP. Is_use='1'
AND SAP. Is_del='0') usepic
The FROM
T_CLSH_PROD P

Has additional overhead to the database, query efficiency is low, is not recommended

CodePudding user response:

Can use a temporary table

CodePudding user response:

I tried once, why can I run? Is the HP - ux server, select 11 g version, two layer can sure

reference 1st floor js14982 response:

does not support double nested relation, think other way

CodePudding user response:

reference 1st floor js14982 response:

does not support double nested relation, think other way

my three layers of support, and 11 g ah, don't know why

 SELECT p. *, 
(select * 
The from (SELECT INNER_TAB. Created 
The FROM (SELECT tt. Created, ROWNUM RN_ FROM LHB. Test1 tt) INNER_TAB 
WHERE INNER_TAB. RN_=1) 
Where created & gt; Sysdate - 5) usepic 
The FROM LHB. Test P

CodePudding user response:

refer to the eighth floor hdg5211314 response:

Quote: refer to 1st floor js14982 response:

Does not support double nested relation, think other way

my three layers of support, and 11 g, don't know why
SELECT p. *,
(select *
The from (SELECT INNER_TAB. Created
The FROM (SELECT tt. Created, ROWNUM RN_ FROM LHB. Test1 tt) INNER_TAB
WHERE INNER_TAB. RN_=1)
Where created & gt; Sysdate - 5) usepic
The FROM LHB. Test P

This and the situation of the building Lord, you his field is embedded within the cover of the above query, you are the whole table