Hi, i need to connect two table with multiple keys, the original query that i want to replicate is the following.
select
Tab1.Brand
, Tab2.item as "ITEM"
, nvl(
(
select
SURNAME||' '||NAME
from TAB3
where USERCODE = Tab1.UserInsertCode
and rownum = 1
)
,'N/A'
) as "User Who Created"
,nvl(
(
select
SURNAME||' '||NAME
from TAB3
where USERCODE = Tab1.UserEditedCode
and rownum = 1
)
,'N/A'
) as "User Who Edited"
,Tab2.qty
from TABLE1 Tab1
join TABLE2 Tab2 on (
Tab1.Brand = Tab2.Brand
and Tab1.code = Tab2.code
)
I'm trying to join the two different key of UsertInsertCode and UserEditedCode with the same field of another table. But when i do it i the keys join eachother and the sum is correct, but when i download the excel the sum is much more different from the report. And the problem are theese 2 fields: User Who Created and User Who Edited:
TMP,table: LOAD distinct Brand&'-'&[Document Number] as KeyTab2, Brand&'-'&[Document Creation User] as KeyTab3UserInsertCode, Brand&'-'&[Document Last Update User] as KeyTab3KeyUserEditCode, [Document Number], [Document Creation User], [Document Last Update User], TABLE1 (qvd);
TAB3TabUserInsert:
LOAD
Brand&'-'&[COD USER] as KeyTab3UserInsertCode,
[COD USER],
[NAME USER INSERT],
[SURNAME USER INSERT],
[USER PERMISSION INSERT]
FROM tab3.qvd (qvd)
TAB3TabUserEdit:
LOAD
Brand&'-'&[COD USER] asKeyTab3KeyUserEditCode,
[COD USER],
[NAME USER EDIT],
[SURNAME USER EDIT],
[USER PERMISSION EDIT]
FROM tab3.qvd (qvd)
I create two table of same qvd but with different field to create different keys, to connect the main table who have only one field for this keys. I hope i was clear enough and someone can help me.
TAB3TabUserInsert:
LOAD
Brand&'-'&[COD USER] as KeyTab3UserInsertCode,
[COD USER] as [COD USER INSERT],
[NAME USER INSERT],
[SURNAME USER INSERT],
[USER PERMISSION INSERT] // I have changed to EDIT to INSERT
FROM tab3.qvd (qvd)
TAB3TabUserEdit:
LOAD
Brand&'-'&[COD USER] asKeyTab3KeyUserEditCode,
[COD USER] as [CODE USER EDIT],
[NAME USER EDIT],
[SURNAME USER EDIT],
[USER PERMISSION EDIT]
FROM tab3.qvd (qvd)
