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;

Oracle Database* PL/SQLSQL* forms6i,

Last Comment

Pawan Kumar

8/22/2022 - Mon

Pawan Kumar

10/29/2017

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

Sujith S

10/30/2017

ASKER

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

10/30/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Sujith S

10/30/2017

ASKER

i did the same the problem comes when i add the above date

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

Pawan Kumar

10/30/2017

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

Pawan Kumar

10/30/2017

Edited my last post. Pls try that.