CODEWITHSUNDEEP
sqloracle-database
Kaskade
Kaskade

Reputation: 795

SQL select and case combined

I am having trouble with a select statement and not sure what I am doing wrong. This is the structure of the source table:

Source Schema DATA1

Source Table FOLDERS

Source Columns FOLDERID, USERID

Destination Schema DATA1

Destination Table FOLDER_USER

Destination Columns FOLDER_ID, USER_ID

And here is the query ( I need to change the folder ID as it is being pulled out of the first query hence the case):

DECLARE
BEGIN
   FOR FOLDER_ROW IN (SELECT FOLDERID = CASE 
                                WHEN FOLDERID = '10' THEN '1'
                                WHEN FOLDERID = '565' THEN '2'
                                WHEN FOLDERID = '11' THEN '3'
                                WHEN FOLDERID = '81' THEN '4'
                                ELSE '0'
                            END, USERID FROM DATA1.FOLDERS WHERE UPPER(OWNER) = 'ADMIN')
      LOOP                       
          INSERT INTO DATA1.FOLDER_USER (FOLDER_ID, CORP_ID) VALUES (FOLDER_ROW.FOLDERID, FOLDER_ROW.CORPID);
     END LOOP;
    COMMIT;
END;

I get the following error when I execute and I'm stuck as to why I am getting it. I know it has something to do with the case inside the select:

ORA-06550: line 3, column 39:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 3, column 22:
PL/SQL: SQL Statement ignored

Upvotes: 2

Views: 456

Answers (4)

Mike Meyers
Mike Meyers

Reputation: 2895

I'm not sure if you have simplified your code or not before posting the question, but I would look at getting rid of the CURSOR FOR loop completely and just using and INSERT statement with a sub-query.

I think CURSOR FOR loops are pretty well optimized by Oracle so it may not run any faster but might be easier to understand.

BEGIN
  INSERT INTO DATA1.FOLDER_USER (FOLDER_ID, CORP_ID) 
  SELECT 
    CASE 
      WHEN FOLDERID = '10' THEN '1'
      WHEN FOLDERID = '565' THEN '2'
      WHEN FOLDERID = '11' THEN '3'
      WHEN FOLDERID = '81' THEN '4'
      ELSE '0'
    END folder_id, 
    USERID 
  FROM DATA1.FOLDERS 
  WHERE UPPER(OWNER) = 'ADMIN';

  COMMIT;
END;

Upvotes: 1

Kevin Burton
Kevin Burton

Reputation: 11936

You could also rewrite it as a decode (it is less portable and harder to read though)

DECLARE 
BEGIN    
    FOR folder_row IN (SELECT DECODE(folderid,'10', '1','565','2','11', '3','81', '4','0') AS folderid , 
                              userid 
                       FROM data1.folders 
                       WHERE UPPER(owner) = 'ADMIN')       
    LOOP                                  
        INSERT INTO data1.folder_user (folder_id, corp_id) VALUES (folder_row.folderid, folder_row.corpid);      
    END LOOP;     
    COMMIT; 
END;

see: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions042.htm

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

It looks like you just want

BEGIN
   FOR FOLDER_ROW IN (SELECT CASE 
                                WHEN FOLDERID = '10' THEN '1'
                                WHEN FOLDERID = '565' THEN '2'
                                WHEN FOLDERID = '11' THEN '3'
                                WHEN FOLDERID = '81' THEN '4'
                                ELSE '0'
                            END folder_id, 
                            USERID 
                       FROM DATA1.FOLDERS 
                     WHERE UPPER(OWNER) = 'ADMIN')
      LOOP                       
          INSERT INTO DATA1.FOLDER_USER (FOLDER_ID, CORP_ID) VALUES (FOLDER_ROW.FOLDER_ID, FOLDER_ROW.CORPID);
     END LOOP;
    COMMIT;
END;

I added an alias of FOLDER_ID to the CASE statement and eliminated the FOLDERID = bit from the CASE

Upvotes: 3

Mark Sherretta
Mark Sherretta

Reputation: 10230

SELECT FOLDERID = CASE is wrong. It should be SELECT CASE ..... END As FOLDERID

Upvotes: 3

Related Questions