locked
Generate a Flat File from two View in Oracle DB RRS feed

  • Question

  • Hello Experts,

    I have the two views in the Oracle Database one for the member and one for the benefit which looks like


    Each Member/Employee in the Member view has one or more benefits in the Benefits view which looks like this

    The Employee ID 1 has two Benefits records and the Employee ID 14 has 3 benefits records.

    I will have to create a csv file which looks up the benefit records for the employee ID like

    MEMBER,1,134,134,Jo, ,Arch,1,S,M
    BENEFIT,1,M,1,89,7,1,1,20110101,20120731,11
    BENEFIT,1,D,2,89,3,11,5,20110101,20120731,16
    MEMBER,14,448,448,Cli,M,Ast,1,M,M
    BENEFIT,14,D,2,89,3,11,5,20170101,17000101,16
    BENEFIT,14,M,1,89,7,1,1,20170101,17000101,11
    BENEFIT,14,M,1,89,7,1,1,20150101,20161231,11

    I have created schema for the Orcale View using the Consume Adapter service. But not dealt with two view at the same time.How do I approach with this. Any help is greatly appreciated.


    • Edited by vdha Wednesday, December 28, 2016 9:00 PM
    Wednesday, December 28, 2016 5:41 PM

Answers

  • Frst of all do not use the mapper, see sample xslt file bellow

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
    >
      <xsl:key name="benefits" match="benefit" use="employeeID"/>
      <xsl:key name="members" match="member" use="employeeID"/>
      
        <xsl:output method="xml" indent="yes"/>
    
        <xsl:template match="/Root">
          <root>
            <xsl:for-each select="members/member[key('benefits',employeeID)]">
              <record>
              <member>
                <employeeID>
                  <xsl:value-of select="employeeID"/>
                </employeeID>
              </member>
              <xsl:for-each select="key('benefits',employeeID)">
                <benefit>
                  <benefitID>
                  <xsl:value-of select="benefitID"/>
                    </benefitID>
                </benefit>
              </xsl:for-each>
              </record>
            </xsl:for-each>
        </root>
        </xsl:template>
    </xsl:stylesheet>

    • Marked as answer by vdha Wednesday, February 22, 2017 8:18 PM
    Thursday, January 26, 2017 9:47 PM

All replies

  • Experts please help me with this. I am kind of stuck.
    Thursday, December 29, 2016 1:42 AM
  • You can use the Equal Functoid between the Employee_ID Fields.

    This is where I say just try some things to see what works. :)

    Thursday, December 29, 2016 1:55 AM
    Moderator
  • Johns,

    I created the Map with two input schema using the orchestration. I am trying to use equals functoid between the Employee_ID of both the views

    As I have created the output schema in below format

    Now I am not sure how to connect the output of the equal functoid in to the output schema.Please help me with this


    • Edited by vdha Tuesday, January 3, 2017 8:34 PM
    Tuesday, January 3, 2017 6:55 PM
  • Please do help me with this.
    Wednesday, January 4, 2017 2:55 PM
  • Hi Vdha,

    What logic do you wish to implement in the map??


    Mandar Dharmadhikari

    Wednesday, January 4, 2017 3:24 PM
    Moderator
  • I have two views one for member and one for benefits. Each Member can have n number of benefits. Member and Benefits has employee ID field.

    I need to map both the views in to single output file like

    MEMBER,1,134,134,Jo, ,Arch,1,S,M
    BENEFIT,1,M,1,89,7,1,1,20110101,20120731,11
    BENEFIT,1,D,2,89,3,11,5,20110101,20120731,16
    MEMBER,14,448,448,Cli,M,Ast,1,M,M
    BENEFIT,14,D,2,89,3,11,5,20170101,17000101,16
    BENEFIT,14,M,1,89,7,1,1,20170101,17000101,11
    BENEFIT,14,M,1,89,7,1,1,20150101,20161231,11

    I created the output file structure as

    I created the map with input schema as response from two views. Johns suggested to use Equal functoid between EmployeeID field of both the views to map the Member and their benefits. But I am not sure if the output of the equal functoid to the Benefit record of the output schema. Also not sure if I will be using the looping functoid to the benefits as there are 1 to many benefits to each member.

    Wednesday, January 4, 2017 3:44 PM
  • you have to use the value mapping functoid. refer below link

    https://msdn.microsoft.com/en-in/library/ee250680(v=bts.10).aspx


    Mandar Dharmadhikari

    Wednesday, January 4, 2017 4:23 PM
    Moderator
  • Should I use value mapping to each field elements in Benefit Records
    Wednesday, January 4, 2017 4:38 PM
  • I guess, you need a value mapping functoid?

    https://msdn.microsoft.com/en-us/library/ee250680%28v=bts.10%29.aspx?f=255&MSPPError=-2147217396

    PS. Oops, I see you've found it already yourself. Not for each field, purpose of value mapping (joined with logical equal) is to return field only if condition is "true".
    • Edited by Yakov72 Wednesday, January 4, 2017 5:09 PM
    Wednesday, January 4, 2017 5:03 PM
  • You will have to use value mapping only where you want the equal functoid to play the role

    Mandar Dharmadhikari

    Wednesday, January 4, 2017 5:17 PM
    Moderator
  • This is where I am confused. As the elements in the Benefits are mapped from the Benefits vie. But it should be mapped only when the employeeID is equal to Member employeeID. Should I be mapping to all the elements or just the Benefits record.
    Wednesday, January 4, 2017 6:38 PM
  • Frst of all do not use the mapper, see sample xslt file bellow

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
    >
      <xsl:key name="benefits" match="benefit" use="employeeID"/>
      <xsl:key name="members" match="member" use="employeeID"/>
      
        <xsl:output method="xml" indent="yes"/>
    
        <xsl:template match="/Root">
          <root>
            <xsl:for-each select="members/member[key('benefits',employeeID)]">
              <record>
              <member>
                <employeeID>
                  <xsl:value-of select="employeeID"/>
                </employeeID>
              </member>
              <xsl:for-each select="key('benefits',employeeID)">
                <benefit>
                  <benefitID>
                  <xsl:value-of select="benefitID"/>
                    </benefitID>
                </benefit>
              </xsl:for-each>
              </record>
            </xsl:for-each>
        </root>
        </xsl:template>
    </xsl:stylesheet>

    • Marked as answer by vdha Wednesday, February 22, 2017 8:18 PM
    Thursday, January 26, 2017 9:47 PM