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,

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

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!

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

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!

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

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!

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

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!

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