Specifying Join based on a list of values

[rpvs]

Am having problems linking two query subjects one of which has a comma seperated list of values in Frame work manager. Am specifying join using the 'IN' function for example

quote_num in (quotes)

The quotes is a list of quote numbers seperated by a comma. Is there something wrong in creating a join like this and publishing a package???? In query studio no data is being returned when i pull columns from the two query subject

[blom0344]

A join is based on equality (=) , in some cases on a range (between) and in even rarer cases  <> / <=/ >=
An in operator is either used as a filter (where clause) or in subqueries (select ..... from .... where x in (select ............)
Did you check in FM?  What you are doing does not sound like a proper way to join sujects

[rpvs]

The only link between the quotes and the sales is the field in sales query which has a comma seperated list of quote numbers. Am trying to link the sales query to the quotes query based on this. If the 'IN' function is not a way to do it , i can add 3 more fields in the sales query which has all the quote numbers(limit is 3 quotes for a Sales) . Can we use an 'OR' (in FM) and join the quote numbers between the 2 queries which would look something like below

salesquery.quote1 = quotes.quotenum or salesquery.quote2 = quotes.quotenum or salesquery.quote3 = quotes.quotenum

[blom0344]

I'd guess that you need a descriptive field from the quote table? In that case I would indeed use 3 fields in the sales object and create aliasses/shortcuts for the quote object.
This would yield the scenarion of a star with 3 dimensions:

Code Select Expand


SELECT S.*,Q1.DESC,Q2.DESC,Q3.DESC FROM SALES S
INNER JOIN QUOTE1 Q1
ON  S.Q1 = Q1.ID
INNER JOIN
QUOTE2 Q2
ON S.Q2 = Q2.ID
INNER JOIN
QUOTE3 Q3
ON S.Q3 = Q2.ID