I have an sql stored procedure focexec that I need it to be able tell if the user logon is in the EmpID field to continue on with the procedure - if not go to the not in scorecard html. Any examples? Not sure if I know what I'm doing. Thanks! I'll keep searching the library.

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

Hi Joe,

See if the following code helps:


Best Regards,

Jimmy Pang

-* Checks if logon is in the table
TABLE FILE EMPDATA
PRINT
PIN
LASTNAME
FIRSTNAME
WHERE PIN EQ '&PINNUM'
ON TABLE HOLD AS HOLD1
END
-RUN

-* Checks if it exists
-IF &LINES NE 0 THEN GOTO RUNREC;
[... doesn't exists, this can display error message in HTML form]
-EXIT

-RUNREC
[run your sql procedure]

I'll try that - thanks!

Almost worked Got this message when I tested for Login that is in the file:

ACCESS FILE RECORD ABSENT, WRONG OR INCOMPLETE FOR SEGMENT : SQLOUT

 -* File scorecardtest.fex
-DEFAULT &ppnum = 'PP33963'
-*-SET &ppnum = 'USERID2'
ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.scorecard '&ppnum'
;
-* checks logon
TABLE FILE SQLOUT
PRINT EmpID
WHERE EmpID EQ '&ppnum'
ON TABLE HOLD AS HOLD1
END
-RUN

-* Checks if it exists
-IF &LINES NE 0 THEN GOTO RUNREC;
-HTMLFORM nodata
-EXIT

-RUNREC


TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
DEFINE FILE SQLOUT
USERID/A8='&IBIC_user';
USERID9/A8=(USERID);
USERNUM/A6=EDIT(USERID9, '$$999999');
USERID2/A8=UPCASE(7,USERID9,'A7');
END
-*-SET &ppnum = 'USERID2'
TABLE FILE SQLOUT
-* 1st Macro
-* 2nd Macro
-* 3rd Macro
-* 4th Macro
-*5th Macro
-* First Format
-* Second Format
-*Third Format
-* Forth Format
-* 5th Format
PRINT
     EmpID
     FName
     LName
     Group
     Team
     AHT
     AHTRank
     COMPUTE AHTPer/D6.2% = AHTPerRank * 100;
     AHTPoints
     RpH
     RpHRank
     COMPUTE RpHPer/D6.1% = RpHPerRank * 100;
     RpHPoints
     FCR
     FCRRank
     COMPUTE FCRPer/D6.1% = FCRPerRank * 100;
     FCRPoints
     CIQ
     CIQRank
     COMPUTE CIQPer/D6.1% = CIQPerRank * 100;
     CIQPoints
     MonScore
     MonRank
     COMPUTE MonPer/D6.1% = MonPerRank * 100;
     MonPoints
     TotRank
     TotPoints
     Payout
HEADING
"&ppnum"
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE * 

Thanks!

Hi Joe,

What is SQLOUT? Is that a table name?
Not sure if holding a file with the same name as table name causes any confusion to WF.

The only other thing that I can see is if this is your entire code, it's missing an 'END' at the end of the program for the last TABLE FILE section of the code.

See if that works.

Best Regards,

Jimmy Pang

I didn't position the coding it in the right spot. It works now: THANKS!!!

  -* File scorecardtest.fex
-DEFAULT &ppnum = 'PP52054'
-*-SET &ppnum = 'USERID2'
ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.scorecard '&ppnum'
;

-* Checks if logon is in the table

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END

TABLE FILE SQLOUT
PRINT
EmpID
WHERE EmpID EQ '&ppnum'
ON TABLE HOLD AS HOLD1
END
-RUN

-* Checks if it exists
-IF &LINES NE 0 THEN GOTO RUNREC;
-HTMLFORM nodata
-EXIT

-RUNREC



DEFINE FILE SQLOUT
USERID/A8='&IBIC_user';
USERID9/A8=(USERID);
USERNUM/A6=EDIT(USERID9, '$$999999');
USERID2/A8=UPCASE(7,USERID9,'A7');
END




-*-SET &ppnum = 'USERID2'
TABLE FILE SQLOUT
-* 1st Macro
-* 2nd Macro
-* 3rd Macro
-* 4th Macro
-*5th Macro
-* First Format
-* Second Format
-*Third Format
-* Forth Format
-* 5th Format
PRINT
     EmpID
     FName
     LName
     Group
     Team
     AHT/I5
     AHTRank
     COMPUTE AHTPer/D6.1% = AHTPerRank * 100;
     AHTPoints
     RpH
     RpHRank
     COMPUTE RpHPer/D6.1% = RpHPerRank * 100;
     RpHPoints
     FCR
     FCRRank
     COMPUTE FCRPer/D6.1% = FCRPerRank * 100;
     FCRPoints
     CIQ
     CIQRank
     COMPUTE CIQPer/D6.1% = CIQPerRank * 100;
     CIQPoints
     MonScore
     MonRank
     COMPUTE MonPer/D6.1% = MonPerRank * 100;
     MonPoints
     TotRank
     TotPoints
     Payout
HEADING
"&ppnum"
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML