Going about this two ways as I'm trying to create one fex file that can provide data from two sources based on the table name.

I tried creating a procedure using SQL and created a similar procedure using native WebFocus.
I'll explain the SQL version first and then the WebFocus.

I created a procedure and it is working to allow me to choose an sql table file or different sql table based on amperprompt criteria. When I run it hard coded with the table name in the sql statement performance is fast.

SELECT XYZ FROM TABLE1

When the same table name is used via amperTableName, performance is slow.

SELECT XZY FROM &TABLENAME 

Since this is sql, can't give car example. Any thoughts on why it would run slower via a prompt versions the actual table name?

I also am trying to go about it the other way around using native webfocus. I have spent sevaral hours on this and got it to provide data from Table A, but when I try to get data from Table B, I run into a problem because in order to get data from Table B, I have to restrict it by RunDate. How do I provide the date or format the date so that it can be used in the following SET statement? This is what I have tried, among other things.

-SET &WHERE_STRING = IF &ORACLEDATE EQ 'CURRENT' THEN &WHERE_STRING ELSE &WHERE_STRING | ' AND HDATE(DATE_UPDATED, ''YYMD'') EQ &ORACLEDATE';

The issue is a combination of how to convert the date so Focus understands it and also format of the SET command as there is another error with missing quotes.



This statement works if I use it directly in the section where the WHERE is located, but of course, I don't want to have to hard code this.

WHERE HDATE ( DATE_UPDATED, 'YYMD' ) EQ '2012-07-31'

Also, how can I have the prompt so that the user can enter the date normally instead of Year-Month-Day?

Question 1 - Try .EVAL in your sql pass thru

SELECT XZY FROM &TABLENAME.EVAL

Question 2 - Try .QUOTEDSTRING to evaluate the quotes correctly.

-SET &ORACLEDATE = 'CURRENT_X';
-SET &ORACLEDATE = 'CURRENT';
-SET &WHERE_STRING = 'WHERE HDATE(DATE_UPDATED, ''YYMD'')';
-SET &WHERE_STRING = IF &ORACLEDATE.EVAL EQ 'CURRENT' THEN &WHERE_STRING.QUOTEDSTRING ELSE &WHERE_STRING.QUOTEDSTRING | ' AND HDATE(DATE_UPDATED, ''YYMD'') EQ &ORACLEDATE.EVAL';
-TYPE &WHERE_STRING

Hi Mighty Max,

Thank you for your suggestions. The first one did not make a difference in the performance. Hard coding the table name, report comes up in about 3 seconds. Using the variable it took 3 minutes even with .EVAL. Does the fact that the portion of the code that uses the sql statement to create the hold file is being called from as an -INCLUDE affect the performance when their is a variable involved?

As for the webfocus version (also an include), I tried the .QUOTEDSTRING and received the error message:
(FOC36355) INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION HDATE

Should I use another function or method to evaluate the date in the variable/WHERE clause? The date field in the underlying Oracle Table mas looks like this:
FIELDNAME=DATE_UPDATED, ALIAS=DATE_UPDATED, USAGE=HYYMDS, ACTUAL=HYYMDS,
MISSING=ON, $

and for the purpose of the WHERE clause, only want to look at the date portion excluding the time. In the prompt box of the fex how do I best key in the date I want to supply as a variable?

With your changes, this part is from the SQL version:

 
-SET &TABLENAME = IF &TABLETYPE EQ 'FOC_NONE' THEN 'usf_stu.usf_as_status_slot a' ELSE 'usf_stu.usf_as_status_slot_archive a';

...
...
from &TABLENAME.EVAL
 

This part is from the WebFocus Version

 
-DEFAULT &ORACLEDATE = 'CURRENT'
-SET &WHERE_STRING = '( PROGRAM OMITS ''ND'' ) AND ( NOT COLLEGE IN (''00'',''LW'') ) AND ( NOT STU_POPULATION IN (''J'',''S'',''V'') ) AND ( TERM GT ''201225'' ) AND ( TERM LT ''201310'' );';
-SET &WHERE_STRING = IF &ORACLEDATE.EVAL EQ 'CURRENT' THEN &WHERE_STRING.QUOTEDSTRING ELSE &WHERE_STRING.QUOTEDSTRING | ' AND HDATE(DATE_UPDATED, ''YYMD'') EQ &ORACLEDATE.EVAL';
...
...
WHERE &WHERE_STRING

 

Q1:

There is absolutely no reason that using a Dialogue Manager variable will make a SQL passthru request slower. Dialogue Manager is a scripting language - the actual report request, whether it be SQL pasthru or WebFOCUS or FOCUS is executed in exactly the same way: embedding Dialogue Manager variables in the code will make no difference - as long as the values in the Dialogue Manager variable values are correct.

