Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chuck_17
Starting Member
3 Posts |
 Posted - 2011-01-27 : 11:17:39
|
| Is it possible to fetch from a table that has more than one format? If so how? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-27 : 11:20:48
|
| >> a table that has more than one formatwhat does that mean?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
chuck_17
Starting Member
3 Posts |
Posted - 2011-01-27 : 11:41:53
|
| Here is an example- Creating a logical file with more than one record formatA multiple format logical file lets you use related records from two or more physical files by referring to only one logical file. Each record format is always associated with one or more physical files. You can use the same physical file in more than one record format.The following shows the DDS for a physical file, ORDDTLP, built from a field reference file:|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A* ORDER DETAIL FILE (ORDDTLP) - PHYSICAL FILE RECORD DEFINITION A REF(DSTREF) A R ORDDTL TEXT('Order detail record') A CUST R A ORDER R A LINE R A ITEM R A QTYORD R A DESCRP R A PRICE R A EXTENS R A WHSLOC R A ORDATE R A CUTYPE R A STATE R A ACTMTH R A ACTYR R AThe following example shows the DDS for the physical file ORDHDRP:|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A* ORDER HEADER FILE (ORDHDRP) - PHYSICAL FILE RECORD DEFINITION A REF(DSTREFP) A R ORDHDR TEXT('Order header record') A CUST R A ORDER R A ORDATE R A CUSORD R A SHPVIA R A ORDSTS R A OPRNME R A ORDMNT R A CUTYPE R A INVNBR R A PRTDAT R A SEQNBR R A OPNSTS R A LINES R A ACTMTH R A ACTYR R A STATE R AThe following example shows how to create a logical file ORDFILL with two record formats. One record format is defined for order header records from the physical file ORDHDRP; the other is defined for order detail records from the physical file ORDDTLP.The logical file record format ORDHDR uses one key field, Order, for sequencing; the logical file record format ORDDTL uses two keys fields, Order and Line, for sequencing.The following example shows the DDS for the logical file ORDFILL.|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A* ORDER TRANSACTION LOGICAL FILE (ORDFILL) A R ORDHDR PFILE(ORDHDRP) A K ORDER A A R ORDDTL PFILE(ORDDTLP) A K ORDER A K LINE ATo create the logical file ORDFILL with two associated physical files, use a Create Logical File (CRTLF) command like the following:CRTLF FILE(DSTPRODLB/ORDFILL) TEXT('Order transaction logical file') |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-27 : 11:55:11
|
| That's a file. If you were holding data in a database it would be in two tables - one for the header and one for the detail==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chuck_17
Starting Member
3 Posts |
Posted - 2011-01-27 : 12:03:44
|
| Yes got it so we need to query on all the tables using union or joinThank you |
|
|
|
|
|
|
Fetch from table with more than one format