Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Join is returning first record from one of the table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Join is returning first record from one of the table
 Login/Join
 
Gold member
posted
I have the following code in my report:
-DEFAULT &FROM_DATE = '20110207';
-DEFAULT &TO_DATE   = '20110209';
-DEFAULT &VENDOR# = 'FOC_NONE'

DEFINE FILE AE_DT1
	VENDOR_NUMBER/I9 = EDIT(AE_DT1.AE_DT1.FIELD1) ;
    INVOICE_NUMBER/A25 = EDIT(AE_DT1.AE_DT1.FIELD2, '9999999999999999999999999') ;
	SCAN_DATE/A8YYMD = EDIT(AE_DT1.AE_DT1.FIELD24, '9999$99$99') ;
END

TABLE FILE AE_DT1
PRINT
	AE_DT1.AE_DT1.FIELD5 AS 'INVOICE_DATE'
	AE_DT1.AE_DT1.FIELD6 AS 'AMOUNT'
	SCAN_DATE
WHERE SCAN_DATE GE '20110201';
WHERE VENDOR_NUMBER EQ '8138864'
BY VENDOR_NUMBER
BY INVOICE_NUMBER
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS TBL1
END
-RUN

DEFINE FILE F0411
BATCH_DATE/A8YYMD = BATCH_DATE ;
G_L_DATE/A8YYMD = G_L_DATE ;
END

TABLE FILE F0411
PRINT
	INVOICE_DATE
	GROSS_AMOUNT
	G_L_DATE
	PAYMENT_TERMS
WHERE G_L_DATE NE '';
WHERE BATCH_DATE NE '';
WHERE G_L_DATE GE '&FROM_DATE.From GL Date.';
WHERE G_L_DATE LE '&TO_DATE.Through GL Date.';
WHERE ADDRESS_NUMBER EQ '8138864';
BY ADDRESS_NUMBER
BY INVOICE_NUMBER
ON TABLE HOLD AS TBL2
END
-RUN

JOIN INNER VENDOR_NUMBER AND INVOICE_NUMBER IN TBL1
   TO ADDRESS_NUMBER AND INVOICE_NUMBER IN TBL2 TAG J001
AS J001
END

DEFINE FILE TBL1
DIFF1/YYMD = DATECVT(SCAN_DATE,'A8YYMD','YYMD');
DIFF2/YYMD = DATECVT(G_L_DATE,'A8YYMD','YYMD');
DIFF3/D20 = DIFF2 - DIFF1;
END

TABLE FILE TBL1
PRINT
ALPHA_NAME AS 'VENDOR NAME'
VENDOR_NUMBER AS 'Vendor Number'
INVOICE_NUMBER AS 'Invoice Number'
INVOICE_DATE AS 'Invoice Date'
AMOUNT AS 'Amount'
SCAN_DATE AS 'Scan Date'
J001.TBL2.G_L_DATE AS 'G_L_Date'
DIFF3 AS '# Days b/w Scan and G_L Date'
J001.TBL2.PAYMENT_TERMS AS 'Payment Terms'
WHERE J001.TBL2.G_L_DATE NE ''
ON TABLE PCHOLD FORMAT EXL2K
END



It returns the data fine. But when I add another join, it only gets the first record from the new table and attach it to the other records, it looks like it does not recognize the join.

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


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 15, 2010Report This Post
Virtuoso
posted Hide Post
If it would not recognize the join, then it would also not retrieve just one record fromthe new table.
Did you specify the join as a multiple join (JOIN ... TO ALL ...)?


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
Can you also post the extra join and the format of the file or the code that generated it ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
I was able to get this resolved. Issue had to do with teh field format. Once had the format P8 and the other had I9. Once we got the format the same. We got correct data from the join.


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 15, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Join is returning first record from one of the table

Copyright © 1996-2020 Information Builders