jdbc adapter

[sahana_ps]

hi all,

i have a requirement to write sql query

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 and pass it to RFC

i have written a query for this as below

SELECT EQUIPMENT_COMMENT FROM MOC_EQUIPMENT WHERE (UNIT_ID,MOC_NO,REF_NO) IN (SELECT UNIT_ID,MOC_NO,REF_NO, CER_NO FROM MOC WHERE STATUS=3 AND CER_NO = NULL)

i am getting error :too many values

can anyone help me with this query

thanks,

sahana

[former_member184681 · Accepted solution]

Hi,

It looks like an SQL statement issue. Notice that you have four fields on the right side of the WHERE statement:

UNIT_ID,MOC_NO,REF_NO, CER_NO

And only three fields on the left side:

(UNIT_ID,MOC_NO,REF_NO)

Remove the CER_NO from the subquery, like this, and you should be fine:

SELECT UNIT_ID,MOC_NO,REF_NO FROM MOC WHERE STATUS=3 AND CER_NO = NULL

Hope this helps,

Greg

[sahana_ps · Accepted solution]

hi Grzegorz Glowacki ,

thanks for your reply,now the query is working but it s reading only unit_id,moc_no,ref_no form table MOC ,its not reading equipment_comment from table MOC_EQUIPMENT

[former_member184681 · Accepted solution]

Dear Sahana,

Maybe there are simply no documents that match all your selection criteria? You can try testing the same statement in the database server directly.

You might also have to change the "CER_NO = NULL" to "CER_NO IS NULL", as the comparison to NULL using "=" might possibly not be handled correctly.

Regads,

Greg