AnsweredAssumed Answered

Need SQL/ Database Help for an outbound Database Integration scenario.

Question asked by vikashpradhan_boomiteam129109 on Jan 29, 2018

Need SQL Help for an outbound Database Integration scenario. The scenario contains 3 tables interlinked by a common field from the Header Table passed two subsequent tables as foreign keys.

 

Table Name: Field Names…

TabHeader: PK_Header, Header_Flag, HField1, HField2, HField3….

TabUsage: PK_Usage, FK_PK_Header, UField1, UField2, UField3....

TabDetails: PK_Details, FK_PK_Header, DField1, DField2, DField3….

 

The PK_Header is the Primary Key of the Header Table and that value is passed to Usage Tale and Details Table as Foreign Key and is the Linking criteria for all three fields.

 

The Relationship Between the Tables is as follows:

 

For one Header record in Header Table, there are multiple respective records in the Usage Table linked by the field: FK_PK_Header.

 

For one Header record in Header Table, there are multiple respective records in the Detail Table linked by the field: FK_PK_Header.

 

A Sample Table: 

TabHeader

28|^|1|^|000805307422016102800|^|PR|^|00|^|150|^|151| #|

29|^|0|^|000805307422016102801|^|PR|^|00|^|150|^|151| #|

30|^|0|^|000805307422016102802|^|PR|^|00|^|150|^|151| #|

 

TabUsage

19|^|28|^|20180123|^|20180123|^|BO|#|

20|^|29|^|20180123|^|20180125|^|BO|#|

21|^|29|^|20180123|^|20180125|^|AA|#|

22|^|30|^|20180125|^|20180125|^|AA|#|

23|^|30|^|20180125|^|20180125|^|BO|#|

 

TabDetails

100|^|28|^|20180123|^|224157|^|N|^|EU|^|ENC001|^|1.041|#|

101|^|28|^|20180123|^|224157|^|N|^|EU|^|ENC001|^|2.851|#|

102|^|28|^|20180123|^|031150|^|N|^|EU|^|ENC001|^|51.58|#|

103|^|28|^|20180123|^|031150|^|N|^|EU|^|ENC001|^|19.50|#|

104|^|28|^|20180123|^|031151|^|N|^|EU|^|ENC001|^|12.50|#|

106|^|29|^|20180123|^|030318|^|N|^|EU|^|ENC004|^|10.00|#|

107|^|29|^|20180123|^|030319|^|N|^|EU|^|ENC006|^|25.00|#|

108|^|29|^|20180123|^|030319|^|N|^|EU|^|ENC005|^|25.00|#|

109|^|29|^|20180123|^|025800|^|N|^|EU|^|ECTS11|^|10.00|#|

110|^|29|^|20180123|^|030319|^|N|^|EU|^|DMD013|^|20.00|#|

111|^|29|^|20180123|^|030319|^|N|^|EU|^|DMD012|^|22.50|#|

112|^|30|^|20180123|^|030319|^|N|^|EU|^|ENC011|^|20.00|#|

113|^|30|^|20180123|^|030319|^|N|^|EU|^|ENC010|^|50.00|#|

114|^|30|^|20180123|^|030319|^|N|^|EU|^|ENC009|^|50.00|#|

115|^|30|^|20180123|^|025913|^|N|^|EU|^|ECTW11|^|20.00|#|

116|^|30|^|20180123|^|030319|^|N|^|EU|^|DMD017|^|40.00|#|

118|^|30|^|20180123|^|030319|^|N|^|EU|^|DMD016|^|44.50|#|

 


 

Is it possible to extract to data into 1 Database profile like below?

 

The conditions being the profile should contain for one instance 1 header record (Queried for 1 Header Primary key record where Header Flag in Header Table = 0) and the corresponding records from the Usage and Detail tables based on the Foreign Key which is the primary key of the Header Table.

 

The output expected for the above result is:

 

28|^|1|^|000805307422016102800|^|PR|^|00|^|150|^|151| #| - Should be rejected as Header_Flag not equal to 0.

 

Ist Output:

TabHeader

29|^|0|^|000805307422016102801|^|PR|^|00|^|150|^|151| #|

TabUsage

20|^|29|^|20180123|^|20180125|^|BO|#|

21|^|29|^|20180123|^|20180125|^|AA|#|

TabDetails

106|^|29|^|20180123|^|030318|^|N|^|EU|^|ENC004|^|10.00|#|

107|^|29|^|20180123|^|030319|^|N|^|EU|^|ENC006|^|25.00|#|

108|^|29|^|20180123|^|030319|^|N|^|EU|^|ENC005|^|25.00|#|

109|^|29|^|20180123|^|025800|^|N|^|EU|^|ECTS11|^|10.00|#|

110|^|29|^|20180123|^|030319|^|N|^|EU|^|DMD013|^|20.00|#|

111|^|29|^|20180123|^|030319|^|N|^|EU|^|DMD012|^|22.50|#|

 

2nd Output:

TabHeader

30|^|0|^|000805307422016102802|^|PR|^|00|^|150|^|151| #|

TabUsage

22|^|30|^|20180125|^|20180125|^|AA|#|

23|^|30|^|20180125|^|20180125|^|BO|#|

TabDetails

112|^|30|^|20180123|^|030319|^|N|^|EU|^|ENC011|^|20.00|#|

113|^|30|^|20180123|^|030319|^|N|^|EU|^|ENC010|^|50.00|#|

114|^|30|^|20180123|^|030319|^|N|^|EU|^|ENC009|^|50.00|#|

115|^|30|^|20180123|^|025913|^|N|^|EU|^|ECTW11|^|20.00|#|

116|^|30|^|20180123|^|030319|^|N|^|EU|^|DMD017|^|40.00|#|

118|^|30|^|20180123|^|030319|^|N|^|EU|^|DMD016|^|44.50|#|

Outcomes