cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
n4tusi4
Regular Visitor

Oracle: ORA-00911: invalid character in query

Hello,

Can You please help me to identify what power BI treats as an invalid character in this query? I can add that it works fine in SQL developer. I have already removed all "%", ";"   and "&". Many thanks for Your support 

 

SELECT distinct (
SELECT sob.NAME
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id
) "SOB_Name"
,(
SELECT sob.set_of_books_id
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id
) "SOB_Name"
,a.period_name "Period_Name"
,a.period_num "Period_Num"
,a.gl_status "GL_Status"
,b.po_status "PO_Status"
,c.ap_status "AP_Status"
,d.ar_status "AR_Status"
,DECODE(e.fa_status,null,'NA',e.fa_status) "FA_Status"
,DECODE(f.rl_status,null,'NA',f.rl_status) "RL_Status"
,g.pa_status "PA_Status"
FROM (
SELECT period_name
,period_num
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) gl_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 101
) a
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) po_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 201
) b
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) ap_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 200
) c
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', 'W','Pending Close',closing_status) ar_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 222
) d
,(
SELECT fdp.period_name
,DECODE(fdp.period_close_date, null, 'Open', 'Closed') fa_status
,fbc.set_of_books_id
FROM apps.fa_book_controls fbc
,apps.fa_deprn_periods fdp
WHERE fbc.book_type_code = fdp.book_type_code
) e
,(
SELECT period_name
,period_num
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) rl_status
,from_ledger_id
FROM apps.gl_period_statuses gps
,apps.gl_consolidation gc
WHERE gps.application_id = 101
AND gps.set_of_books_id = gc.to_ledger_id
) f
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) pa_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 8721
) g

WHERE a.period_name IN (
SELECT period_name
FROM apps.gl_period_statuses
WHERE start_date between '01/OCT/2018' and sysdate+60

)
AND a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.period_name = f.period_name(+)
AND a.period_name = g.period_name(+)
AND a.set_of_books_id IN (
SELECT ls.ledger_id
FROM apps.gl_ledger_segment_values ls
,apps.xle_entity_profiles xle
,apps.gl_ledgers l
WHERE ls.segment_value = xle.LEGAL_ENTITY_IDENTIFIER
AND ls.ledger_id = l.ledger_id
AND xle.effective_from IS NOT NULL
AND (
xle.effective_to > sysdate
OR xle.effective_to IS NULL
)
)
AND a.set_of_books_id = b.set_of_books_id(+)
AND a.set_of_books_id = c.set_of_books_id(+)
AND a.set_of_books_id = d.set_of_books_id(+)
AND a.set_of_books_id = e.set_of_books_id(+)
AND a.set_of_books_id = f.from_ledger_id(+)
AND a.set_of_books_id = g.set_of_books_id(+)

ORDER BY 1,4
1 ACCEPTED SOLUTION
n4tusi4
Regular Visitor

The issue was created by the same name of 2 different columns. I have changed a name of 2nd one and now it works.

SELECT distinct (
SELECT sob.NAME
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id
) "SOB_Name"
,(
SELECT sob.set_of_books_id
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id
) "SOB_Name"

View solution in original post

3 REPLIES 3
n4tusi4
Regular Visitor

The issue was created by the same name of 2 different columns. I have changed a name of 2nd one and now it works.

SELECT distinct (
SELECT sob.NAME
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id
) "SOB_Name"
,(
SELECT sob.set_of_books_id
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id
) "SOB_Name"
v-yalanwu-msft
Community Support
Community Support

Hi, @n4tusi4

May be here. try to change left join.

vyalanwumsft_0-1655088386027.png

If a special character other than $, _, and # is used in the name of a column or table, the name must be enclosed in double quotations. Link


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-yalanwu-msft ,

Unfortunately it didn't help. + is allowed sign from what I can see on my previous queries. What is funny about this query - it worked on previous dashboard but I assume, my colleague worked on older version of power BI.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors