Direct SQL with Data Retrieval

Unanswered Question
Nov 17th, 2011
User Badges:

I am getting a BINDING ERROR when trying to use my own sql in a data retrieval rule.

Oracle 10g
ns 2008.3 SP7
Websphere 6.1

The field name is   REQUISITION_TASKS.RIT

the SQL is "select 'With '||firstname||' '|| CASE SUBSTR(lastname,1,1)  WHEN  '<'  THEN ' '  ELSE lastname END||' to do the task - '||subject ||' and is due on - '||DUEON REQ, 'Ongoing tasks listed below' YESREC from TxActivity ,dirperson where ActivityID IN (SELECT DISTINCT ActivityID FROM TxActivity where TicketID IN (SELECT DISTINCT RequisitionEntryID FROM TxRequisitionEntry WHERE RequisitionID IN ('#REQUISITION_TASKS.RIT#'))  ) and (stateid = 2 OR stateid = 6) and waiting = 0 AND performerid = personid

This works fine in SQL plus (as long as I replace #REQUISITION_TASKS.RIT# with a valid Requisition.
When I perform the data retrieval, I get the error...   

System error :[newScale][Oracle JDBC Driver] Invalid parameter binding(s).

Has anyone seen this?

thank you

Safeway Inc.



  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Fred Pitman Thu, 11/17/2011 - 18:16
User Badges:

When it works in SQL Plus, are you logged in with the same ID that RequestCenter is using? Otherwise, you might need to qualify the DB tables by the Owner.

Daniel Faust Thu, 11/17/2011 - 18:16
User Badges:

Yes, I think that the issue is that I am not using the namespace properly.
In SQL plus I provide a number, but in the data retrieval I am using a namespace.

Thank you

Tim Schmitt Thu, 11/17/2011 - 18:16
User Badges:

I had some trouble using a parameter Inbound SQL field of the adapter as well and I wondered if you could even use a parameter in that location.  I know that you can use parameters in the InboundFailureSQl and InboundSuccessSQL but those parameters are references to fields that were pulled using the initial select statement. The manual is not very clear on the use of parameters in the database adapter.

Ant Erickson Thu, 11/17/2011 - 18:16
User Badges:

Hi all,

We've found the SQL engine to be pretty weak in that it only really allows pretty simple Select statements.  One of the suggestions from Leslie was to use SQL functions; so you can code a more complex function that takes parameters and spits results back in the DB itself and then call it using the data retrieval configuration.

Eg, we use common-table-expressions in SQL to obtain the details of a users manager (as the org structure and hierarchies aren't available through the directory).  We have a function in the database called 'findmanager(x,y)' which takes 2 parameters and returns 4-5 pieces of information we need to route the authorisation.

You could do something similar with coding a function to take the parameters from the Order form (or constants) and then have it send back the right info.  Hope this helps!




This Discussion

Related Content