Toad World® Forums

Multiple identical place holders causing recurring confirmations


#1

When entering a statement like the one below where the SQLPlaceholder is referenced more than once in the same SQL the optimizer seems toask for each Place Holder to be defined with a value instead of each Uniqueplace holder.

SELECT sedalia.displayname, facility, smr_state, bgs.bug_id,
bgs.creation_ts, BGS.short_desc
FROM bugs bgs,
(SELECT usr.userid, USR.DISPLAYNAME
FROM users usr
WHERE usr.city IN (‘Greenwood Village’, ‘Sedalia’, ‘Englewood’))
sedalia
WHERE bgs.creation_ts >= TO_DATE (:YEAR || ‘0101’, ‘YYYYMMDD’)
AND bgs.creation_ts <= TO_DATE (:YEAR || ‘1231’, ‘YYYYMMDD’)
AND (bgs.REPORTER = sedalia.userid OR bgs.client = sedalia.userid)
ORDER BY sedalia.displayname DESC, bgs.creation_ts DESC


#2

Hi,

For bind variable with the same name but having multiple occurences in a SQL, the product should only need you to enter datatype and value for once. I have just tested your case and I don’t see the problem on my side. Can you please provide more steps how you repeat the error? Also, please specify in which module (Batch Optimizer or Tuning Lab) you have this problem.

Thanks,
Alex