Updating Database with using a Parameter

[kevinmdaly]

I am trying to update my database using a command in my crystal sub report with a parameter. I am using the following command in my subreport to test my functionality.

Declare

R_Inv5 varchar(30);

LABELID NUMBER(9,0);

begin

Update arinvt set cuser5 = '66666' where id = 202685;

if {?Pm-MASTER_LABEL.ID} is null then

select 9385776 into LABELID from DUAL;

else

select {?Pm-MASTER_LABEL.ID} into LABELID from DUAL;

end if;

update master_label set inv_cuser5 = '666769'

where id = LABELID;

update lminvtry set inv_cuser5 = '8884444' where master_label_id = 9385776;

select 9378870 into LABELID from DUAL;

end;

The highlighted line where I Have hardcoded the master_label_id appears to be executed but the command using the {?Pm-MASTER_LABEL.ID} parameter does not appear to execute.

When I use the parameter in a select command its appears to work so the parameter value appears to get passed into the command correctly.

select m.inv_cuser5,l.inv_cuser5 as luser5,{?Pm-MASTER_LABEL.ID} as id from master_label m

join lminvtry l on l.master_label_id = m.id

where m.id = {?Pm-MASTER_LABEL.ID}

ID from above displays on subreport with correct value.

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

Since you're asking a question here for the first time, I'd like to recommend you with the following steps so you can get the most out of your community membership:

• Please see: https://community.sap.com/resources/questions-and-answers as this resource page provides tips for preparing questions that draw responses from our members
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html. as that will help you when submitting questions to the community.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,
-Alex

[former_member11696]

Be careful doing updates using CR, if Verify Database options are enabled it can run the query 2 times causing problems...

As Dell suggest, avoid doing this...

[ido_millet]

Another option is to use a Crystal UFL (User Function Library) that adds functions to Crystal so formulas can execute SQL statements.

A list of 3rd-party UFLs is maintained here .
At least one of these UFLs provides functions to trigger dynamic SQL statements from Crystal Reports formulas.

[DellSC]

Crystal is not really intended to be used to update data. However, you could try this instead (because you don't need to use "Select" to set the value of a variable in Oracle):

Declare
  R_Inv5 varchar(30);
  LABELID NUMBER(9,0);

begin
  Update arinvt set cuser5 = '66666' where id = 202685;
  if {?Pm-MASTER_LABEL.ID} is null then
    LABELID := 9385776 ;
  else
    LABELID := {?Pm-MASTER_LABEL.ID};
  end if;

  update master_label 
  set inv_cuser5 = '666769'
  where id = LABELID;

  update lminvtry 
  set inv_cuser5 = '8884444' 
  where master_label_id = 9385776;

  select 9378870 into LABELID from DUAL;
end;

If that doesn't work, you might have to create a stored procedure that will do the update and return a dataset - even if it's a single field - in an InOut parameter.

-Dell