Issue with SQL function in SAP BODS

[Former Member]

Hi All,

I am looking for SQL help in BODS v4.0

I have given the below sql in a script and trying to run in BODS. While execution i am getting an error as

Failed due to error <70301> Ora-90005 missing key word;

sql('DS_STG',

'(select Branch_Plant, dataset_name  INTO  Gv_Branch_Plant, $Gv_DSName

from

(select Rgn_Mkt_Cntry,Branch_Plant, dataset_name,row_number() over (partition by Rgn_Mkt_Cntry

order by Rgn_Mkt_Cntry, Branch_Plant) AS Row_Id

From

STG_SCM_BRANCH_PLANT_PARAMETER)

where Rgn_Mkt_Cntry = {$Gv_Rgn_Mkt_Cntry} and ROW_ID = {$Gv_Row_ID} ');

FYI: The syntax is validated in the JOB.Only run time it is giving error. I tried giving begin end and no positive result.

I know by assning to global variable individually as Gv_Branch_Plant = Sql or Gv_DSName will work.

But i want the value to be taken in one sql instead of two.

Can some one tell me assigning two variables into in the sql will be possible.

Thanks in advance

Regards,

Gannu

[former_member190054]

Hi Sir,

sql('DS_STG',

'(select Branch_Plant, dataset_name  INTO  Gv_Branch_Plant, $Gv_DSName

from

(select Rgn_Mkt_Cntry,Branch_Plant, dataset_name,row_number() over (partition by Rgn_Mkt_Cntry

order by Rgn_Mkt_Cntry, Branch_Plant) AS Row_Id

From

STG_SCM_BRANCH_PLANT_PARAMETER)

where Rgn_Mkt_Cntry = {$Gv_Rgn_Mkt_Cntry} and ROW_ID = {$Gv_Row_ID} '));

i think here the bracket are not correctly closed ...:)..so put one more closing bracket at the end..

Regards

Asgar

[titto_antony]

Hi Ganesh,

Can you provide the error message that you are getting while executing the job.

Also Try [] instead of {} around the global variable. Also provide ' around the global variable if it is passing the string valueEg:\'[$Gv_Rgn_Mkt_Cntry]\'

[Former Member]

Hi Tito,

I am getting below

<OCIStmtExecute>: <ORA-00911: invalid character error:

Function call <sql ( DS_SC_STG, (select Branch_Plant, dataset_name  INTO  $Gv_Branch_Plant, $Gv_Dataset_Name

from

(select Rgn_Mkt_Cntry,Branch_Plant, dataset_name,row_number() over (partition by Rgn_Mkt_Cntry

order by Rgn_Mkt_Cntry, Branch_Plant) AS Row_Id

From

STG_SCM_BRANCH_PLANT_PARAMETER)

where Rgn_Mkt_Cntry = 'EMEA_ZA' and ROW_ID = 1  ) > failed, due to error <70301>: <Oracle <DD1> error message for           operation

<OCIStmtExecute>: <ORA-00911: invalid character

The modified query is:

sql('DS_SC_STG',

'(select Branch_Plant, dataset_name  INTO  $Gv_Branch_Plant, $Gv_Dataset_Name

from

(select Rgn_Mkt_Cntry,Branch_Plant, dataset_name,row_number() over (partition by Rgn_Mkt_Cntry

order by Rgn_Mkt_Cntry, Branch_Plant) AS Row_Id

From

STG_SCM_BRANCH_PLANT_PARAMETER)

where Rgn_Mkt_Cntry = \'[$Gv_Rgn_Mkt_Cntry]\' and ROW_ID = [$Gv_Row_ID] ');

[titto_antony]

Hi ganesh,

There is one issue with your query.I think we cannot assign value direcltly into the BODS variable used inside the query fired to the SQL server. Can you try with the below code.

$Gv_Branch_Plant=sql('DS_SC_STG','select Branch_Plant from  (select Rgn_Mkt_Cntry,Branch_Plant, dataset_name,row_number() over (partition by Rgn_Mkt_Cntry  order by Rgn_Mkt_Cntry, Branch_Plant) AS Row_Id  From  STG_SCM_BRANCH_PLANT_PARAMETER)  where Rgn_Mkt_Cntry = \'[$Gv_Rgn_Mkt_Cntry]\' and ROW_ID = [$Gv_Row_ID] ');

$Gv_DSName = sql('DS_SC_STG','select dataset_name  from  (select Rgn_Mkt_Cntry,Branch_Plant, dataset_name,row_number() over (partition by Rgn_Mkt_Cntry  order by Rgn_Mkt_Cntry, Branch_Plant) AS Row_Id  From  STG_SCM_BRANCH_PLANT_PARAMETER)  where Rgn_Mkt_Cntry = \'[$Gv_Rgn_Mkt_Cntry]\' and ROW_ID = [$Gv_Row_ID] ');

[Former Member]

You are right Tito

We can't assign variable directly to Global variable like

INTO  $Gv_Branch_Plant, $Gv_DSName (which we do in Oracle db).

Created individual select statement for eac global varialble and assigned individually.