Thanks Andy for your reply.
I will try to simply my question: Below are 5 different tables which user access. Now aim is to have a single view with all combined data from different tables so that user doesnt have to query so many different tables. Mapping is not an issue.
NOte: I have limited the num of rows to only 3. Also number of columns can be more.
| 1 | SCHEMANAME:SchemaX, TABLENAME:TAB_G | ||||||||
| UID | COL1 | COl2 | TEXT | ||||||
| 1 | 127 | some textA | Comments1 | ||||||
| 2 | 453 | some textB | Comments2 | ||||||
| 3 | 234 | some textC | Comments3 | ||||||
| 2 | SCHEMANAME:SchemaY, TABLENAME:TAB_G | ||||||||
| UID | COL1 | COl2 | TEXT | ||||||
| 101 | 11 | some textX | Comments44 | ||||||
| 102 | 22 | some textY | Comments5 | ||||||
| 103 | 57 | some textZ | Comments62 | ||||||
| 3 | SCHEMANAME:SchemaZ, TABLENAME:TAB_G | ||||||||
| UID | COL1 | COl2 | TEXT | ||||||
| 21 | 48 | some text4 | Comments17 | ||||||
| 22 | 23 | some text5 | Comments80 | ||||||
| 23 | 10 | some text6 | Comments9 | ||||||
| 4 | SCHEMANAME:SchemaM, TABLENAME:TAB_MXM | ||||||||
| COLID | COLX | COLY | COLZ | COLN | COLM | TEXTCOL | |||
| 1301 | txtP | 9XUN | datetime | 1001 | some text | CommentsX | |||
| 1302 | txtQ | PS8J | date | 1010 | txt | CommentsY | |||
| 1303 | txtR | LKS7 | som date | 3367 | text | Comments22 | |||
| 5 | SCHEMANAME:SchemaM, TABLENAME:TAB_SBR | ||||||||
| MODID | COLA | COLB | COLC | COLH | COLP | COLV | COLS | COMMENTS | |
| 91 | 121 | text33 | dateval1 | 1001 | some text | Y | 0 | CommentsA | |
| 92 | 465 | text12 | dateval2 | 1010 | txt | N | 1 | Comments0 | |
| 93 | 837 | text64 | dateval3 | 3367 | text | N | 1 | CommentsM | |
| Expected View to have following structure | |||||||||
| TYPE | ID | DETAILS | MESSAGE | ||||||
| SchemaX-TAB_G | 1 | COL1=127; COL2=some textA | Comments1 | ||||||
| SchemaX-TAB_G | 2 | COL1=453; COL2=some textB | Comments2 | ||||||
| SchemaX-TAB_G | 3 | COL1=234; COl2=some textC | Comments3 | ||||||
| SchemaY-TAB_G | 101 | COl1=11; COl2=some textX | Comments44 | ||||||
| SchemaY-TAB_G | 102 | COL1=22; COL2=some textY | Comments5 | ||||||
| SchemaY-TAB_G | 103 | COL1=57; COL2=some textZ | Comments62 | ||||||
| SchemaZ-TAB_G | 21 | COL1=48; COL2=some text4 | Comments17 | ||||||
| SchemaZ-TAB_G | 22 | COL1=23; COL2=some text5 | Comments80 | ||||||
| SchemaZ-TAB_G | 23 | COL1=10; COL2=some text6 | Comments9 | ||||||
| SchemaM-TAB_MXM | 1301 | COLY=9XUN; COLN=1001 | CommentsX | ||||||
| SchemaM-TAB_MXM | 1302 | COLY=PS8J; COLN=1010 | CommentsY | ||||||
| SchemaM-TAB_MXM | 1303 | COLY=LKS7; COLN=3367 | Comments22 | ||||||
| SchemaM-TAB_SBR | 91 | COLA=121; COLV=Y | CommentsA | ||||||
| SchemaM-TAB_SBR | 92 | COLA=465; COLV=N | Comments0 | ||||||
| SchemaM-TAB_SBR | 93 | COLA=837; COLV=N | CommentsM | ||||||
If there would have been only 5 tables , then I would have used 4 UNION ALL to combine them with all necessary mapping done easily.
The only issue is Table TAB_G can be in 3 or more different schemas and hence question how many UNION_ALL to use for them and how to use it?
Table 4 and 5 are fixed and wont change. All their mappings can be easily taken care.
-Thanks