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]Maintain Application error - FOC03645

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Maintain Application error - FOC03645
 Login/Join
 
Platinum Member
posted
Hello,

I am developing a Maintain application where the user has the ability to search the database using any 1 of the 2 fields Cust_no or Cust_id and based on the user input, I filter the database for those records and display them in a HTML table so that the user can further update/delete them.

I am getting the error "WHERE clause syntax: Field-to-Field not yet supported" while trying to introduce 2 filters in my where clause to satisfy this requirement.

My code snippet is as below:
Case TOP
......
....
COMPUTE P_CUSTNO/A16V;
COMPUTE P_CUSTID/I6;
..........
EndCase

Case GetRecords
Reposition cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE ;
Stack clear GetRecordStack ;
For all next cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE into GetRecordStack 
WHERE CUST_NO EQ P_CUSTNO OR CUST_ID EQ P_CUSTID;
Commit;
EndCase


Can anyone help me with why I am getting this error on the WHERE clause?

Thanks in advance!

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


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Master
posted Hide Post
Maintain does not support OR. There are two ways to make this work. The first is to have two Next Statements:


Case GetRecords
Reposition cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE ;
Stack clear GetRecordStack ;
For all next cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE into GetRecordStack
WHERE CUST_NO EQ P_CUSTNO

Reposition cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE ;
For all next cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE into GetRecordStack(GetRecordStack.foccount+1);
WHERE CUST_ID EQ P_CUSTID;

Commit;
EndCase

Notice that in the second retrieval I use GetRecordStack(GetRecordStack.foccount+1). This will make sure the records from the first retrieval are not overwritten.

The more efficient way to do this is with an EXEC statement. This is where you can pass values to a TABLE request.
In the Maintain you would have:

Case GetRecords
Infer cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE into GetRecordStack
Stack clear GetRecordStack ;
EXEC GetRecordFex from P_CUSTNO P_CUSTID INTO GetRecordStk
Commit;
EndCase

Then GetRecordFex would be:

TABLE FILE CUST_TABLE
PRINT fields
WHERE CUST_NO EQ P_CUSTNO
OR CUST_ID EQ P_CUSTID;
ON TABLE PCHOLD
END
-RUN

Replace fields with the name of the Fields you wish to retrieve. Both ways work. The only issue with the first method is that you may get duplicate records. I can give you a routine to get rid of those if you need it.

Good Luck
Mark Derwin
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Platinum Member
posted Hide Post
Thank you for your help, Mark!

After I used the EXEC fex method to pass input parameters into my fex, I have a HTML table where my results are displayed from GetRecordStack. But nothing shows up on the table, instead I get following error:
(FOC03690) Called Proc, ACS_REMOTE_IDS, wants 0 Output parms but was called w/ 1.


Can you please let me know why I am getting this error?

Thanks in advance!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Master
posted Hide Post
That error means that there is a problem with the FEX. The usual problem is that Maintain can't find the Master file or a syntax error in the WHERE close. To debug, do this.

1) Add - SET &ECHO='ALL' at the top of the FEX

-SET &ECHO = 'ALL';
TABLE FILE CUST_TABLE
PRINT fields
WHERE CUST_NO EQ P_CUSTNO
OR CUST_ID EQ P_CUSTID;
ON TABLE PCHOLD
END
-RUN

2) On your Maintain form, add an HTMLTable and populate it with FOCMSG.MSG

Now, any problems with the FEX are displayed in the HTMLTable on the form.

It may be that you need to enclose your variables in quotes in the WHERE clauses.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Platinum Member
posted Hide Post
I still get an error:

FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: P_CUSTID


I am not sure why I get this error, I have declared it in my Maintain file under Case TOP.

Thanks!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Master
posted Hide Post
So Sorry! My mistake. Variables are received sequentially! Do this instead...

-SET &ECHO = 'ALL';
TABLE FILE CUST_TABLE
PRINT fields
WHERE CUST_NO EQ &1
OR CUST_ID EQ &2;
ON TABLE PCHOLD
END
-RUN

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Platinum Member
posted Hide Post
No problem - thanks a lot for assisting with this issue.

Actually, I believe it still isn't working as expected.
I have 2 input variables for the end-user on the form and they can enter either 1 out of the 2 or they might enter both.

I included a -DEFAULT &P_CUSTNO = FOC_NONE; statement for both variables so as to be able to search the table by either of them or both - but I think that's not working.

Is it something different for Maintain? I know this would work for a regular fex.
I am not sure why the user-input does not pass onto the fex variable - infact the fex just ignores the where clause (due to the DEFAULT statement) and returns all rows in the HTML table.

Please advise! Thanks!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Master
posted Hide Post
When we EXEC the procedure it's exactly the same as running it from outside of Maintain. You SHOULD be seeing exactly what is going on on your screen. Remember, if you placed an HTMLTable on the form populated by FOCMSG.MSG and have ECHO On in your procedure, all the code should be visible. Make sure you are not getting any errors.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Platinum Member
posted Hide Post
Okay, I am not sure what I am doing wrong, but I can't get user-input from the form to pass on to the fex.

Here's my fex code:
-DEFAULTS &P_CUSTNO = FOC_NONE;
-DEFAULTS &P_CUSTID = FOC_NONE;


-SET &P_CUSTNO = IF &P_CUSTNO EQ '' THEN FOC_NONE ELSE &P_CUSTNO;
-SET &P_CUSTID = IF &P_CUSTID EQ 0 THEN FOC_NONE ELSE &P_CUSTID;

TABLE FILE MY_TABLE
PRINT
     ........
     Fields
     ......
WHERE CUSTNO EQ &P_CUSTNO OR CUSTID EQ &P_CUSTID;
ON TABLE PCHOLD
END
-RUN


Through my troubleshooting using &ECHO= 'ALL' - I see that only FOC_NONE passes to both variables even though I enter values in the text boxes in my input form in maintain.

Also, if I don't put in the -DEFAULTS statement in the fex, then I get an error that value is missing for &P_CUSTNO and &P_CUSTID.

I am not really sure what's wrong - how do I get the input form values (the user enters) to pass to the fex?

Thanks!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Master
posted Hide Post
As I mentioned above, Maintain sends in the variables as &1 and &2. You need to replace

-SET &P_CUSTNO = IF &P_CUSTNO EQ '' THEN FOC_NONE ELSE &P_CUSTNO;
-SET &P_CUSTID = IF &P_CUSTID EQ 0 THEN FOC_NONE ELSE &P_CUSTID;

with

-SET &P_CUSTNO = IF &1 EQ '' THEN FOC_NONE ELSE &1;
-SET &P_CUSTID = IF &2 EQ 0 THEN FOC_NONE ELSE &2;

if you are still having issues, we can go back to Maintain code. Is it always going to be P_CUSNO or P_CUSTID if so we can use this logic:

Case GetRecords
Reposition cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE ;
Stack clear GetRecordStack ;

if P_CUSTID ne 0 then begin
For all next cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE into GetRecordStack
WHERE CUST_ID EQ P_CUSTID
ENDBEGIN

ELSE BEGIN
For all next cust_table.CUST_TABLE.CUST_NO cust_table.CUST_TABLE.CUST_ID cust_table.CUST_TABLE.LAST_PROCESSED_DATE into GetRecordStack
WHERE CUST_NO EQ P_CUSTNO;
Endbegin
Commit;
EndCase

Check to see if a Custid was entered. IF so, screen on it. Else, screen on Custno. You could also add logic for if neither are entered.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report 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]Maintain Application error - FOC03645

Copyright © 1996-2020 Information Builders