Home » SQL & PL/SQL » SQL & PL/SQL » Sequence generation help (Oracle 9i)
Sequence generation help [message #443478] Tue, 16 February 2010 00:42 Go to next message
manjukmoorthi
Messages: 20
Registered: February 2010
Junior Member
I have a query,

insert into tidlggls(BLT_KEY,BASE_LANGUAGE_TERM,MAX_TERM_SIZE,REPOSITORY_KEY)(
select BLT_KEY_SEQ.nextval,gls.BASE_LANGUAGE_TERM,gls.MAX_TERM_SIZE,gls.REPOSITORY_KEYfrom(
select MIN(BASE_LANGUAGE_TERM),MIN(MAX_TERM_SIZE),MIN(REPOSITORY_KEY) FROM TIDLRREP WHERE UPPER(BASE_LANGUAGE_TERM) NOT IN (SELECT UPPER(BASE_LANGUAGE_TERM) FROM TIDLGGLS)AND MULTIPLE_ROW_ENTRY ='N' AND REPOSITORY_STATUS = '00' GROUP BY UPPER(BASE_LANGUAGE_TERM)
)gls)


when executed, throws gls.REPOSITORY_KEY as invalid identifier though I have that column defined in tidlggls table.
I also tried adding identifier for inner subquery that has the group by function. Still it throws the same error

Please help.
Re: Sequence generation help [message #443482 is a reply to message #443478] Tue, 16 February 2010 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you correctly format you query maybe you can see the error.
Trust Oracle if it says it does not see this column then it does not see it.

If you don't know how to format a query, use a SQL Formatter.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.


Regards
Michel
Re: Sequence generation help [message #443486 is a reply to message #443482] Tue, 16 February 2010 01:00 Go to previous messageGo to next message
manjukmoorthi
Messages: 20
Registered: February 2010
Junior Member
I am sorry as I am new to the forum..

I am pasting the formatted SQL here.

INSERT INTO tidlggls 
           (blt_key, 
            base_language_term, 
            max_term_size, 
            repository_key) 
(SELECT blt_key_seq.nextval, 
        gls.base_language_term, 
        gls.max_term_size, 
        gls.repository_key 
 FROM   (SELECT   Min(base_language_term), 
                  Min(max_term_size), 
                  Min(repository_key) 
         FROM     tidlrrep 
         WHERE    Upper(base_language_term) NOT IN (SELECT Upper(base_language_term) 
                                                    FROM   tidlggls) 
                  AND multiple_row_entry = 'N' 
                  AND repository_status = '00' 
         GROUP BY Upper(base_language_term)) gls) 


I dont really find any issues still .. Can you check if the query used in correct?
Re: Sequence generation help [message #443490 is a reply to message #443486] Tue, 16 February 2010 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no returned field named "repository_key" in "gls" subquery.
If "Min(repository_key)" is what you want then add an alias to this field.

Regards
Michel
Re: Sequence generation help [message #443510 is a reply to message #443490] Tue, 16 February 2010 01:51 Go to previous message
manjukmoorthi
Messages: 20
Registered: February 2010
Junior Member
It works..
Thanks for the help..
Previous Topic: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i
Next Topic: Find out Object name
Goto Forum:
  


Current Time: Sun Nov 03 08:14:22 CST 2024