jdbc query help

[sahana_ps]

hi all,

I have a requirement

a. Read table MOC and fetch the values for UNIT_ID, MOC_NO, REF_NO and CER_NO where field STATUS has value 3.

b. From the shortlisted entries,read table MOC where CER_NO  is blank

c. For the finally shortlisted UNIT_ID, MOC_NO and REF_NO above, get the value for EQUIPMENT_COMMENT from MOC_EQUIPMENT table where EQUIPMENT_NO =84  and pass it to RFC

the query i have written is

Select e.EQUIPMENT_COMMENT, e.unit_id, e.moc_no,e.ref_no from MOC_EQUIPMENT e join MOC m on e.unit_id = m.unit_id and e.moc_no=m.moc_no and e.ref_no=m.ref_no  where m.status=4 or m.status=9 and m.cer_no is null and e.EQUIPMENT_NO=84

i am not getting any error for this when i run the query ,but data is not read from database table.can anyone help with the query

thanks

[rajasekhar_reddy14]

Run same query on data base level and check wethere data was there in table or not.

[Former Member]

Hi Sahana,

      First whatever query you have written try running the same query on the sql client. If you get the desired result then I don't see any reason why you are not able to fetch the same in PI. If the query is fine and running successfully on the sql client then check your connection parameters of your sender adapter channel and firewall settings.

Also, use m.cer_no IS NULL as mentioned above.

Hope this helps.

[baskar_gopalakrishnan2]

Couple of  suggestions.

a) Use logSQLStatement = true in the advance tab of the jdbc channel. This will help log trace during runtime.

b) use NULL (uppercase) in the query

c) I dont see status = 3 here. (Per your requirement)

d) Test using TOAD or othe sql client tool using this query and see what result you get. This is the easiest way to find it out.