 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to report column from 2 alternative tables
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to report column from 2 alternative tables Read-Only TopicGo | Search | Notify | Tools |
| How to report column from 2 alternative tables | Login/Join |
| Member |
I am running into a problem when trying to report data on a column that might come from two different tables. Here is my situation, I need to report for every VOUCHER in table GLTABLE the VENDOR name. The VENDOR name can be obtained from one of two tables, APOPEN or APHIST, depending if the voucher has been paid or not. See the tables below:
GLTABLE APOPEN APHIST
VOUCHER VOUCHER VOUCHER
VENDOR VENDOR
I am doing a Join between GLTABLE and APOPEN AND APHIST using VOUCHER.
GLTABLE -----> APOPEN Both Left Outer Joins
-----> APHIST
The desired report for this is as below. Desired Report: REPORT VOUCHER VENDOR 1234 CISCO - FROM APOPEN 1235 CISCO - FROM APHIST 1236 NORTEL - FROM APHIST 1237 CISCO - FROM APOPEN My initial approach was to create a DEFINE with something like: REALVENDOR=IF APOPEN.VOUCHER IS MISSING THEN APHIST.VENDOR ELSE AOPEN.VENDOR But the REALVENDOR is always showing empty... Do you have any ideas on what approach I should take? Thanks, RafaelThis message has been edited. Last edited by: Rafa, Rafa | ||
|
| Virtuoso |
Please edit your post (click on the Folder icon, lower right), putting the column-sensitive data displays
between CODE delimiters
(click on the red </> icon)This message has been edited. Last edited by: j.gross, - Jack Gross WF through 8.1.05 | |||
|
| Virtuoso |
If GLTABLE has a field indicating a/p status (say APSTATUS = "OPEN" or "PAID"), this is all you need:
JOIN VOUCHER IN GLTABLE TO VOUCHER IN APOPEN AS OP:
JOIN VOUCHER IN GLTABLE TO VOUCHER IN APHIST AS PD:
DEFINE FILE GLTABLE
VENDOR/Axx=
IF (APSTATUS EQ 'PAID')
THEN APHIST.VENDOR
ELSE APOPEN.VENDOR;
END - Jack Gross WF through 8.1.05 | |||
|
| Expert |
With GL data one would expect a voucher to exist in either the open file or the hist file and not both, so you could easily use MATCH logic with OLD-OR-NEW. In fact there are several methods to ahcieve what you want and some of them depend upon the RDBMS type of your source data files. For instance, if they are focus files and have the same layout you could effectively combine them using USE logic before the join. T
| |||||||||
|
| Member |
Thank you for your input, It seems I figured it out. The first thing I did was to make the join UNIQUE. The second was to create a DEFINE APSTATUS as j.g. suggested. But since it is now available in table GLTABLE I used a define to define if the data is coming from OPEN or HIST. It looks like this APSTATUS=IF APHIST.VOUCHER IS MISSING THEN 'OPEN' ELSE 'HIST'; All the best, Rafael | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to report column from 2 alternative tables
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to report column from 2 alternative tablesCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |