Creating a flat file


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Creating a flat file

  1. #1
    SUE Guest

    Creating a flat file


    Hi,
    I would really appreciate it if someone could help me create a flat file
    from a couple of relational tables.
    Emp_Info = Employee_id along with name & address info
    Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate (employee's
    children)

    I need to create one record per Employee that would contain some employee
    info along with the names & birthdates of their children.

    Would I create a Table_emp_dep in the format that I need, load it with the
    Emp_Info data first and then update it with the Emp_Depend data that I need?


    Emp_id
    Emp_last
    Emp_first
    Child1_lastname
    Child1_firstname
    Child1_birthdate
    Child2_lastname
    Child2_firstname
    Child2_birthdate ....and so on for as the most children an employee has.

    In sql, how do I set it up to loop through the Emp_Depend file and choose
    the records that I need and write them to the specific fields that they correspond
    to?

    If this is the correct approach, please include example of actual code that
    works.

    Thank in advance,
    Sue

  2. #2
    Porkstone Guest

    Re: Creating a flat file

    Sue,
    Forgive me if I have misunderstood, but I think what you're looking for
    is to perform a join on the two tables to create a superset of records. The
    code for this would look something like this. If you are already familiar
    with SQL you may find the following explanation a little bit condescending.

    SELECT * FROM Emp_Info AS EI
    INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id

    --
    You can replace the * with the names of the fields you want in your 'flat
    file', to retrieve the last name from the Emp_Depend table you would put.

    SELECT ED.lastname FROM Emp_Info AS EI
    INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
    --
    The 'ED.' prefix is an alias for the table, this is created in the join
    expression. If you wish to include more fields simply place comma in between
    each one and remember to use the correct alias.

    Hope this is some use to you.

    P


    > Hi,
    > I would really appreciate it if someone could help me create a flat file
    > from a couple of relational tables.
    > Emp_Info = Employee_id along with name & address info
    > Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate

    (employee's
    > children)
    >
    > I need to create one record per Employee that would contain some employee
    > info along with the names & birthdates of their children.
    >
    > Would I create a Table_emp_dep in the format that I need, load it with the
    > Emp_Info data first and then update it with the Emp_Depend data that I

    need?
    >
    >
    > Emp_id
    > Emp_last
    > Emp_first
    > Child1_lastname
    > Child1_firstname
    > Child1_birthdate
    > Child2_lastname
    > Child2_firstname
    > Child2_birthdate ....and so on for as the most children an employee has.
    >
    > In sql, how do I set it up to loop through the Emp_Depend file and choose
    > the records that I need and write them to the specific fields that they

    correspond
    > to?
    >
    > If this is the correct approach, please include example of actual code

    that
    > works.
    >
    > Thank in advance,
    > Sue




  3. #3
    SUE Guest

    Re: Creating a flat file


    Thanks for the response Porkstone but I need to return one long record for
    the multiple join in the dependent table. The final outcome needs to look
    as follows:

    Emp_ID Emp_last Dep_1 Dep1_first Dep_2 Dep2_first Dep_3 Dep3_first

    1000 Smith 1 Jane 2 Lucy 3 John
    1002 Jones 1 Paul 2 Ben (3 could be blank)
    1003 McDonald (They may have no matches)


    It is a superset that I need but it can't be multiple records for each employee
    as the result...unless I'm doing something wrong I end up with the following:

    Emp_ID Emp_last Dep_No Dep_first
    1000 Smith 1 Jane
    1000 Smith 2 Lucy
    1000 Smith 3 John
    1002 Jones 1 Paul
    1002 Jones 2 Ben
    1003 McDonald

    Thanks,
    Sue

    "Porkstone" <Porkstoner@Hotmail.com> wrote:
    >Sue,
    > Forgive me if I have misunderstood, but I think what you're looking

    for
    >is to perform a join on the two tables to create a superset of records.

    The
    >code for this would look something like this. If you are already familiar
    >with SQL you may find the following explanation a little bit condescending.
    >
    >SELECT * FROM Emp_Info AS EI
    >INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
    >
    >--
    >You can replace the * with the names of the fields you want in your 'flat
    >file', to retrieve the last name from the Emp_Depend table you would put.
    >
    >SELECT ED.lastname FROM Emp_Info AS EI
    >INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
    >--
    >The 'ED.' prefix is an alias for the table, this is created in the join
    >expression. If you wish to include more fields simply place comma in between
    >each one and remember to use the correct alias.
    >
    >Hope this is some use to you.
    >
    >P
    >
    >
    >> Hi,
    >> I would really appreciate it if someone could help me create a flat file
    >> from a couple of relational tables.
    >> Emp_Info = Employee_id along with name & address info
    >> Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate

    >(employee's
    >> children)
    >>
    >> I need to create one record per Employee that would contain some employee
    >> info along with the names & birthdates of their children.
    >>
    >> Would I create a Table_emp_dep in the format that I need, load it with

    the
    >> Emp_Info data first and then update it with the Emp_Depend data that I

    >need?
    >>
    >>
    >> Emp_id
    >> Emp_last
    >> Emp_first
    >> Child1_lastname
    >> Child1_firstname
    >> Child1_birthdate
    >> Child2_lastname
    >> Child2_firstname
    >> Child2_birthdate ....and so on for as the most children an employee has.
    >>
    >> In sql, how do I set it up to loop through the Emp_Depend file and choose
    >> the records that I need and write them to the specific fields that they

    >correspond
    >> to?
    >>
    >> If this is the correct approach, please include example of actual code

    >that
    >> works.
    >>
    >> Thank in advance,
    >> Sue

    >
    >



  4. #4
    Dennis Guest

    Re: Creating a flat file


    Denormalizing data can be more work than normalizing it. I can think of 3
    solutions, all of which require some knowledge and work.

    You identified the first, an Insert and a series of Updates into a denormalized
    table. This is an abbreviate form.

    INSERT into Table_emp_dep (Emp_ID, Emp_last)
    SELECT Emp_ID, Emp_last from Emp_info

    UPDATE Table_emp_dep
    SET Dep1_first = Emp_Depend.firstname
    FROM Emp_Depend
    WHERE Emp_Depend.Emp_ID = Table_emp_dep.Emp_ID
    AND Emp_Depend.Dep_No = 1

    UPDATE Table_emp_dep
    SET Dep2_first = Emp_Depend.firstname
    FROM Emp_Depend
    WHERE Emp_Depend.Emp_ID = Table_emp_dep.Emp_ID
    AND Emp_Depend.Dep_No = 2

    I don't think you'll be able to have all those in a single transaction without
    locking problems. This is straightforward and should work.

    The second takes a different approach. Briefly, you declare variables for
    all the columns you want to insert into the denormalized table. Then you
    declare a cursor to walk through all the employees. Within that cursor loop,
    you declare another cursor to walk through all the dependents for a single
    employee. Each time you load all the column data into the variables. At the
    bottom of the employee loop you Insert the row into the denormalized table
    using all the variables for the column values.

    The third method uses something I know as "linguistic optimization" and would
    use a single SELECT and a single INSERT. The SQL would take a while to work
    out, but it uses functions and the GROUP BY to compress all the rows you
    identified below into a single row per employee.

    Dennis

    "SUE" <susan.klick@po.state.ct.us> wrote:
    >
    >Thanks for the response Porkstone but I need to return one long record for
    >the multiple join in the dependent table. The final outcome needs to look
    >as follows:
    >
    >Emp_ID Emp_last Dep_1 Dep1_first Dep_2 Dep2_first Dep_3 Dep3_first
    >
    >1000 Smith 1 Jane 2 Lucy 3 John
    >1002 Jones 1 Paul 2 Ben (3 could be blank)
    >1003 McDonald (They may have no matches)
    >
    >
    >It is a superset that I need but it can't be multiple records for each

    employee
    >as the result...unless I'm doing something wrong I end up with the following:
    >
    >Emp_ID Emp_last Dep_No Dep_first
    >1000 Smith 1 Jane
    >1000 Smith 2 Lucy
    >1000 Smith 3 John
    >1002 Jones 1 Paul
    >1002 Jones 2 Ben
    >1003 McDonald
    >
    >Thanks,
    >Sue
    >
    >"Porkstone" <Porkstoner@Hotmail.com> wrote:
    >>Sue,
    >> Forgive me if I have misunderstood, but I think what you're looking

    >for
    >>is to perform a join on the two tables to create a superset of records.

    >The
    >>code for this would look something like this. If you are already familiar
    >>with SQL you may find the following explanation a little bit condescending.
    >>
    >>SELECT * FROM Emp_Info AS EI
    >>INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
    >>
    >>--
    >>You can replace the * with the names of the fields you want in your 'flat
    >>file', to retrieve the last name from the Emp_Depend table you would put.
    >>
    >>SELECT ED.lastname FROM Emp_Info AS EI
    >>INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
    >>--
    >>The 'ED.' prefix is an alias for the table, this is created in the join
    >>expression. If you wish to include more fields simply place comma in between
    >>each one and remember to use the correct alias.
    >>
    >>Hope this is some use to you.
    >>
    >>P
    >>
    >>
    >>> Hi,
    >>> I would really appreciate it if someone could help me create a flat file
    >>> from a couple of relational tables.
    >>> Emp_Info = Employee_id along with name & address info
    >>> Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate

    >>(employee's
    >>> children)
    >>>
    >>> I need to create one record per Employee that would contain some employee
    >>> info along with the names & birthdates of their children.
    >>>
    >>> Would I create a Table_emp_dep in the format that I need, load it with

    >the
    >>> Emp_Info data first and then update it with the Emp_Depend data that

    I
    >>need?
    >>>
    >>>
    >>> Emp_id
    >>> Emp_last
    >>> Emp_first
    >>> Child1_lastname
    >>> Child1_firstname
    >>> Child1_birthdate
    >>> Child2_lastname
    >>> Child2_firstname
    >>> Child2_birthdate ....and so on for as the most children an employee has.
    >>>
    >>> In sql, how do I set it up to loop through the Emp_Depend file and choose
    >>> the records that I need and write them to the specific fields that they

    >>correspond
    >>> to?
    >>>
    >>> If this is the correct approach, please include example of actual code

    >>that
    >>> works.
    >>>
    >>> Thank in advance,
    >>> Sue

    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center