troubleshooting Question

when i enter % in :r_status it shows error as literals does not match with constrains error

Avatar of Sujith S
Sujith SFlag for United Arab Emirates asked on
Oracle Database* PL/SQLSQL* forms6i,
6 Comments1 Solution100 ViewsLast Modified:
/* Formatted on 2017/10/30 08:46 (Formatter Plus v4.8.8) */
SELECT   driver_name, TO_DATE (issue_date, 'dd-mm-yyyy') issue_date,
         invh_cust_code, customer_name, invoice_date, invoice_no, do_no,
         terms, remarks, amount, annotation
    FROM (SELECT invh_flex_03 driver_name, invh_flex_04 issue_date,
                 invh_cust_code, invh_cust_name customer_name,
                 invh_dt invoice_date,
                 invh_txn_code || '-' || invh_no invoice_no,
                 invh_ref_txn_code || '-' || invh_ref_no do_no,
                 invh_term_code terms, ' ' remarks, amount,
                 invh_annotation annotation
            FROM ot_invoice_head a
                 JOIN
                 (SELECT   invi_invh_sys_id, SUM (invi_fc_val) amount
                      FROM ot_invoice_item
                  GROUP BY invi_invh_sys_id) b
                 ON a.invh_sys_id = b.invi_invh_sys_id
           WHERE invh_dt >= TO_DATE ('03-09-2013', 'DD-MM-YYYY')
             AND invh_flex_03 LIKE :r_till_cust_code
             AND NVL (invh_ship_city_code, ' ') LIKE :r_city
             AND invh_flex_01 LIKE :r_from_cust_code
            AND invh_term_code LIKE :r_terms
             AND NVL (invh_flex_05, 'N') LIKE :r_status
             AND REGEXP_LIKE (invh_flex_04, '^[[:digit:]]+$')
             AND (   invh_txn_code LIKE 'DT%'
                  OR invh_txn_code LIKE 'DV%'
                  OR invh_txn_code LIKE '%SER'
                 ))
   WHERE TO_DATE (issue_date, 'DD-MM-YYYY')
            BETWEEN TO_DATE (:r_from_cust_main_acnt_code, 'DD/MM/RRRR')
                AND TO_DATE (:r_till_cust_main_acnt_code, 'DD/MM/RRRR')
ORDER BY driver_name, invoice_date, issue_date;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros