I have the following query:
Select DISTINCT * From
(
SELECT "WORK_CENTER"."EQNO" AS E1,
"WORK_CENTER"."CNTR_TYPE",
"WORK_CENTER"."CNTR_DESC",
"WORK_CENTER"."MFGCELL",
"WORK_CENTER"."MFG_TYPE",
"WORK_CENTER"."CUSER1",
"WORK_CENTER"."CUSER2",
"WORK_CENTER"."CUSER3",
"WORK_CENTER"."CUSER4",
"WORK_CENTER"."CUSER5",
"WORK_CENTER"."NUSER1",
"WORK_CENTER"."NUSER2",
"WORK_CENTER"."NUSER3",
"WORK_CENTER"."NUSER4",
"WORK_CENTER"."NUSER5",
"UD_DATA"."CUSER"
FROM "IQMS"."UD_DATA" "UD_DATA"
FULL OUTER JOIN "IQMS"."WORK_CENTER" "WORK_CENTER" ON "UD_DATA"."PARENT_ID"="WORK_CENTER"."ID"
LEFT OUTER JOIN "IQMS"."UD_COLS" "UD_COLS" ON "UD_DATA"."UD_COLS_ID" = "UD_COLS"."ID"
WHERE "WORK_CENTER"."MFG_TYPE"='INJECTION' AND "UD_COLS"."ID"='16'),
(SELECT
"WORK_CENTER"."EQNO" AS E2,
"UD_DATA"."CUSER" AS "U2"
FROM "IQMS"."UD_DATA" "UD_DATA"
FULL OUTER JOIN "IQMS"."WORK_CENTER" "WORK_CENTER" ON "UD_DATA"."PARENT_ID"="WORK_CENTER"."ID"
LEFT OUTER JOIN "IQMS"."UD_COLS" "UD_COLS" ON "UD_DATA"."UD_COLS_ID" = "UD_COLS"."ID"
WHERE "UD_COLS"."ID"='17') ORDER BY E1,E2
E2 is something I tried to use to organise the data, I don't want this column to show. This is the desired result(not showing blank columns and apologies for badly formatted table):
EQNO | CNTR_TYPE | CNTR_DESC | MFGCELL | MFG_TYPE | CUSER | U2
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test1
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test2
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test3
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test4
But what I'm getting is:
EQNO | CNTR_TYPE | CNTR_DESC | MFGCELL | MFG_TYPE | CUSER | U2 | E2
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test1 | 001
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test2 | 002
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test3 | 003
001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test4 | 005
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test1 | 001
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test2 | 002
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test3 | 003
002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test4 | 005
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test1 | 001
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test2 | 002
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test3 | 003
003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test4 | 005
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test1 | 001
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test2 | 002
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test3 | 003
005 | 150T-45MM | DEMAG EXTRA 500-610 | MOLDING | INJECTION | 4000 | test4 | 005
Union doesn't really work as it just puts the two columns "CUSER" and "CUSER AS U2" together since they are the same column really.
What can I do to fix this and/or is there something I'm doing very wrong?(I guess that would be most likely)
Even just being told what to look at would be extremely helpful. I've been working on this for a couple of days and not making much progress.
The crossproduct of those two selects is created. The results are handled like independent tables. I deleted because I could not elaborate more. If it was helpfull, great.