Have you tried turning retrieval off so you can see your code results without having to wait for the slow SQL?

-SET &ECHO=ON;
SET XRETRIEVAL=OFF

With this you get to see the actual code that is passed to WebFOCUS - with Dialogue Manager variables translated to their values. Stick a -EXIT right after the END statement of you're having trouble with.

This really shouldn't be difficult to debug.

Q2:

-SET &WHERE_STRING = IF &ORACLEDATE EQ 'CURRENT' THEN &WHERE_STRING ELSE &WHERE_STRING | ' AND HDATE(DATE_UPDATED, ''YYMD'') EQ &ORACLEDATE';

If DATE_UPDATED is a Date-Time column, I would not use HDATE to extract the Date from this column - you're doing this for each row in the table, which could end up being very inefficient.

This is the type of WHERE statement I would use when I have a Dialogue Manager variable that contains a date and a table that contains a Date-Time column:

WHERE DATE_UPDATED FROM '1977-06-07 00:00:00.000' TO '1977-06-07 23:59:59.999'

You may be able to get away with

WHERE DATE_UPDATED FROM '19770607 00:00:00.000' TO '19770607 23:59:59.999'

so, if your &ORACLEDATE value is in YYMD format, this should work:

WHERE DATE_UPDATED FROM '&ORACLEDATE 00:00:00.000' TO '&ORACLEDATE 23:59:59.999'

Also, you may not need the .000 and .999.

This translates to SQL:

WHERE
 (T1.DATE_UPDATED BETWEEN '2001-12-24 00:00:00' AND '2001-12-24
 23:59:59')

Hi Francis,

Thank you for looking at this. I ended up contacting Tech Support and they told me that I should use -IF and GOTO LABEL rather than IF THEN ELSE in the -SET statement. Using this approach solved the performance issue for the SQL version. They said it doesn't have to do with the sql statement itself but how Webfocus handles the variables in the sql pass through. Here is the revised code:

-DEFAULT &ORACLEDATE = 'CURRENT'
-IF &ORACLEDATE EQ 'CURRENT' GOTO LABEL1 ELSE GOTO LABEL2;
-LABEL1
-SET &TABLETYPE = 'FOC_NONE';
-SET &TABLENAME = 'usf_stu.usf_as_status_slot a';
-GOTO TABLESET
-LABEL2
-SET &TABLETYPE = ' ';
-SET &TABLENAME = 'usf_stu.usf_as_status_slot_archive a';
-GOTO TABLESET
 

I'll try your other suggestion on the dates for the Q2 and let you know how it goes.

I am very surprised by this!

quote:

...but how Webfocus handles the variables in the sql pass through...

@sxschech -> Thank you for sharing this information/solution.

@group -> Our shop doesn't currently use the Direct SQL Passthru facility very much. However, I have often wondered if we did, whether or not Direct SQL Passthru supports Dialogue Manager amper variables.

The information from the support line, seems to indicate that amper variables are supported (resolved), but a bit differently.

Can anyone clarify this point further? (I've checked the manuals and did not see mention/examples of amper variable resolution within Direct SQL Passthru.)

I use amper variables all of the time in sql pass thru. But I always SET their values before the sql pass thru. I have found some inconsistency using FOC_NONE however. It does not always ignore the line. I usually comment out the line or skip the line. I am not sure if sxschech was trying to SET the amper variables within the sql pass thru.

  

-SET &CONNECTION = 'DEV';
-SET &USER_ID = 'FOC_NONE';
-SET &USER_FILTER = IF ('&USER_ID.EVAL' EQ 'FOC_NONE') THEN '-*' ELSE '';

ENGINE SQLORA SET DEFAULT_CONNECTION &CONNECTION.EVAL
SQL SQLORA PREPARE SQLOUT FOR

SELECT
      FIRST_NAME,
      LAST_NAME
FROM USERS
&USER_FILTER.EVAL WHERE USER_ID = '&USER_ID.EVAL'

;
END
-RUN

TABLE FILE SQLOUT
PRINT
     FIRST_NAME
     LAST_NAME
END

Thanks for the tip, and nice example, Max.

Yep, the need to keep the dash commands outside/before the Direct SQL Passthru construct, is reasonable.

Thanks again.

The -SET commands were in the first few lines of code before the SQL section. The problem wasn't affecting the speed/performance when using amper variables as a filter in a WHERE clause or another part of the select statement. It only seemed to be an issue in the FROM clause and only when there was a need to choose between different tables. If the ampervariable was hard coded with a specific table name (didn't need to decide between Table A or Table B), the performance was not affected.