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

/* 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;
Sujith SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Pls try this-

AND invh_term_code LIKE ':r_terms'
AND NVL (invh_flex_05, 'N') LIKE ':r_status'

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;

Open in new window

0
Sujith SAuthor Commented:
no its not working is there any issue with the date


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')


BECAUSE WHEN I SKIP THIS THE CODE WORKS AFTER IT SHOWS LITERALS ARE NOT MATCHING
0
Pawan KumarDatabase ExpertCommented:
Try adding one one filter and the try.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Sujith SAuthor Commented:
i did the same the problem comes when i add the above date
0
Pawan KumarDatabase ExpertCommented:
Pls check if this is working..

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'
                 )

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Edited my last post. Pls try that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
forms6i,

From novice to tech pro — start learning today.