CODEWITHSUNDEEP
sqloracle-databaseplsql
Emrahall
Emrahall

Reputation: 81

ORA-01007: variable not in select list ORA-06512: at line 32

I'm trying to select multiple queries with group by but I get the following error.

ORA-01007: variable not in select list
ORA-06512: at line 32
01007. 00000 -  "variable not in select list"

can you help me please 

declare
 X number:=0;
     v_partition  varchar(20);
     type detail_rt is record(
     message_ıd varchar2(100),                   
     phone_no number,                 
     send_phone_no varchar2(150),            
     start_date DATE,               
     end_date DATE,                 
     status number,                   
     dcode number,                    
     DELIVERED_DATE date,           
     DCODE_DESC varchar2(250),
     sms_count number,                
     sms_message varchar2(1850),              
     CDR_MSISDN number,
     sayi number
     );
type detail_aat is table of detail_rt index by pls_integer;
     detail_aatt  detail_aat;
type l_cursor is ref cursor;
     c l_cursor;
BEGIN
     <<GET_Partition>>
     select 'P_'||to_char(trunc(sysdate-X),'YYYYMMDD') into v_partition from dual;
     open c for 
     'SELECT count(*) as sayi,
      C.CDR_MSISDN as CDR_MSISDN
FROM
    corp_smsdetaıl partition('|| v_partition||') A   INNER JOIN CORP_COMPANY C ON A.COMPANY_ID=C.ID  
                       INNER JOIN  CORP_DCODE_DESC B ON A.DCODE=B.DCODE where a.company_id=17790 group by c.CDR_MSISDN';
fetch c bulk collect into detail_aatt;            
close c;
for indx in 1 .. detail_aatt.count

loop
    DBMS_OUTPUT.PUT_LINE (detail_aatt(indx).sayi || '||' || detail_aatt(indx).CDR_MSISDN);
 end loop;
    X:=X+1;
    IF x < 2 THEN
    GOTO GET_Partition;
END IF; 
 end;

Upvotes: 0

Views: 948

Answers (2)

Emrahall
Emrahall

Reputation: 81

declare
     X number:=0;
     v_partition  varchar(20);
     type detail_rt is record(                  
     COMPANY_ID number,     
     sayi number
     );
type detail_aat is table of detail_rt index by pls_integer;
     detail_aatt  detail_aat;
type l_cursor is ref cursor;
     c l_cursor;
BEGIN
     <<GET_Partition>>
     select 'P_'||to_char(trunc(sysdate+X-76),'YYYYMMDD') into v_partition from dual;
     open c for 
     'SELECT
      A.COMPANY_ID,
      count(*) as sayi

FROM
    corpsms_arch_user.corp_smsdetaılold partition('|| v_partition||')  A 
                        where A.company_id in(13396,15844,17309,20197) group by 

                       A.COMPANY_ID';
fetch c bulk collect into detail_aatt;            
close c;
for indx in 1 .. detail_aatt.count

loop
    DBMS_OUTPUT.PUT_LINE (detail_aatt(indx).COMPANY_ID|| '||' ||detail_aatt(indx).sayi);
 end loop;
    X:=X+1;
    IF x < 76 THEN
    GOTO GET_Partition;
END IF; 
 end;
 /

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142710

This should be line 32 (according to code you posted).

fetch c bulk collect into detail_aatt;

However, it appears that true error line is this:

DBMS_OUTPUT.PUT_LINE (detail_aatt(indx).sayi || '||' || detail_aatt(indx).CDR_MSISDN);

How come?

  • you are selecting SAYI and CDR_MSISDN
  • putting them into detail_aatt
  • whose columns are MESSAGE_ID, PHONE_NO, SEND_PHONE_NO, etc.

Therefore, use

dbms_output.put_line(detail_aatt(indx).message_id ||', '|| detail_aatt(indx).phone_no);

instead.

Upvotes: 2

Related Questions