OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » DATES

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DATES [message #19010]

Mon, 25 February 2002 22:22

Rm69
Messages: 39
Registered: January 2002

Member

The following error has occurred:

ORA-01861: literal does not match format string
ORA-06512: at "EXWH.MIS_NEWBUSINSERT_PROC", line 573
ORA-06512: at line 9

CREATE OR REPLACE PROCEDURE mis_newbusinsert_proc (
v_start_date DATE,
v_end_date DATE
)
IS
v_cnt NUMBER;

BEGIN

--------------------------------------------------------------------------------
-- OTHER ---------------------------------------------------------------------
--------------------------------------------------------------------------------
-- SBSA --
INSERT INTO mis_newbusiness
(branch, sbsa, sbsa_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital),SYSDATE, v_start_date
FROM zw30800p
WHERE disdate BETWEEN v_start_date AND v_end_date
AND company IN ('002')
GROUP BY branch;

-- QUERY TO SUM CAPITAL ON EDCON --
INSERT INTO mis_newbusiness
(branch, edcon, edcon_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital),SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd = 'KIOSKS'
AND disdate BETWEEN v_start_date AND v_end_date
OR loanoff IN ('153',
'1114',
'97',
'4843',
'4849',
'1157',
'E0080',
'J0196'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
AND company NOT IN ('002')
GROUP BY branch;

-- SUM OF STAFF LOANS --
INSERT INTO mis_newbusiness
(branch, staff_loans, staff_loans_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) = 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- CALL CENTRE --
INSERT INTO mis_newbusiness
(branch, call_centre, call_center_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd = 'AB CC'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

--------------------------------------------------------------------------------
-- CHANNELS ------------------------------------------------------------------
--------------------------------------------------------------------------------
-- BSP --
INSERT INTO mis_newbusiness
(branch, bsp, bsp_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- SUM OF HYBRID products at branch level --
INSERT INTO mis_newbusiness
(branch, hybrid_consultants, hybrid_count, summary_date,
captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd = 'HMC'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- MOBILE SALES product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, mobile_sales, mob_sales_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd = 'CREDIT'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- ESPS product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, esps, esps_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd = 'ESP'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- PSA product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, psa, psa_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd = 'PSA'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- BROKERS product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, brokers, brokers_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd IN ('AIN',
'HOSPERA',
'REGIONAL',
'COVERHOLD',
'NATU',
'ACC CARE',
'WACC',
'TAF BROK'
)
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

------------------------------------------------------------------------
------ PRODUCTS - BRANCHES -----------------------------------------------
------------------------------------------------------------------------

-- EXEMPT LOANS --
INSERT INTO mis_newbusiness
(branch, payroll_exempt, pay_exempt_count, summary_date,
captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) NOT IN ('D', 'N', 'J', 'S')
AND product NOT IN ('CL001', 'CL002')
AND SUBSTR (product, 1, 1) != 'T'
AND SUBSTR (product, 3, 1) != 'T'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- COMPLIANT --
INSERT INTO mis_newbusiness
(branch, payroll_compliant, pay_comp_count, summary_date,
captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) NOT IN ('D', 'N', 'J', 'S')
AND ( product = 'CL001'
OR product = 'CL002'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN ('153',
'1114',
'97',
'4843',
'4849',
'1157',
'E0080',
'J0196'
)
OR SUBSTR (product, 1, 1) = 'T'
OR SUBSTR (product, 3, 1) = 'T'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- EPLAN , EPLAN_COUNT --
INSERT INTO mis_newbusiness
(branch, eplan, eplan_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product NOT IN ('JL016', 'JL021', 'JL022')
AND SUBSTR (product, 1, 1) = 'J'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- EPLAN_ENTRY, EPLAN_ENTRY_COUNT " INVESTIGATE ERROR" --
INSERT INTO mis_newbusiness
(branch, eplan_entry, eplan_entry_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product IN ('JL016', 'JL021', 'JL022')
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- NUPAY , NUPAY_COUNT --
INSERT INTO mis_newbusiness
(branch, nupay, nupay_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product NOT IN ('NL005', 'NL006')
AND SUBSTR (product, 1, 1) = 'N'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- NUPAY_ENTRY, NUPAY_ENTRY_COUNT --
INSERT INTO mis_newbusiness
(branch, nupay_entry, nupay_entry_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product IN ('NL005', 'NL006')
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

-- CATCHALL, CATCHALL_COUNT --
INSERT INTO mis_newbusiness
(branch, catchall, catchall_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) = 'D'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;

------------------------------------------------------------------------
------ PRODUCTS - SBROKERCD --------------------------------------------------
------------------------------------------------------------------------
-- EXEMPT LOANS --
INSERT INTO mis_newbusiness
(sbrokercd, payroll_exempt, pay_exempt_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) NOT IN ('D', 'N', 'J', 'S')
AND product NOT IN ('CL001', 'CL002')
AND SUBSTR (product, 1, 1) != 'T'
AND SUBSTR (product, 3, 1) != 'T'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- COMPLIANT --
INSERT INTO mis_newbusiness
(sbrokercd, payroll_compliant, pay_comp_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) IN ('D', 'N', 'J', 'S')
AND ( product = 'CL001'
OR product = 'CL002'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
OR SUBSTR (product, 1, 1) = 'T'
OR SUBSTR (product, 3, 1) = 'T'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- EPLAN , EPLAN_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, eplan, eplan_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product NOT IN ('JL016', 'JL021', 'JL022')
AND SUBSTR (product, 1, 1) = 'J'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- EPLAN_ENTRY, EPLAN_ENTRY_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, eplan_entry, eplan_entry_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product IN ('JL016', 'JL021', 'JL022')
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- NUPAY , NUPAY_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, nupay, nupay_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product NOT IN ('NL005', 'NL006')
AND SUBSTR (product, 1, 1) = 'N'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- NUPAY_ENTRY, NUPAY_ENTRY_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, nupay_entry, nupay_entry_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product IN ('NL005', 'NL006')
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

-- CATCHALL, CATCHALL_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, catchall, catchall_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) = 'D'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;

--------------------------------------------------------------------------------
-- CHECKING FOR DUPLICATES BEFORE COPYING DATA TO HISTORY TABLE --------------
--------------------------------------------------------------------------------

v_cnt := 0;

SELECT COUNT(*)
INTO v_cnt
FROM mis_wrkflw_pdincl_hist

WHERE TO_DATE(v_start_date,'yyyy-mm-dd') =
TO_DATE(captured,'yyyy-mm-dd')
AND TO_DATE(SYSDATE,'yyyy-mm-dd') =
TO_DATE(summary_date,'yyyy-mm-dd');

IF v_cnt = 0
THEN
INSERT INTO mis_newbus_hist
(branch, sbrokercd, sbsa, sbsa_count, bsp, bsp_count, edcon,
edcon_count, staff_loans, staff_loans_count, call_centre,
call_center_count, payroll_exempt, pay_exempt_count,
payroll_compliant, pay_comp_count, hybrid_consultants,
hybrid_count, mobile_sales, mob_sales_count, esps,
esps_count, psa, psa_count, brokers, brokers_count, eplan,
eplan_count, nupay, nupay_count, eplan_entry,
eplan_entry_count, nupay_entry, nupay_entry_count, catchall,
catchall_count, captured, summary_date)

SELECT branch, sbrokercd, sbsa, sbsa_count, bsp, bsp_count, edcon,
edcon_count, staff_loans, staff_loans_count, call_centre,
call_center_count, payroll_exempt, pay_exempt_count,
payroll_compliant, pay_comp_count, hybrid_consultants,
hybrid_count, mobile_sales, mob_sales_count, esps, esps_count,
psa, psa_count, brokers, brokers_count, eplan, eplan_count,
nupay, nupay_count, eplan_entry, eplan_entry_count,
nupay_entry, nupay_entry_count, catchall, catchall_count,
captured, summary_date
FROM mis_newbusiness;
END IF;
END;
/

Report message to a moderator

Re: DATES [message #19012 is a reply to message #19010]

Tue, 26 February 2002 00:12

Umamaheswar
Messages: 1
Registered: February 2002

Junior Member

hai

Literals in the input must be the same length as literals in the format string (with the exception of leading white space). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra white space.
Correct the format string to match the literal.

uma

Report message to a moderator

Previous Topic:	Re: Create simple view
Next Topic:	Re: core dump, please help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 17:43:13 CDT 2024