Home » SQL & PL/SQL » SQL & PL/SQL » Creating a Report.... (PL/SQL - for Oracle)
icon8.gif  Creating a Report.... [message #318317] Tue, 06 May 2008 08:43 Go to next message
mbrizza
Messages: 6
Registered: May 2008
Junior Member
Hi Please help

I am supposed to create a report and this thing just does not work....

WHENEVER SQLERROR EXIT 9;
spool reg_rep.txt;
/* DECLARING INPUT FIELDS*/
CREATE OR REPLACE DIRECTORY reg_dir as '/home/mbatham';
GRANT READ, WRITE ON DIRECTORY REG_DIR TO PUBLIC;
DECLARE
APP_NO VARCHAR2(26);
CUST_NO NUMBER(18);
APP_DAT VARCHAR2(10);
CREDIT_CLASS VARCHAR2(2);
ACC_NO NUMBER(18,0);
TOT_ANN_TRNOVR NUMBER(22,2);
GROSSMNTHLYINCME NUMBER(15,0);
REQ_LIM_LOAN_AMNT NUMBER(22,2);
TERM NUMBER(3, 0);
DECISION_DATE VARCHAR2(10);
DECISION_COD VARCHAR2(1);
LOAN_PURPOSE VARCHAR2(2);
NCA_IN_ACT_IND VARCHAR2(1);
CALLER_COD NUMBER(3,0);
PROD_NO NUMBER(6,0);
NCA_JURISTIC_IND VARCHAR2(1);
NON_RESIDENT VARCHAR2(1);
RACE VARCHAR2(1);
ID_TYPE VARCHAR2(3);
ID_NO VARCHAR2(16);
FIN_DATE VARCHAR2(10);
GENDER VARCHAR2(1);
MRKTINGGRP_COD VARCHAR2(2);
CURR_RUN_D VARCHAR(8);
PREV_BUS_D VARCHAR(8);
reg_dir VARCHAR2(10);
/*DWAPLC01*/
WS_RECRD_ID_N VARCHAR2(1);
WS_APLIC_N VARCHAR2(26);
WS_HOLD_APLIC_N VARCHAR2(26);
WS_CUST_N NUMERIC(10);
WS_APLIC_D VARCHAR2(10);
WS_CRPROV_C NUMERIC(10);
WS_CLASS_C VARCHAR2(2);
WS_ACNT_N VARCHAR2(16);
WS_TURNOVR_A NUMBER(16,2);
WS_ASSET_VALU_A DECIMAL(16,2);
WS_MTHLY_GROS_INCOM_A DECIMAL(16,2);
WS_CREDT_A DECIMAL(16,2);
WS_TERM_MM NUMBER(3,0);
WS_DECISION_D VARCHAR2(8);
WS_DECISION_C VARCHAR2(1);
WS_CREDT_PURPOS_C VARCHAR2(2);
WS_EMERG_TYP_C VARCHAR2(2);
WS_IN_NCA_Y VARCHAR2(1);
WS_TRGT_SOURC_N NUMERIC(3);
WS_PROD_N NUMERIC(5);
WS_JURISTIC_Y VARCHAR2(1);
WS_NON_RES_Y VARCHAR2(1);
WS_RACE_C VARCHAR2(1);
WS_ID_TYPE_C VARCHAR2(6);
WS_ID_N VARCHAR2(60);
WS_GEND_C VARCHAR2(1);
WS_TABLEDATA VARCHAR2(250);

CURSOR REG_REP IS
SELECT
A.T_160_FE_GEN_APP_NO,
A.T_149_FE_GEN_CUST_NO,
A.T_161_FE_GEN_APP_DAT,
A.T_10070_FE_GEN_CREDIT_CLASS,
A.T_169_FE_GEN_ACC_NO,
A.T_3026_AFF_E_TOT_ANN_TRNOVR,
A.T_7669_CPM_CUST_GROSSMNTHLYINC,
A.T_171_FE_GEN_REQ_LIM_LOAN_AMNT,
C.T_9804_CALL_TAKUP_TERM,
C.T_10327_CALL_DEC_DECISION_DATE,
B.T_10824_FIN_DECISION_COD,
D.T_10577_PROD_LN_LOAN_PURPOSE,
A.T_10071_FE_GEN_NCA_IN_ACT_IND,
A.T_154_FE_GEN_CALLER_COD,
A.T_170_FE_GEN_PROD_NO,
A.T_10072_FE_GEN_NCA_JURISTIC_IN,
A.T_7691_CPM_CUST_NON_RESIDENT,
A.T_182_PS_RACE,
A.T_150_FE_GEN_ID_TYPE,
A.T_151_FE_GEN_ID_NO,
c.T_9782_CALL_FIN_FINLISE_DATE,
A.T_7651_CPM_CUST_GENDER,
E.DTE_PREV_BUS_D,
E.DTE_CURR_RUN_D
-- /* A.T_959_FE_GEN_APP_MRKTINGGRP_COD*/
FROM
MAIN_APPLICANT A,
DA_RESULTS_2 B,
CALL_TYPE_FIELDS C,
MAIN_APP_PROD_SPECIFIC D,
BEHVDATE E
WHERE A.SYS_RECORDKEY = B.SYS_RECORDKEY
AND B.SYS_RECORDKEY = C.SYS_RECORDKEY
AND C.SYS_RECORDKEY = D.SYS_RECORDKEY
AND A.T_160_FE_GEN_APP_NO IS NOT NULL
AND A.T_160_FE_GEN_APP_NO <> ' '
AND A.T_154_FE_GEN_CALLER_COD = 51
AND ( A.T_161_FE_GEN_APP_DAT >= E.DTE_PREV_BUS_D
AND A.T_161_FE_GEN_APP_DAT < E.DTE_CURR_RUN_D)
OR (C.T_10327_CALL_DEC_DECISION_DATE >= E.DTE_PREV_BUS_D
AND C.T_10327_CALL_DEC_DECISION_DATE < E.DTE_CURR_RUN_D)
OR (C.T_9782_CALL_FIN_FINLISE_DATE >= E.DTE_PREV_BUS_D
AND C.T_9782_CALL_FIN_FINLISE_DATE < E.DTE_CURR_RUN_D)
ORDER BY A.T_160_FE_GEN_APP_NO ASC
;

BEGIN


FETCH REG_REP INTO APP_NO, CUST_NO, APP_DAT, CREDIT_CLASS, ACC_NO, TOT_ANN_TRNOVR, GROSSMNTHLYINCME, REQ_LIM_LOAN_AMNT, TERM, DECISION_DATE, DECISION_COD, LOAN_PURPOSE, NCA_IN_ACT_IND, CALLER_COD, PROD_NO, NCA_JURISTIC_IND, NON_RESIDENT, RACE, ID_TYPE, ID_NO, FIN_DATE, GENDER, PREV_BUS_D, CURR_RUN_D;

LOOP
EXIT WHEN REG_REP%FOUND = FALSE;

IF DECISION_COD = 'B' THEN
WS_DECISION_C := ' ';
ELSIF DECISION_COD = 'R' THEN
WS_DECISION_C := 'D';
ELSE
WS_DECISION_C := DECISION_COD;
END IF;

IF NCA_IN_ACT_IND = ' ' THEN
WS_IN_NCA_Y:= 'N';
ELSE
WS_IN_NCA_Y:= NCA_IN_ACT_IND;
END IF;

IF NCA_JURISTIC_IND = 'Y' OR NCA_JURISTIC_IND = 'N' THEN
WS_JURISTIC_Y:= NCA_JURISTIC_IND;
ELSIF NCA_JURISTIC_IND = 'J' THEN
WS_JURISTIC_Y:='Y';
ELSE
WS_JURISTIC_Y:='N';
END IF;

IF NON_RESIDENT = ' ' THEN
WS_NON_RES_Y:='N';
ELSE
WS_NON_RES_Y:= NON_RESIDENT;
END IF;

IF RACE = 'A'
OR RACE = 'B'
OR RACE = 'C'
OR RACE = 'W'
OR RACE = 'N' THEN
WS_RACE_C:= RACE;
ELSE
WS_RACE_C:= 'U';
END IF;

IF ID_TYPE = '001' THEN WS_ID_TYPE_C := '01';
ELSIF ID_TYPE = '002' THEN WS_ID_TYPE_C := '02';
ELSIF ID_TYPE = '003' THEN WS_ID_TYPE_C := '03';
ELSIF ID_TYPE = '004' THEN WS_ID_TYPE_C := '04';
ELSIF ID_TYPE = '005' THEN WS_ID_TYPE_C := '05';
ELSIF ID_TYPE = '006' THEN WS_ID_TYPE_C := '06';
ELSIF ID_TYPE = '007' THEN WS_ID_TYPE_C := '07';
ELSIF ID_TYPE = '008' THEN WS_ID_TYPE_C := '08';
ELSIF ID_TYPE = '009' THEN WS_ID_TYPE_C := '09';
ELSE WS_ID_TYPE_C := ID_TYPE;
END IF;

IF GENDER = ' ' THEN
WS_GEND_C:='U';
ELSE
WS_GEND_C:= GENDER;
END IF;

IF LOAN_PURPOSE = 01 THEN WS_CREDT_PURPOS_C:= 'HO';
ELSIF LOAN_PURPOSE = 02 THEN WS_CREDT_PURPOS_C:= 'FU';
ELSIF LOAN_PURPOSE = 03 THEN WS_CREDT_PURPOS_C:= 'VH';
ELSIF LOAN_PURPOSE = 04 THEN WS_CREDT_PURPOS_C:= 'DE';
ELSIF LOAN_PURPOSE = 05 THEN WS_CREDT_PURPOS_C:= 'ED';
ELSIF LOAN_PURPOSE = 06 THEN WS_CREDT_PURPOS_C:= 'EM';
ELSIF LOAN_PURPOSE = 07 THEN WS_CREDT_PURPOS_C:= 'OT';
ELSE WS_CREDT_PURPOS_C:= 'OT';
END IF;

-- IF MRKTINGGRP_COD = '54' THEN
-- WS_CLASS_C := 'US';
-- ELSE
-- WS_CLASS_C := 'OT';
-- END IF;
--
WS_DECISION_D:= DECISION_DATE;

WS_APLIC_D:= APP_DAT;

WS_TURNOVR_A:= TOT_ANN_TRNOVR;

WS_TERM_MM:= TERM;

WS_ASSET_VALU_A:= 0;

WS_CREDT_A:= REQ_LIM_LOAN_AMNT;

WS_TRGT_SOURC_N:= CALLER_COD;

WS_PROD_N:= PROD_NO;

WS_ID_N:= ID_NO;

WS_MTHLY_GROS_INCOM_A:= GROSSMNTHLYINCME;

WS_RECRD_ID_N:= '2';

WS_APLIC_N:= APP_NO;

WS_CUST_N:= CUST_NO;

WS_ACNT_N:= ACC_NO;

WS_CLASS_C:= CREDIT_CLASS;

WS_CRPROV_C:= 0;

WS_EMERG_TYP_C:= ' ';

WS_TABLEDATA:=
'INSERT INTO DWAPLC01
(RECRD_ID_N, APLIC_N, CUST_N, APLIC_D, CRPROV_C, CLASS_C, ACNT_N, TURNOVR_A, ASSET_VALU_A, MTHLY_GROS_INCOM_A, CREDT_A , TERM_MM, DECISION_D, DECISION_C, CREDT_PURPOS_C, EMERG_TYP_C, IN_NCA_Y, TRGT_SOURC_N, PROD_N, JURISTIC_Y, NON_RES_Y, RACE_C, ID_TYPE_C, ID_N,GEND_C)
VALUES (WS_RECRD_ID_N,
WS_APLIC_N,
WS_CUST_N,
WS_APLIC_D,
WS_CRPROV_C,
WS_CLASS_C,
WS_ACNT_N,
WS_TURNOVR_A,
WS_ASSET_VALU_A,
WS_MTHLY_GROS_INCOM_A,
WS_CREDT_A,
WS_TERM_MM,
WS_DECISION_D,
WS_DECISION_C,
WS_CREDT_PURPOS_C,
WS_EMERG_TYP_C,
WS_IN_NCA_Y,
WS_TRGT_SOURC_N,
WS_PROD_N,
WS_JURISTIC_Y,
WS_NON_RES_Y,
WS_RACE_C,
WS_ID_TYPE_C,
WS_ID_N,
WS_GEND_C)';


FETCH REG_REP INTO APP_NO, CUST_NO, APP_DAT, CREDIT_CLASS, ACC_NO, TOT_ANN_TRNOVR, GROSSMNTHLYINCME, REQ_LIM_LOAN_AMNT, TERM, DECISION_DATE, DECISION_COD, LOAN_PURPOSE, NCA_IN_ACT_IND, CALLER_COD, PROD_NO, NCA_JURISTIC_IND, NON_RESIDENT, RACE, ID_TYPE, ID_NO, FIN_DATE, GENDER, PREV_BUS_D, CURR_RUN_D;
END LOOP ;
END;
Exit;

Pleeeeeeeeeeeeeeese help
Re: Creating a Report.... [message #318324 is a reply to message #318317] Tue, 06 May 2008 08:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Completely unreadable. Please format your post and enclose your code in [code] [/code] tags.
Quote:
thing just does not work....
What on earth are we to infer from that? What a useless/ worthless piece of information.
Please read the forum guide on how to ask a question.
Thanks

[Updated on: Tue, 06 May 2008 08:52]

Report message to a moderator

Re: Creating a Report.... [message #318325 is a reply to message #318317] Tue, 06 May 2008 08:52 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>this thing just does not work....
My does does not work.
Please tell me how to make it go.

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above

Re: Creating a Report.... [message #318339 is a reply to message #318317] Tue, 06 May 2008 09:18 Go to previous messageGo to next message
mbrizza
Messages: 6
Registered: May 2008
Junior Member
Im sorry...

Please check attachment then...

The code is supposed to read several tables and write to a table (DWAPLC01). It also needs to write the same output into a file. This happens to be my first PL/SQL program ( I'm a COBOL developer).

I hope this is better.....


PS : Don't be nasty - you also started somewhere. If you feel you don't wanna help just shut up and let those who want to help go on and help.

The world would be a better place with a little tolerance

Peace!
  • Attachment: Report.sql
    (Size: 10.83KB, Downloaded 127 times)
Re: Creating a Report.... [message #318341 is a reply to message #318339] Tue, 06 May 2008 09:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
If you feel you don't wanna help just shut up and let those who want to help go on and help.
Suit yourself.
Re: Creating a Report.... [message #318342 is a reply to message #318317] Tue, 06 May 2008 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>If you feel you don't wanna help just shut up
OK.
Re: Creating a Report.... [message #318346 is a reply to message #318317] Tue, 06 May 2008 09:28 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Try to see it from our point-of-view as well: the number of posts similar to yours is rather discouraging Smile

But then again, let's see if we can get you going.

You provides us with code, and that's about it.
Telling us that your code "does not work" leaves a lot to our imagination.
Why do you think is "does not work"?
Some possible answers to this last question (even a few stupid ones):
- You forgot to compile it?
- You don't call it?
- When you compile it, it gives you an error?
- When you run it, it gives you an error?
- Compilation went well, running the thing too, but the result is not what I expected.

Let alone a test-case would help us well: create statements of the different tables, some test-data (preferrable in INSERT-statements) and etc, etc, etc.

This said: welcome to the forum Smile

[Updated on: Tue, 06 May 2008 09:30]

Report message to a moderator

Re: Creating a Report.... [message #318352 is a reply to message #318346] Tue, 06 May 2008 09:40 Go to previous messageGo to next message
mbrizza
Messages: 6
Registered: May 2008
Junior Member
MarcS wrote on Tue, 06 May 2008 16:28
Try to see it from our point-of-view as well: the number of posts similar to yours is rather discouraging Smile

But then again, let's see if we can get you going.

You provides us with code, and that's about it.
Telling us that your code "does not work" leaves a lot to our imagination.
Why do you think is "does not work"?
Some possible answers to this last question (even a few stupid ones):
- You forgot to compile it? It compiled-
You don't call it? [B]i call it from unix using a .sh script- [/B]When you compile it, it gives you an error? No
- When you run it, it gives you an error? When I run it, the output file gets created and comes out with nothing- Compilation went well, running the thing too, but the result is not what I expected.

Let alone a test-case would help us well: create statements of the different tables, some test-data (preferrable in INSERT-statements) and etc, etc, etc.

I ran the same sql select statement on SQLDEVELOPER just to see if there is any data in the database and there is data - something goes wrong somewhere.... Confused




Re: Creating a Report.... [message #318353 is a reply to message #318317] Tue, 06 May 2008 09:43 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Briefly had a look at the code.
Just a silly question: where do you WRITE your output ???
Re: Creating a Report.... [message #318354 is a reply to message #318353] Tue, 06 May 2008 09:46 Go to previous messageGo to next message
mbrizza
Messages: 6
Registered: May 2008
Junior Member
I am supposed to write the output into a .txt file.

No such thing as a silly question....=;)
Re: Creating a Report.... [message #318355 is a reply to message #318317] Tue, 06 May 2008 09:47 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
"supposed to write" ...

Well, could you post the code-snippet(s) where you actually do the writing to your output-file.
Re: Creating a Report.... [message #318357 is a reply to message #318355] Tue, 06 May 2008 09:52 Go to previous messageGo to next message
mbrizza
Messages: 6
Registered: May 2008
Junior Member
   WS_TABLEDATA:=	
   'INSERT INTO DWAPLC01	
        (RECRD_ID_N, APLIC_N, CUST_N, APLIC_D, CRPROV_C,   CLASS_C, ACNT_N, TURNOVR_A, ASSET_VALU_A, MTHLY_GROS_INCOM_A, CREDT_A , TERM_MM, DECISION_D, DECISION_C, CREDT_PURPOS_C, EMERG_TYP_C, IN_NCA_Y, TRGT_SOURC_N, PROD_N, JURISTIC_Y, NON_RES_Y, RACE_C, ID_TYPE_C, ID_N,GEND_C)                                    		
           VALUES (WS_RECRD_ID_N,  
                  WS_APLIC_N,     
                  WS_CUST_N,                 
                  WS_APLIC_D,     
                  WS_CRPROV_C,    
                  WS_CLASS_C,     
                  WS_ACNT_N,      
                  WS_TURNOVR_A,              
                  WS_ASSET_VALU_A,           
                  WS_MTHLY_GROS_INCOM_A,     
                  WS_CREDT_A,                
                  WS_TERM_MM,                
                  WS_DECISION_D,  
                  WS_DECISION_C,  
                  WS_CREDT_PURPOS_C,         
                  WS_EMERG_TYP_C,            
                  WS_IN_NCA_Y,              
                  WS_TRGT_SOURC_N,           
                  WS_PROD_N,       
                  WS_JURISTIC_Y,   
                  WS_NON_RES_Y,    
                  WS_RACE_C,       
                  WS_ID_TYPE_C,    
                  WS_ID_N,         
                  WS_GEND_C)';      
		   
Re: Creating a Report.... [message #318358 is a reply to message #318357] Tue, 06 May 2008 09:54 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
That's not it: you just assign a string to a VARCHAR2-variable.
The content of the string resembles an INSERT statement.
Re: Creating a Report.... [message #318526 is a reply to message #318358] Wed, 07 May 2008 01:52 Go to previous message
mbrizza
Messages: 6
Registered: May 2008
Junior Member
I think that's where i confuse myself - i am thinking COBOL in SQL.....
Previous Topic: Prolem in executing the code.
Next Topic: Handle # character
Goto Forum:
  


Current Time: Tue Dec 06 13:58:57 CST 2016

Total time taken to generate the page: 0.09934 seconds