In a one-to-many RDBMS join, is there any way of retrieving only one row per parent segment - other than using BY LOWEST/HIGHEST 1 column-name-in-child-segment?

Thanks.

Francis,

Off the top of my shiny head

SET ALL = OFF?
- or -
TABLE FILE parent
SUM MAX.childfield
    MIN.childfield
    FST.childfield
    LST.childfield
BY parentfield

T

Take the ALL off of the JOIN so that it is unique? Just an idea. You can do the SQL trace to see what SQL you get.

Do you want the highest or lowest or just the first one?

Also, while I know you use SQL Server, you need to be careful with direct operators as not all are passed to all RDBMS adapters.

As the others already stated, which one do you want to get.
It's like as if you say I have 6 children, and tonight I'm going to the circus, but I have only two tickets, one for me and one for.....the one how has first finished his meal...

I can only afford to take one child out and I don't care which one.

I'll try the SET ALL=OFF setting.

Thanks.

Francis,

With my aplogies to Tony, I don't think SET ALL=OFF will work. It is off by default so if your query is not doing what you expect now, explicitly coding it won't help.

Can you try taking the all off of the JOIN and let us know what happens?

You will need to use the aggregates of min or max.

JOIN KEY IN FILE1 TO KEY IN FILE2
TABLE FILE FILE1
SUM
MIN.FILE2.FIELD1
BY FILE1.FIELD1
END

Otherwise you could turn off optimization and force focus to do the join, or create a hold file from file1 and then join the hold file up which would also force a focus managed join.

i agree with Ginny...i think the ALL out of the JOIN will bring you back just your firstborn child, the others having run away from home to join that circus.

Coding a one-to-one JOIN against FOCUS databases will return only one matching row from the child file regardless of the true nature of the JOIN. However, I don't think you can force an rdbms to treat a one-to-many relationship as a one-to-one relationship. Even if you code the JOIN without the ALL it will return all rows and then you need to try to work with the prefix operators or the BY 1 type of syntax.

I'd experiment with the SET OPTIMIZATION OFF. You should see a FOCUS managed JOIN in the traces which could allow the FOCUS one-to-one JOIN behaviour. Be aware it will do multiple select statements... one for each table. The other setting to try is SET OPTIMIZATION FOCUS .

how about that! hmm. thanks,P.

I originally had SET ALL=ON in the program and was getting a lot more rows than I expected. I SET ALL=OFF and reduced the number of rows but I am still getting more than I should be.

Over the years I have disciplined myself to writing the most optimized code as possible, so my fingers would find it very hard to type "SET OPTIMIZATION OFF"!

I may give it a try.

Thanks,

Ginny's original response was the answer. You don't need to change settings with SETs. This is even simpler if you don't care which record it is. If you remove the ALLfrom the JOIN statement, as soon as it finds a single cross-referenced record, it quits looking for more. So you get one child record per parent.

select distinct....
OR
select [unique field[s]],
[some aggregation function such as MAX() of fact field 1],
[some aggregation function such as MAX() of fact field 2]
from
x inner join y on x.field = y.field
group by
[unique field[s]]

Darin, I hate to disagree with a Guru, but Gizmo has the correct answer. To return one row per parent in a one-to-may RDBMS join, you must do something that creates a GROUP BY line. To do that, you have to do MIN.field or MAX.field, just as GIZMO states. The other option, as Gizmo states is to set optimization off, which I almost never recommend. Consider the following examples where KDSCLINT is the parent and CL_CLID is a unique key and KDSRFLCL is the child table where there can be many occurances of CL_CLID/RC_CLID:

 JOIN CL_CLID IN KDSCLINT TO RC_CLID IN KDSRFLCL
 TABLE FILE KDSCLINT
 PRINT CL_FNM CL_LNM RC_REFER_ID
    BY CL_CLID
 WHERE CL_CLID EQ 1746707
    ON TABLE HOLD
 END

returns the following

SELECT T1."CL_ID",
       T1."FRST_NME",
       T1."LST_NME",
       T2."REFER_ID"
  FROM KIDSPROD.TCLIENT T1,
  KIDSPROD.TREF_CLIENT T2
 WHERE (T2."CL_ID" = T1."CL_ID")
   AND (T1."CL_ID" = 1746707)
 ORDER BY T1."CL_ID";

 0 NUMBER OF RECORDS IN TABLE=        6  LINES=      6

Second example:

 JOIN CL_CLID IN KDSCLINT TO RC_CLID IN KDSRFLCL
 TABLE FILE KDSCLINT
   SUM MAX.CL_FNM MAX.CL_LNM MAX.RC_REFER_ID
    BY CL_CLID
 WHERE CL_CLID EQ 1746707
    ON TABLE HOLD
 END

returns the following

SELECT T1."CL_ID",
       MAX(T1."FRST_NME"),
       MAX(T1."LST_NME"),
       MAX(T2."REFER_ID")
  FROM KIDSPROD.TCLIENT T1,
       KIDSPROD.TREF_CLIENT T2
 WHERE (T2."CL_ID" = T1."CL_ID")
   AND (T1."CL_ID" = 1746707)
 GROUP BY T1."CL_ID"
 ORDER BY T1."CL_ID";

 0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1

Notice the GROUP BY. That will get you the single row. Note, FST. and LST. will work but you get the following:

(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2505) A SEGMENT IN THE STRUCTURE IS NON-KEYED:  : TCLIENT
(FOC2517) FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

The only effect that ALL being ON or OFF has is that with ALL being ON, you will still get a parent if there are no children. WebFOCUS now generates the outer join where as in earlier releases, it did not.

This message has been edited. Last edited by: jgelona,

jgelona, thanks for the explanation.

that was very instructive. thank you jg

quote:

As the others already stated, which one do you want to get.
It's like as if you say I have 6 children, and tonight I'm going to the circus, but I have only two tickets, one for me and one for.....the one how has first finished his meal...

So not the first or the last will join you but the oldest or the youngest....

What happens if there are no kids??

Do you get the parents, or would they be missing in this report.

FrankDutch, that's where the SET ALL comes in. Set ALL to OFF and if there are no children, the parent row is dropped. Set ALL to ON or PASS and you will get the parent even if there are no children.

A lot of my reporting is done by dates and there are many times when I want the first or last. Since Oracle doesn't have a first or last operator like FST. or LST. and I have to use a 2 step process. First step is to collect and sort the data, hold the data, then use a second step on the HOLD file using FST. or LST. Sometimes, depending on the requirements, I will code an SQL PREPARE preparenm and TABLE FILE preparenm and use the FST. and/or LST. operators. This works because the database returns the answer set already sorted.

Much of my code came from FOCUS for HP-UX. One of the manuals we got was "Interface to Oracle Users Manual". I highly recommend it. Chapter 8 - Interface Reporting Techniques, explains a lot. There are some slight differences now on how the interface works now, i.e. when this manual was published, FOCUS would not generate outer joins when one used SET ALL=ON, now it does. I haven't checked to see if there is a current manual.