Visual basic project , outer joins, flat files, sql commands

pts.
Tags:
SQL
Visual Basic
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I don’t know whether this helps??

insert into output_table (select ‘v1.12′, emp concat ’00′ concat social concat ‘ ‘ concat name from input_table)

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • WebCodeKid
    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.
    0 pointsBadges:
    report
  • Jwhanon
    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
    0 pointsBadges:
    report
  • Paralogist
    How about checking out this? http://builder.com.com/5100-6388_14-5259821.html
    0 pointsBadges:
    report
  • Alex8809
    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.
    0 pointsBadges:
    report
  • Dmcdconsult
    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).
    40 pointsBadges:
    report
  • Wizard90
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following