I need to find out disconnected records within an optional set relationship using OLQ in Batch.
Let me elaborate using the following example:
Say there are 3 IDMS records, A, B and C. B has 2 owners A and C. Set relationships are A-B (mandatory) and B-C (Optional).
The requirement is to obtain the following fields:
All fields of record B.
Fields from record A (for corresponding Owner in A-B): A1, A2
Fields from record C (for corresponding Owner in B-C): C1, C2 or Blanks (in case set is disconnected)
If we write the following query on OLQ batch:
Select a.A1, a.A2, c.C1, c.C2, b.* -
FROM A a, C c, B b -
WHERE (A-B.A.B) -
The query will fetch ONLY those rows from record B that are connected to both records A and C. But since the set B-C is optional there may be rows in record B that are disconnected. These rows will not appear in the output.
If B has 100 rows, all of these will be connected to A. But say only 80 are connected to C. So the above query will fetch only 80 rows and drop the rest 20 rows. The requirement is to have all 100 rows (80 + 20) rows to appear in the output with Values in C1 and C2 for connected records (B-C) and blank values in C1 and C2 for dis-connected records .
I am aware that this can be achieved by using a COBOL-IDMS routine, but I need this through OLQ Batch query only. If anyone can share the query to achieve this please respond.
Since I needed it quickly I have already coded a program but I need the solution through OLQ since this is simple and quick. Also I am working on apilot as of now. The next application has several such instances. So even if the solution comes across slightly late it won't be a problem.