Can this be done using only sql? Programmers please advise. 1

[gtarrant]

I've been handed a project at work and I don't know if this can be done using only sql - some tell me that you have to use an SQR to do this.

The project is an electronic file feed for one of our vendors. The problem is that the file layout has a header record, 3 detail records per employee and a trailor record. I can't get around the problem of each row having different data elements. The output would look something like this:

row1: RECORD_TYPE, PROCESS_DATE, CUST_NUM, CUST_NAME
row2: RECORD_TYPE, EMPLOYEE_ID, EMPLOYEE_NAME, COUNTRY_CODE
row3: RECORD_TYPE, EMPLOYEE_ID, PRODUCT, POLICY
row4: RECORD_TYPE, EMPLOYEE_ID, DEDUCTION_TYPE, DEDUCTION
row5: RECORD_TYPE, CUSTOMER_NUM, TOTAL_COVERAGE_RECORDS

Is this possible using only sql? I'm a novice, so if there is any simple examples that you can give would be greatly appreciated.

 

[SantaMufasa]

G,

There exist many ways to resolve your need. Before I suggest a specific resolution method, please post what you want done with the data residing in your vendor feed. Do you want the feed data to become table data, or what? If so, what are the formats of the target tables?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:08 (22Jun04) UTC (aka "GMT" and "Zulu"), 10:08 (22Jun04) Mountain Time)

 

[gtarrant]

Thanks for your quick response, SantaMufasa.

The output will be a file type TXT. From there, we would either zip it or use PGP to send it to the vendor.

 

[SantaMufasa]

G,

A straightforward solution exists using PL/SQL. Without having your source-table formats, it is a bit difficult to show you specifically how to do it, so please post your source formats and some sample data. Specifically, post your table rows that represent at least one full iteration of output. Also, I presume there are five different record_types. What are the record types?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:39 (22Jun04) UTC (aka "GMT" and "Zulu"), 10:39 (22Jun04) Mountain Time)

 

[gtarrant]

Sorry about that, I've left out alot of info in my above example. I'm actually working on Oracle 8i and the database is very simular to PeopleSoft called ADP HRMS. Since there are a ton of tables out there, for this example lets say I'm using only 3 tables to gather the info.

PS_PERSONAL_DATA
EMPLOYEE_NAME VARCHAR2(40) NOT NULL,
EMPLOYEE_ID VARCHAR2(7) NOT NULL,
COUNTRY_CODE VARCHAR2(3) NOT NULL

PS_CUSTOMER_TBL
CUST_NUM VARCHAR2(6) NOT NULL,
CUST_NAME VARCHAR2(40) NOT NULL

PS_BENEFIT_TBL
PRODUCT VARCHAR2(10) NOT NULL,
POLICY VARCHAR2(40) NOT NULL,
EMPLOYEE_ID VARCHAR2(7) NOT NULL,
DEDUCTION_TYPE VARCHAR2(20) NOT NULL,
DEDUCTION NUMBER(6,2) NOT NULL

row1: RECORD_TYPE, PROCESS_DATE, CUST_NUM, CUST_NAME
row2: RECORD_TYPE, EMPLOYEE_ID, EMPLOYEE_NAME, COUNTRY_CODE
row3: RECORD_TYPE, EMPLOYEE_ID, PRODUCT, POLICY
row4: RECORD_TYPE, EMPLOYEE_ID, DEDUCTION_TYPE, DEDUCTION
row5: RECORD_TYPE, CUST_NUM, TOTAL_COVERAGE_RECORDS

The output should look like this:

01 22-JUNE-2004 100001 WIDGET_INC
02 1212270 SMITH,JOHN USA
03 1212270 UNUM FMLA
04 1212270 BEFORE_TAX $5.00
02 5555555 BROWN,TIM USA
03 5555555 UNUM FMLA
04 5555555 BEFORE_TAX $10.00
02 7777777 JONES,BILL USA
03 7777777 UNUM FMLA
04 7777777 BEFORE_TAX $15.00
05 100001 3

Hope this helps. Thanks again.

 

[SantaMufasa]

G,

Just one more issue: Could you please confirm that PS_PERSONAL_DATA has an additional column, CUST_NUM? Without it, I cannot see how you are linking Employees to Customers.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:34 (22Jun04) UTC (aka "GMT" and "Zulu"), 11:34 (22Jun04) Mountain Time)

 

[gtarrant]

Yes, it would have that field also.

 

[SantaMufasa]

G,

Here is your PL/SQL code:

declare
	outfile	utl_file.file_type;
	cnt	number;
	procedure wrt (x varchar2) is
	begin
		utl_file.put_line(outfile,x);
	end;
begin
	outfile	:= utl_file.fopen(	location => 'D:\dhunt\sqldba',
					filename => 'gtarrant.txt',
					open_mode => 'W');
	for c in (select * from ps_customer_tbl order by cust_num) loop
		wrt('01 '||to_char(sysdate,'fmdd-MONTH-yyyy')||' '||c.cust_num||' '||c.cust_name);
		cnt	:= 0;
		for e in (select EMPLOYEE_NAME
				,e.EMPLOYEE_ID
				,COUNTRY_CODE
				,PRODUCT
				,POLICY
				,DEDUCTION_TYPE
				,DEDUCTION
				from	 PS_PERSONAL_DATA e
					,PS_BENEFIT_TBL b
				where e.employee_id = b.employee_id
				order by e.employee_id) loop
			wrt('02 '||e.employee_id||' '||e.employee_name||' '
                          ||e.country_code);
			wrt('03 '||e.employee_id||' '||e.product||' '||e.policy);
			wrt('04 '||e.employee_id||' '||e.deduction_type||' '||
				trim(to_char(e.deduction,'$99,990.00')));
			cnt	:= cnt+1;
		end loop;
		wrt('05 '||c.cust_num||' '||cnt);
	end loop;
	utl_file.fclose(outfile);
end;
/
prompt
prompt Wrote to file 'D:\dhunt\sqldba\gtarrant.txt'
prompt

Wrote to file 'D:\dhunt\sqldba\gtarrant.txt'

(Contents of output flat file, 'D:\dhunt\sqldba\gtarrant.txt')

01 22-JUNE-2004 100001 WIDGET_INC
02 1212270 SMITH,JOHN USA
03 1212270 UNUM FMLA
04 1212270 BEFORE_TAX $5.00
02 5555555 BROWN,TIM USA
03 5555555 UNUM FMLA
04 5555555 BEFORE_TAX $10.00
02 7777777 JONES,BILL USA
03 7777777 UNUM FMLA
04 7777777 BEFORE_TAX $15.00
05 100001 3

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:32 (22Jun04) UTC (aka "GMT" and "Zulu"), 12:32 (22Jun04) Mountain Time)

 

[gtarrant]

I am forever in your debt. Thank you!!!! It works GREAT!