subquery

[Arun Kumar Menon]

Dear gurus

select null APPNTDATE,TO_char(b.TIMESLOT, 'HH24:MI') apttime,null pin,null name,null telephone,null remarks,null appointmento,doccode,
comp_code,div_code,0 intappno,0 yearcode from EDDOCTORDYSLOT b
where (b.COMP_CODE = ?)
AND (b.DIV_CODE = ?)
AND (b.DOCCODE = ?)
and TO_char(b.TIMESLOT, 'HH24:MI') not in(SELECT to_char(a.APPNTTIME,'hh24:mi')
FROM CMS.EDTRAPPOINTMENT a
WHERE (a.COMP_CODE = ?)
AND (a.DIV_CODE = ?)
AND (a.YEARCODE = ?)
AND (a.DOCCODE = ?)
AND (a.APPNTDATE = ?)

I am novice and am trying to run this query in an oledbadaptor. I have generated the dataset and during run time i am trying to fill the dataset it gives an error saying object instance not set.

If i remove the subquery i am getting the records in the dat set.

appreciate all help

thanks and regards
Arun

 

[lingsn]

Another way to debug it is to run you subquery separately with the same parameters and check whether is it return any rows?

 

[Arun Kumar Menon]

thanks lingsn I tried it thru toad the subquery is working fine. only during thr fill(dset) it is creating a problem.
i tried to preview the data thru componenet design view alos it givves me the same error.

 

[lingsn]

I am expecting there is an error in your sql syntax, a ")" is missing at the back for the subquery select.

select null APPNTDATE,TO_char(b.TIMESLOT, 'HH24:MI') apttime,null pin,null name,null telephone,null remarks,null appointmento,doccode,
comp_code,div_code,0 intappno,0 yearcode from EDDOCTORDYSLOT b
where (b.COMP_CODE = ?)
AND (b.DIV_CODE = ?)
AND (b.DOCCODE = ?)
and TO_char(b.TIMESLOT, 'HH24:MI') not in(SELECT to_char(a.APPNTTIME,'hh24:mi')
FROM CMS.EDTRAPPOINTMENT a
WHERE (a.COMP_CODE = ?)
AND (a.DIV_CODE = ?)
AND (a.YEARCODE = ?)
AND (a.DOCCODE = ?)
AND (a.APPNTDATE = ?))