Please let me know the way to get the following result.
Table 1 -------- col1 col2 1 a b c 2 a b c d
Table 2 -------- col2 col3 a aaaa b bbbb c cccc d dddd e eeee f ffff g gggg h hhhh
Output ------- col1 col2 col3 1 a aaaa b bbbb c cccc d dddd e eeee f ffff g gggg h hhhh 2 a aaaa b bbbb c cccc d dddd e eeee f ffff g gggg h hhhh
ThanksThis message has been edited. Last edited by: Kerry,
WFConsultant
WF 8105M on Win7/Tomcat
March 06, 2012, 01:06 PM
j.gross
You mean:
Table 1
--------
col1 col2
1 a
1 b
1 c
2 a
2 b
2 c
2 d
Table 2
--------
col2 col3
a aaaa
b bbbb
c cccc
d dddd
e eeee
f ffff
g gggg
h hhhh
Output
-------
col1 col2 col3
1 a aaaa
b bbbb
c cccc
d dddd
e eeee
f ffff
g gggg
h hhhh
2 a aaaa
b bbbb
c cccc
d dddd
e eeee
f ffff
g gggg
h hhhh
It's not clear how this output relates to these inputs. How are values f,g,h of col2 in Table 2 associated with either value (1,2) of col1 in Table 1? Do you just want to replicate all columns and rows of table2 for each distinct value of col1 in Table 1?
March 06, 2012, 01:29 PM
Kamesh
YES, I want to replicate all columns and rows of table2 for each distinct value of col1 in Table1.
WFConsultant
WF 8105M on Win7/Tomcat
March 06, 2012, 02:25 PM
j.gross
So you want a Cartesian product. One method is a "Conditional" join without conditions:
JOIN CLEAR *
SET HOLDLIST=PRINTONLY
-* set up two data sources without common fields
TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY CONTAINS 'Y';
ON TABLE HOLD AS TABLE1 FORMAT FOCUS
END
TABLE FILE CAR
BY CAR BY MODEL BY RCOST
WHERE COUNTRY OMITS 'Y';
ON TABLE HOLD AS TABLE2 FORMAT FOCUS
END
-* join and report
JOIN FILE TABLE1 AT E01 TO MULTIPLE FILE TABLE2 AT E01
END
TABLE FILE TABLE1
PRINT SEG.CAR
BY COUNTRY
ON TABLE SET ONLINE-FMT STANDARD
END
Output:
NUMBER OF RECORDS IN TABLE= 2 LINES= 2
NUMBER OF RECORDS IN TABLE= 7 LINES= 7
NUMBER OF RECORDS IN TABLE= 14 LINES= 14
COUNTRY CAR MODEL RETAIL_COST
------- --- ----- -----------
ITALY DATSUN B210 2 DOOR AUTO 3,139
JAGUAR V12XKE AUTO 8,878
JAGUAR XJ12L AUTO 13,491
JENSEN INTERCEPTOR III 17,850
PEUGEOT 504 4 DOOR 5,610
TOYOTA COROLLA 4 DOOR DIX AUTO 3,339
TRIUMPH TR7 5,100
W GERMANY DATSUN B210 2 DOOR AUTO 3,139
JAGUAR V12XKE AUTO 8,878
JAGUAR XJ12L AUTO 13,491
JENSEN INTERCEPTOR III 17,850
PEUGEOT 504 4 DOOR 5,610
TOYOTA COROLLA 4 DOOR DIX AUTO 3,339
TRIUMPH TR7 5,100
March 07, 2012, 09:40 AM
Kamesh
This is not working for me.
In your case, COUNTRY is in other table and doing the sort by COUNTRY will work but my case just CAR is in different table but after we join, I want to do the sort by COUNTRY.
something like this
TABLE FILE CAR BY CAR WHERE COUNTRY CONTAINS 'Y'; ON TABLE HOLD AS TABLE1 FORMAT FOCUS END
TABLE FILE CAR BY COUNTRY BY MODEL BY RCOST WHERE COUNTRY OMITS 'Y'; ON TABLE HOLD AS TABLE2 FORMAT FOCUS END
-* join and report JOIN FILE TABLE1 AT E01 TO MULTIPLE FILE TABLE2 AT E01 END
TABLE FILE TABLE1 PRINT CAR MODEL RCOST BY COUNTRY ON TABLE SET ONLINE-FMT STANDARD END
WFConsultant
WF 8105M on Win7/Tomcat
March 07, 2012, 12:25 PM
njsden
quote:
This is not working for me.
quote:
... my case just CAR is in different table but after we join, I want to do the sort by COUNTRY.
You could have provided all those particular details about your data from the very beginning when you posted your question, therefore reducing a lot of guesswork.
I honestly don't see how j.gross could have come up with such a good example given so limited specs ... perhaps I'm just becoming lazy
Anyway, it seems as if you already managed to envision a solution based on your last piece of code, didn't you?