CODEWITHSUNDEEP
sqloracle-database
Tompa
Tompa

Reputation: 11

ORA-00920 - Invalid Relational Operator Error

I'm trying to understand why i am now getting this error ORA-00920.

(SOURCE_REF1, SOURCE_REF2, SOURCE_REF3, RECEIPT_NO) IN (SELECT ORDER_NO, LINE_NO, RELEASE_NO, RECEIPT_NO FROM &AO.PURCHASE_RECEIPT_STATISTICS WHERE order_line_objstate IN ('Arrived', 'Received', 'Closed')
AND qty_arrived > 0 
AND rental_db = 'FALSE' 
AND NVL(&AO.COMPANY_SITE_API.GET_COMPANY(CONTRACT),'%') LIKE NVL('$COMPANY$','%') 
AND NVL(CONTRACT,'%') LIKE NVL('$SITE$','%') 
AND NVL(VENDOR_NO,'%') LIKE NVL('$SUPPLIER_ID$','%')  
AND NVL(&AO.PURCHASE_ORDER_API.GET_BUYER_CODE(ORDER_NO),'%') LIKE NVL('$PERSON_BUYER$','%') 
AND REAL_ARRIVAL_DATE >= SYSDATE-$DAYS$) 
AND &AO.Purchase_Stat_Util_API.Get_Max_Days_Late (SOURCE_REF1, SOURCE_REF2, SOURCE_REF3, RECEIPT_NO) = 0 
AND &AO.Purchase_Stat_Util_API.Get_Max_Days_Early (SOURCE_REF1, SOURCE_REF2, SOURCE_REF3, RECEIPT_NO) = 0
AND EXTRACT(YEAR FROM DELIVERY_DATE) LIKE NVL('$YEAR$','%')
AND EXTRACT(MONTH FROM DELIVERY_DATE) LIKE NVL('$MONTH$','%')

checked all Parenthesis

Upvotes: 0

Views: 71

Answers (1)

MT0
MT0

Reputation: 168730

Indenting your query makes it so much easier to work out which parts are related to which other parts:

WHERE  (SOURCE_REF1, SOURCE_REF2, SOURCE_REF3, RECEIPT_NO) IN (
         SELECT ORDER_NO, LINE_NO, RELEASE_NO, RECEIPT_NO
         FROM   &AO.PURCHASE_RECEIPT_STATISTICS
         WHERE  order_line_objstate IN ('Arrived', 'Received', 'Closed')
         AND    qty_arrived > 0 
         AND    rental_db = 'FALSE' 
         AND    NVL(&AO.COMPANY_SITE_API.GET_COMPANY(CONTRACT),'%') LIKE NVL('$COMPANY$','%') 
         AND    NVL(CONTRACT,'%') LIKE NVL('$SITE$','%') 
         AND    NVL(VENDOR_NO,'%') LIKE NVL('$SUPPLIER_ID$','%')  
         AND    NVL(&AO.PURCHASE_ORDER_API.GET_BUYER_CODE(ORDER_NO),'%') LIKE NVL('$PERSON_BUYER$','%') 
         AND    REAL_ARRIVAL_DATE >= SYSDATE-$DAYS$
       ) 
AND    &AO.Purchase_Stat_Util_API.Get_Max_Days_Late (SOURCE_REF1, SOURCE_REF2, SOURCE_REF3, RECEIPT_NO) = 0 
AND    &AO.Purchase_Stat_Util_API.Get_Max_Days_Early (SOURCE_REF1, SOURCE_REF2, SOURCE_REF3, RECEIPT_NO) = 0
AND    EXTRACT(YEAR FROM DELIVERY_DATE) LIKE NVL('$YEAR$','%')
AND    EXTRACT(MONTH FROM DELIVERY_DATE) LIKE NVL('$MONTH$','%')

If your query is being run through a pre-processor that replaces all the $$ delimited strings with actual values then it is "fine" (probably, assuming your pre-processor does not introduce any SQL injection vulnerabilities); however, if you don't run it through any pre-processor then:

SYSDATE-$DAYS$

Is not valid SQL and generates the syntax error you are seeing.

Upvotes: 0

Related Questions