Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Bind Variable

Received Response
61
Views
5
Comments
GaryWW
GaryWW Rank 3 - Community Apprentice
in Oracle Analytics Cloud and Server

Summary

Bind Variable

Content

Required to create a custom version of an Oracle Data definition to add a new field.

The Data Definition dataQuery sqlStatement's are mostly populated via a beforeReport package trigger

e.g.

<sqlStatement name="Q_SUMMARY_SELECT">

  <![CDATA[

                &P_SUMMARY_SQL_STATEMENT

    AND :L_RUN_SUMMARY_REPORT = 'Y' ]]>

  </sqlStatement>

Can I reference one of the fields within Q_SUMMARY_SELECT as a following Bind Varaible ?

I've tried but the value looks to be null.

I don't want to waste time on the impossible :-)

Thanks.

Answers

  • GaryWW
    GaryWW Rank 3 - Community Apprentice

    Apologies for any confusion.

    The existing Data Definition includes sqlStatement Q_SUMMARY_SELECT which is populated via a PL/SQL package into &P_SUMMARY_SQL_STATEMENT.

    I'm wanting to reference on of the output fields  from that statement in a following sqlStatement definition.

    I've tried using :COLUMN_NAME but request log shows [STATEMENT] 1: COLUMN_NAME:null

    Before spending time with the code I was querying whether Bind Variables work as normal when the referenced column is within an external package generated statement.

    Hope this is clear ?

  • User910243567
    User910243567 Rank 6 - Analytics Lead 
    Can I reference one of the fields within Q_SUMMARY_SELECT as a following Bind Varaible ?

    Your question is not clear.

  • Jagadekara
    Jagadekara Rank 3 - Community Apprentice

    Hi,

    It's not clear. As per my understanding, if there are two queries, you want to use first query column in second query. Right?

    Then yes, bind variable is correct.

    You can use like :First_col_column

    Hope this will help you...

  • Vani-R
    Vani-R Rank 4 - Community Specialist

    Not sure if this could be solution but, I think you can go for temporary table (which gets populated from pl/sql package )which can be used in another query

  • GaryWW
    GaryWW Rank 3 - Community Apprentice

    Not the solution that I was after, but some lateral thinking results in a different way round the issue.

    <sqlStatement name="Q_SUMMARY_SELECT">

      <![CDATA[ 

        SELECT T.COLUMN_NAME,

            J.*

      FROM   TABLE_NAME T

       INNER JOIN ( &P_SUMMARY_SQL_STATEMENT

                       AND :L_RUN_SUMMARY_REPORT = 'Y' ) J

                      ON J.COLUMN_NAME = T.COLUMN_NAME ]]>

      </sqlStatement>

    Happy days.

    Nice to know if there is a way around the null Bind Variable, though.