SetParameterInfo has not been called

[roberthagan]

The following is a query in Oracle, that runs in TOAD, and as the basis for a Crystal "push" report. I'm interested in converting the report to Reporting Services, but I get the following error: Provideer cannot derive parameter inforation and SetParameterInfo has not been called. What casues this on a query that seems to be syntactically correct?

SELECT P.SITE_CD, ST1.STAFF_NAME AS SUPERVISOR, ST1.STAFFCODE AS MGR_KEY, P.PROF_NM AS WORKER, P.STAFFCODE AS WKR_KEY, CL.CLIENT_NM, P.CLIENT_ID, 
MAX(DECODE(SUBSTR(S.STATUS, 1, 2), 'N-', S.STATUS, NULL)) AS REFER_TYPE, 
MAX(decode(SUBSTR(S.STATUS, 1, 2), 'N-', S.STATUS_DT, NULL)) AS REFER_DT, 
MAX(DECODE(S.STATUS, 'O', S.STATUS_DT, NULL)) AS OPEN_DT, MAX(DECODE(S.STATUS, 'TRANS-O', S.STATUS_DT, NULL)) AS TRANSO_DT, 
MAX(DECODE(S.STATUS, 'TRANS-P', S.STATUS_DT, NULL)) AS TRANSP_DT, 
MAX(DECODE(S.STATUS, 'C', S.STATUS_DT, NULL)) AS CLOSE_DT, MAX(DECODE(ENC.ENC_TYPE, '3603', ENC.END_DT, NULL)) AS L3603_DT, 
ENC.END_DT, ENC.ENC_TYPE, ENC.ENC_SITE, ENC.ENC_STAT, ENC.OUTCOME 
FROM PROF P, CLIENT CL, STAFF ST, STAFF ST1, STATUS S,
  (SELECT e.client_id, e.end_dt, e.enc_type, e.enc_site, e.enc_stat, e.outcome 
   FROM encountr e, client cl
   WHERE e.client_id = cl.client_id) ENC
WHERE P.CLIENT_ID = CL.CLIENT_ID AND 
P.STAFFCODE = ST.STAFFCODE AND 
ST.MANAGED_BY = ST1.STAFFCODE AND 
CL.CLIENT_ID = S.CLIENT_ID AND 
CL.CLIENT_ID = ENC.CLIENT_ID (+) AND 
(P.START_DT <= TO_DATE(:end_dt, 'mm/dd/yyyy')) AND (P.END_DT IS NULL OR
  P.END_DT >= TO_DATE(:start_dt, 'mm/dd/yyyy')) AND (P.STAFFCODE = :wkr_key) AND 
(P.EMER_CONT = 'C')
GROUP BY P.SITE_CD, ST1.STAFF_NAME, ST1.STAFFCODE, P.PROF_NM, P.STAFFCODE, CL.CLIENT_NM, P.CLIENT_ID, ENC.END_DT, ENC.ENC_TYPE, ENC.ENC_SITE, ENC.ENC_STAT, ENC.OUTCOME, P.START_DT, P.END_DT

 

[roberthagan]

Update:
If I remove the parameters, and hardcode values into the query, it runs fince

 

[xlbo]

I think it is purely the syntax for the parameters

in RS, parameters are passed like:

WHERE (dbo.Customer.CustomerID = @CustomerID)

This will create a report parameter which is "attached" to the query

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[roberthagan]

Makes sense, since when I hardcode the values, the query runs. However we are actually running this report against Oracle, and there the parameter marker is a colon. I have other, simpler, Oracle parameterized queries in RS with this syntax that work.

Which makes me think I should cut down the complexity of the query and see what happens then.

Thanks

 

[MisterMo]

when using Oracle you can't name the parameters as you would with MS-SQL

I do it this way

WHERE (dbo.Customer.CustomerID = (?))
and (dbo.Customer.Name = (?))

this will create a set of parameters which you can then use in your report



-Mo