on 03-20-2007 5:15 AM
Post Author: tazer98
CA Forum: Data Connectivity and SQL
Hello All,
Back again. Its getting a little tougher as I get more involved in CR. Hereu2019s my latest situation.
I have 4 tables (A, B, C and D). There are links between tables: A-B, A-C and C-D. Keys are: KeyAB, KeyAC and KeyCD. Link A-B is a one-to-one, A-C is a one-to-one and table C-D is a one-to-many.
After retrieving a row from table A I make a determination in getting my data (on-hand quantity) from table B or table D.
My pseudo code would be:
Retrieve table A row
If tableA code = 1 then
Retrieve tableB row
Print tableBQty
Else
Retrieve tableC row
Retrieve all related tableD rows
Print tableDQty
End
Go to retrieve another from tableA
Well I really donu2019t know how to put this type of logic into CR. I believe it lies in the link options of the database.
Can someone help me out on this one?
Thanks,
Hexman
Post Author: tazer98
CA Forum: Data Connectivity and SQL
Very thourough answer. I'll be trying this today.
Thanks,
Hexman
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: tazer98
CA Forum: Data Connectivity and SQL
Thanks for all the info. The suppression of sections sounds like it will do the trick. Yes, I do want to show D's detail, row by row.
Hexman.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: GraemeG
CA Forum: Data Connectivity and SQL
Clarification on file links (and apologies if this is old hat) - when I'm working on multi-file reports with combinations of one-to-one, many-to-one and one-to-many, I tend to change my links to outer joins that aren't enforced - I can then control my inclusion/exclusion and any other record discrepancy error trapping. Inner joins require a matching record to exist in both files that you are linking.
Are you wanting to show D's records in detail or summary?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: Jagan
CA Forum: Data Connectivity and SQL
You could have two sections, both conditionally suppressed. Put the B.qty value into one section and conditionally suppress if A.code <> 1. Put the fetch of the C-D data into a subreport, place it in its own section and conditionally suppress the section if A.code = 1.. i.e. only fetch the C-D data if you need it, record by record. However, this could slow the report down a bit if you've got a lot of records.
Otherwise you could join all tables as you've described, which'll give you N rows per record in A where N is the number of rows in D for that record (or at least 1 if you need to left join to either C or D). You could group on A's key accordingly (giving you N detail records per group) and print (using a formula to chose the value you want to display) in the A group footer. This would affect any sums / running totals etc. if you have any 'cos of the N rows in D.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.