DataSource.Error: Oracle: ORA-00911: invalid character

[lotussutol]

I am looking for help on why the following SQL does not work in Power BI.

The SQL I have works fine when I test it in TOAD but when I try it in Power BI I get the following error.

DataSource.Error: Oracle: ORA-00911: invalid character
Details:
DataSourceKind=Oracle
DataSourcePath=d7ip_adg
Message=ORA-00911: invalid character
ErrorCode=-2147467259

Here is the SQL.

WITHq as ( 
    select        table1.name as label,        count(table1.id) as count_id,        
        row_number() over (order by count(table1.id) desc) as rn    
    from table1   
    where table1.name like 'D%'  
    group by table1.name)
select
    case
        when rn <= 9
        then label
        else 'Other'
    end label,    sum(count_id) count_idfrom qgroup by
    case
        when rn <= 9
        then label
        else 'Other'
    end;

IF I strip out the last part and run it like this it will return data but not group it up like I need.

select        table1.name as label,        count(table1.id) as count_id,        
        row_number() over (order by count(table1.id) desc) as rn    
    from table1   
    where table1.name like 'D%'  
    group by table1.name

[v-yuezhe-msft · Accepted solution]

@lotussutol,

Please remove semicolon at the end of your command. I can import data to Power BI Desktop successfully when I execute this statement :

WITH q as ( 
    select        table1.name as label,        count(table1.id) as count_id,        
        row_number() over (order by count(table1.id) desc) as rn    
    from table1   
    where table1.name like 'D%'  
    group by table1.name)
select
    case
        when rn <= 9
        then label
        else 'Other'
    end label,    sum(count_id) count_id from q group by
    case
        when rn <= 9
        then label
        else 'Other'
    end

 


Regards,
Lydia

[lotussutol]

Thanks, that worked!

[lotussutol]

ugh...the formating made the SQL look really ugly.