Toad World® Forums

Bind variable defaulting to a different type


#1

I have 2 queries that I need to join for a report.
My problem comes in that query 1 uses a field act_compldate defined as DATE in oracle table and query 2 uses act_scheddate defined as DATE in oracle table.

When I run query 1 the with a bind variable Report_Dt its TYPE defaults to DATE …
When I run query 2 with the bind variable Report_Dt its TYPE defaults to VARCHAR

When I join these queries the data type matching or rather mismatching ensues.

Any idea why the optimizer doesn’t see the bind variable the same way for each query?


#2

I would have to see the queries. Does the second query use act_scheddate in a function?

Can you provide more details on both queries and how you are joining them? Are you using Automation? Or SQL Editor?

Debbie


#3

select com_record_type,
act_schedrep,
act_id,
act_status,
act_scheddate,
(CASE WHEN :Report_Dt - a.ACT_SCHEDDATE > 0 THEN 1 ELSE 0 END) AS NR_PAST_DUE,
(CASE WHEN a.ACT_SCHEDDATE = :Report_Dt THEN 1 ELSE 0 END) AS NR_TODAY
from mxactivity, company
where act_compid = coid
AND ACT_STATUS = ‘Scheduled’
and com_record_type in(‘GEN’, ‘MED’)

This is query 2 query 1 is a lot more complicated and they are linked by INNER JOIN on act_schedrep = act_complrep

Running these in SQL Editor

I edited the query to CAST(:Report_Dt AS DATE) around each bind variable and it works now.

Message was edited by: DallasW