Forum Discussion

Ashok_Gopeani's avatar
Ashok_Gopeani
Icon for Helper I rankHelper I
1 year ago
Solved

Facing issue while referring query parameters in direct query

Hi Team,

 

I am trying to referring query parameters in direct query like below but it is giving error always...

SELECT * FROM table_name WHERE cm_run_id = @cmrunid

could you please let me know what is the correct syntax to refer query params in direct query for paginated report

 

Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    Hi Ashok_Gopeani,

    The error ORA-00936 typically occurs in Oracle databases and indicates a missing expression in your SQL query. This error is often caused by an incomplete or improperly structured SQL query.

     

    In Oracle, parameters are typically referred to with a colon (:) before the parameter name, unlike SQL Server which uses @. So, change @cmrunid to :cmrunid for Oracle.

     

    • In Power BI Report Builder, after creating the parameter (cmrunid), make sure that it is mapped correctly to the query. Oracle expects the parameter to be prefixed with a colon (:).

    • Make sure that the Data Type of the parameter matches the Data Type of cm_run_id in the database (e.g., if cm_run_id is a string in the database, set your parameter data type to Text).

    Try using 

    SELECT *
    FROM table_name
    WHERE cm_run_id = :cmrunid

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

     

     

2 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    Hi Ashok_Gopeani,

    The error ORA-00936 typically occurs in Oracle databases and indicates a missing expression in your SQL query. This error is often caused by an incomplete or improperly structured SQL query.

     

    In Oracle, parameters are typically referred to with a colon (:) before the parameter name, unlike SQL Server which uses @. So, change @cmrunid to :cmrunid for Oracle.

     

    • In Power BI Report Builder, after creating the parameter (cmrunid), make sure that it is mapped correctly to the query. Oracle expects the parameter to be prefixed with a colon (:).

    • Make sure that the Data Type of the parameter matches the Data Type of cm_run_id in the database (e.g., if cm_run_id is a string in the database, set your parameter data type to Text).

    Try using 

    SELECT *
    FROM table_name
    WHERE cm_run_id = :cmrunid

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!