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

Sujith S
Sujith S used Ask the Experts™
on
/* 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;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Author

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
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Try adding one one filter and the try.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

Commented:
i did the same the problem comes when i add the above date
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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 KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Edited my last post. Pls try that.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial