CODEWITHSUNDEEP
oracle-databaseplsqlora-00917
Vishesh Aneja
Vishesh Aneja

Reputation: 21

i am getting an Error "Missing comma" at line 5

I am getting an error "missing comma at line 5" from this code:

declare
  AGG_COLUMNS VARCHAR2(2000);


begin
   AGG_COLUMNS := '(''S'' || chr(38) ||''P (LT Rating)'' as SP,''CreditSights CSR'' as CSR,''FITCH (LT Rating)'' as  Fitch,''Moody||''''''||''s (LT Rating or CFR)'' as MOODY,''DBRS (LT Rating)'' as DBRS )';




execute immediate'

CREATE Global TEMPORARY TABLE temp_extRating

ON COMMIT PRESERVE ROWS
AS

(select * from 
(
  select EXTRAT.Rating_ID  ,EXTRAT.AGENCY_NM  ,EXTRAT.EXT_RATING  ,EXTRAT.RBC_EQ_RATING  ,EXTRAT.EFFECTIVE_DATE 
    from RBC_P_EXT_RATING EXTRAT 
    join RBC_P_RATING Rat on 
  Rat.ID=EXTRAT.Rating_ID
  )

pivot (

  MIN(EXT_RATING) ER, 

  MIN(RBC_EQ_RATING) RER, 

  MIN(EFFECTIVE_DATE) ED

  FOR AGENCY_NM IN ('''||AGG_COLUMNS||''')

) 

order by Rating_ID) ';

end;

Why am I getting that error?

Upvotes: 0

Views: 308

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

If you displayed the dynamic SQL you were trying to run you'd end up with the statement:

...
  FOR AGENCY_NM IN ('('S' || chr(38) ||'P (LT Rating)' as SP,'CreditSights CSR' as CSR,'FITCH (LT Rating)' as  Fitch,'Moody||'''||'s (LT Rating or CFR)' as MOODY,'DBRS (LT Rating)' as DBRS )')
) 
order by Rating_ID)

and that IN clause is just malformed. Your agg_column construction has quote issues, around the concatenatin of chr(38) and the escaping of the two single quotes you do eventually want in Moody''s; it should be:

   AGG_COLUMNS := '''S' || chr(38) ||'P (LT Rating)'' as SP,''CreditSights CSR'' as CSR,''FITCH (LT Rating)'' as  Fitch,''Moody''''s (LT Rating or CFR)'' as MOODY,''DBRS (LT Rating)'' as DBRS';

You then add extra quotes, so this should be:

  FOR AGENCY_NM IN ('||AGG_COLUMNS||')

And the outermost set of parentheses around the whole select part should not be there.

declare
  AGG_COLUMNS VARCHAR2(2000);
begin
   AGG_COLUMNS := '''S' || chr(38) ||'P (LT Rating)'' as SP,''CreditSights CSR'' as CSR,''FITCH (LT Rating)'' as  Fitch,''Moody''''s (LT Rating or CFR)'' as MOODY,''DBRS (LT Rating)'' as DBRS';
   execute immediate '
CREATE Global TEMPORARY TABLE temp_extRating
ON COMMIT PRESERVE ROWS
AS
select * from 
(
  select EXTRAT.Rating_ID  ,EXTRAT.AGENCY_NM  ,EXTRAT.EXT_RATING  ,EXTRAT.RBC_EQ_RATING  ,EXTRAT.EFFECTIVE_DATE 
    from RBC_P_EXT_RATING EXTRAT 
    join RBC_P_RATING Rat on 
  Rat.ID=EXTRAT.Rating_ID
  )
pivot (
  MIN(EXT_RATING) ER, 
  MIN(RBC_EQ_RATING) RER, 
  MIN(EFFECTIVE_DATE) ED
  FOR AGENCY_NM IN ('||AGG_COLUMNS||')
) 
order by Rating_ID';
end;
/

I'm not sure why you have that as a separate variable, but it would be simpler to use the alternative quoting mechanism to reduce the single-quote mess.

   AGG_COLUMNS := q'['S]' || chr(38) || q'[P (LT Rating)' as SP,'CreditSights CSR' as CSR,'FITCH (LT Rating)' as  Fitch,'Moody''s (LT Rating or CFR)' as MOODY,'DBRS (LT Rating)' as DBRS]';

You can also use set define off in SQL*Plus or SQL Developer to avoid having to concatenate the ampersand as chr(38):

set define off
declare
  AGG_COLUMNS VARCHAR2(2000);
begin
   AGG_COLUMNS := q'['S&P (LT Rating)' as SP,'CreditSights CSR' as CSR,'FITCH (LT Rating)' as  Fitch,'Moody''s (LT Rating or CFR)' as MOODY,'DBRS (LT Rating)' as DBRS]';
...

But you don't need to use dynamic SQL at all; you seem to only have it to avoid the ampersand:

set define off
CREATE Global TEMPORARY TABLE temp_extRating
ON COMMIT PRESERVE ROWS
AS
select * from 
(
  select EXTRAT.Rating_ID  ,EXTRAT.AGENCY_NM  ,EXTRAT.EXT_RATING  ,EXTRAT.RBC_EQ_RATING  ,EXTRAT.EFFECTIVE_DATE 
    from RBC_P_EXT_RATING EXTRAT 
    join RBC_P_RATING Rat on 
  Rat.ID=EXTRAT.Rating_ID
  )
pivot (
  MIN(EXT_RATING) ER, 
  MIN(RBC_EQ_RATING) RER, 
  MIN(EFFECTIVE_DATE) ED
  FOR AGENCY_NM IN ('S&P (LT Rating)' as SP,'CreditSights CSR' as CSR,'FITCH (LT Rating)' as  Fitch,q'[Moody's (LT Rating or CFR)]' as MOODY,'DBRS (LT Rating)' as DBRS)
) 
order by Rating_ID;
set define on

Upvotes: 4

Related Questions