1. 

   JMitchell, Member

   My dbr.editable is returning that it doesnt know what vCon is in this example. How can i pass my report parameters into an editable select statement?

   CREATE PROCEDURE sp_DBR_eligscreen( vSel int, vCon varchar(45) )
   BEGIN

   select 'dbr.editable', 9, 'sp_DBR_models', 'vMake=8', 'vCon1=11', 'vSel1=10', 'type=select',
   "select= select e.model from x.client cl left outer join x.eligible_mm e on e.make = cl.car_make where contract_policy = vCon ;";

   select cl.client_app_id as "Client ID"
   , cl.client_fname as "First Name"
   , cl.client_lname as "Last Name"
   , cl.activity_purchase_dt as "Sale Date"
   , cl.contract_policy as "Contract/Policy Number"
   , cl.finance_account as "Finance Account Number"
   , cl.car_year as "Year"
   , cl.car_make as "Make"
   , cl.car_model as "Model"
   , vSel
   , vCon
   from x.client cl
   where contract_policy = vCon;
2. 

   myDBR Team, Key Master

   Instead of passing string 'vCon' into the select statement, pass the value of the vCon. Use the concat-function to add the value into the end of of the query string.

   P.S. Using column references in myDBR calls instead of column numbers makes your code much easier to write and maintain.
   --
   myDBR Team
3. 

   JMitchell, Member

   can you show me an example from my code of how i would pass the value?

   like this?

   declare vstring varchar(255);

   set vstring = concat("select= select e.model from x.client cl left outer join x.eligible_mm e on e.make = cl.car_make where contract_policy = ",vCon);

   select 'dbr.editable', 9, 'sp_DBR_models', 'vMake=8', 'vCon1=11', 'vSel1=10', 'type=select',vstring;
4. 

   myDBR Team, Key Master

   Yes,
   just make sure you enclose the variable value into quotation marks if it is a string. If it is a number, you need to cast it to a string.

   --
   myDBR Team
5. 

   JMitchell, Member

   which variable and where do i need to enclose it in quotes?

   vCon or vstring?
6. 

   JMitchell, Member

   nvm i got it! Thank you so much for your help!
7. 

   myDBR Team, Key Master

   You need to create a valid SQL statement for the select option.

   "select .... where contract_policy = vCon" is not valid SQL as vCon would be treated as an unknown column by the SQL parser. You need to use the value of the vCon-variable instead.

   So if the vCon is a number use:
   concat( "select .... where contract_policy = ", cast( vCon as char(10) ) )

   if the vCon is a string use:
   concat( "select .... where contract_policy = '",vCon, "'" )

   --
   myDBR Team

Reply

You must log in to post.