This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
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_co
AND TO_DATE (:r_till_cust_main_acnt_co
ORDER BY driver_name, invoice_date, issue_date;
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_co
AND TO_DATE (:r_till_cust_main_acnt_co
ORDER BY driver_name, invoice_date, issue_date;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pls try this-
AND invh_term_code LIKE ':r_terms'
AND NVL (invh_flex_05, 'N') LIKE ':r_status'
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;
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_co
AND TO_DATE (:r_till_cust_main_acnt_co
BECAUSE WHEN I SKIP THIS THE CODE WORKS AFTER IT SHOWS LITERALS ARE NOT MATCHING
WHERE TO_DATE (issue_date, 'DD-MM-YYYY')
BETWEEN TO_DATE (:r_from_cust_main_acnt_co
AND TO_DATE (:r_till_cust_main_acnt_co
BECAUSE WHEN I SKIP THIS THE CODE WORKS AFTER IT SHOWS LITERALS ARE NOT MATCHING
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'
)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial