I need to output a flat file in sql plus and visual basic , the information is coming from oracle, where the files looks as follow's;
v.12 1800123456789 gibbons alex a test test
the v.12 has to automatically come in while the rest of the information is coming from tables. but they must be specific positions and lengths. for example the above information 1800123456789, would be 18 is code for employee followed by padded 00 infront of 9 digit social, followed by 2 spaces, last name coming from table. please help
terribly confused.
to do this it looks like you can use the split function in VB6.
Private Sub Command1_Click()
Dim s As String
Dim sS() As String
s = "aaa bbb ccc ddd"
sS() = Split(s, " ")
MsgBox sS(0)
MsgBox sS(1)
MsgBox sS(2)
MsgBox sS(3)
End Sub
I used a space as the field delimiter. You can output the flat file with any delimiter you want.
Alex,
I do this kind of thing in SQL*Plus all the time. It's not diifcult, but tedious.
SPOOL XYZ.txt
SELECT 'v.12' || LPAD('18',3)
||LPAD(ssn,11,'0')
||' '||last_name
FROM ...
SPOOL OFF
You can use LPAD, RPAD, LENGTH, and all the other SQL functions to format each piece to exectly how you need it.
James
Yet another question, and an example of my code.
i have pulled some of the information that i need for the flat file by connecting to 3 tables, then sending the information i have chosen from the table into columns.
i need to connect to another 2set of tables to add further information to my first record. how can i do a select statement within a select statement; example below;
Thank you for your help, just to make sure i am in the right track could you just please see if i go the right idea.
my code so far for the flat file is set as such;
set echo off;
column version format a4;
column description format a5;
column relationship format a2;
column ssn1 format a11;
column ssn2 format a11;
column lastname format a26;
column firstname format a26;
SELECT a.relat_id relationship, a.ssn_id ssn1,
a.ssn_id ssn2, b.last_name lastname,
c.first_name firstname,
from Tbl1 a, Tbl2 b, Tbl3 c
where a.empl_id = b.empl_id
and b.empl_id = c.empl_id
and c.empl_id = a.empl_id.
this will produce something like this;
wife 123456789 123456789 lastname firstname
after first name i need to pull another set of information from another select statement how can i add the new select staement to what i already have above so it can pull the info right after first name.
Here is updated code: the **** represent new lines added to your code
set echo off;
column version format a4;
column description format a5;
column relationship format a2;
column ssn1 format a11;
column ssn2 format a11;
column lastname format a26;
column firstname format a26;
****
add the additional columns you need here. Then add:
****
SELECT table1.*, table2.*
from ( ****
SELECT a.relat_id relationship, a.ssn_id ssn1,
a.ssn_id ssn2, b.last_name lastname,
c.first_name firstname,
from Tbl1 a, Tbl2 b, Tbl3 c
where a.empl_id = b.empl_id
and b.empl_id = c.empl_id
and c.empl_id = a.empl_id
****
) table1,
****
(Add 2nd Select statement in here) table2
Make sure table1 is joined to table2 with a where clause (or you will get way too many rows (rowcount in table1*rowcount in table2).
I would use OZEXE Lite not VB. (http://www.download.com or http://www.ozdevelopment.com )
Add an ODBC device interface (Dev1) to connect to Oracle
Then the code is as simple as:
DevOpen( Dev1 )
nRowCount = DevWrite( Dev1, "SELECT Code, SSN, First, Last FROM employees" )
nCurRow = 1
nOutFile = fOpen( "c:oufile.txt", "w" )
While nCurRow
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 6  Replies