Hi,

I need to determine a data effective date that will be displayed in my reports. The method to determine this date is as follows, first I need to run an Oracle query and based on the result (which is just a number) either run another Oracle query and display a date (today - 1 day) or display a date (today - 2 days). Does anyone have any suggestions on how to accomplish this?

Thanks in advance for your help.

Sara,

Will you be running your queries against the Oracle tables using SQL passthrough, or with TABLE FILE commands? Do you need to access the data from Dialog Manager variables, or from the DEFINE FILE section?

For a start before getting more info, here is how to get date - 1 and date - 2 in from the DEFINE FILE section:

DEFINE FILE CAR
TODAY/YYMD = &YYMD;
DATEMINUS1/YYMD = DATEADD(TODAY, 'D', -1);
DATEMINUS2/YYMD = DATEADD(TODAY, 'D', -2);
END


Regards,
Sean

Hi,

Thanks for the info!

I'll be using SQL passthrough. In regard to your other question, I'm not sure; chances are the logic that determines the date would be in its own procedure and therefore the result would be pulled into another procedure so would a DEFINE FILE be the most efficent way?

Thanks!

Sara,

Here is a simple sample of what I think you are trying to accomplish. How your 2 table reads fit in, I'm unclear, but this will hopefully give you the idea. Note that I haven't been able to figure out how SQL passthrough works to read data from a table, so I use the TABLE FILE method:


-* Yesterday
-SET &DATEMINUS1 = DATEADD(&YYMD, 'D', -1);

-* Two days ago
-SET &DATEMINUS2 = DATEADD(&YYMD, 'D', -2);

-* First query to get a number
TABLE FILE CAR
SUM
SEATS
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD FORMAT ALPHA
END

-* Initilize number
-SET &SEATCNT = '0';

-* Read number into amper variable
-RUN
-READ HOLD &SEATCNT.A3

-* Set the effective date based on the number
-SET &EFF_DATE = IF &SEATCNT GT '07' THEN &DATEMINUS1
- ELSE &DATEMINUS2;

-* Run your report with the appropriate effective day
TABLE FILE CAR
PRINT
CAR
BY COUNTRY

HEADING
"&EFF_DATE"

END


Hope this helps!
Sean

Thank you, I think this gets me closer. When I perform the SQL passthrough I'll have it output to a report and hold it.

I do have a question about this statement: -READ HOLD &SEATCNT.A3

I don't have to specify the name of the hold file, correct? Also, what does the A3 stand for?

If the hold was in the format:
ON TABLE HOLD AS HOLDFILE

then you would have to specify:
-READ HOLDFILE &SEATCNT.A3

But with no hold file name specified, it defaults to HOLD.

The A3 means that the field &SEATCNT will be loaded with 3 alphanumeric characters from the hold file. You will need to adjust this to suit your particular requirements.

Great! Thanks so much!

I knew that syntax looked familiar I just totally blanked...I'll chock it up to being a complete novice.

I haven't had a chance to try this all out as I've been pulled away on something else but will let you know how it goes and what I find. Thanks again!

Hi again,
Ok, I've got it working to the point where the correct date is determined however I'm having trouble displaying that date on the report; I receive

quote:

(FOC295) A VALUE IS MISSING FOR: &EFF_DATE

. However, when I display the variable (-? &EFF_DATE) there is a value, what am I missing?

This is my code:
ENGINE SQLORA SET DEFAULT_CONNECTION DBTEST
SQL SQLORA PREPARE SQLOUT FOR
select num
from dual
;
END

TABLE FILE SQLOUT
PRINT
NUM
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END

-SET &DATEMINUS1 = DATEADD(&YYMD, 'D', -1);
-SET &DATEMINUS2 = DATEADD(&YYMD, 'D', -2);
-SET &NUM1 = 0;

-RUN
-READ HOLD &NUM1.D20.2
-IF &NUM1 NE 2 THEN :NoRefresh;

ENGINE SQLORA SET DEFAULT_CONNECTION DBTEST
SQL SQLORA PREPARE SQLOUT FOR
select num
from dual
;
END

TABLE FILE SQLOUT
PRINT
NUM
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END

-SET &NUM2 = 0;

-RUN
-READ HOLD &NUM2.D20.2
-SET &EFF_DATE = IF &NUM2 EQ 1 THEN &DATEMINUS1 ELSE :NoRefresh;
-:NoRefresh
-SET &EFF_DATE = IF &NUM1 NE 2 THEN &DATEMINUS2;

TABLE FILE CAR
PRINT
CAR
FOOTING
"&EFF_DATE "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
END

quote:

-SET &EFF_DATE = IF &NUM2 EQ 1 THEN &DATEMINUS1 ELSE :NoRefresh;
-:NoRefresh
-SET &EFF_DATE = IF &NUM1 NE 2 THEN &DATEMINUS2;

The ELSE part of the IF statement is redundant, since you are jumping to the next line, which you will execute regardless. Perhaps you want the :NoRefresh one line further down.

But the reason for the error is likely that you are not achieving a scenario where &EFF_DATE is getting set. For example, if &NUM2 is not equal to 1 and &NUM1 is equal to 2.

So be sure to initialize &EFF_DATE, and revisit the component quoted above so that you cover off all possible scenarios. Use the -TYPE command to see what your amper variables are set to.

Thank you for the suggestions, with a little rearranging, simplification, and re-evaluation I was able to get it to work.

Here's the final product:

-SET &DATEMINUS1 = DATEADD(&YYMD, 'D', -1);
-SET &DATEMINUS2 = DATEADD(&YYMD, 'D', -2);
-SET &NUM1 = 0;
-SET &NUM2 = 0;

ENGINE SQLORA SET DEFAULT_CONNECTION DBTEST
SQL SQLORA PREPARE SQLOUT FOR
select num
from dual
;
END

TABLE FILE SQLOUT
PRINT
NUM
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END

-RUN
-READ HOLD &NUM1.D20.2
-IF &NUM1 NE 2.00 THEN :EFFDATE;

ENGINE SQLORA SET DEFAULT_CONNECTION DBTEST
SQL SQLORA PREPARE SQLOUT FOR
select num
from dual
;
END

TABLE FILE SQLOUT
PRINT
NUM
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END

-RUN
-READ HOLD &NUM2.D20.2
-:EFFDATE
-SET &EFF_DATE = IF &NUM1 EQ 2 AND &NUM2 EQ 1.00 THEN &DATEMINUS1 ELSE &DATEMINUS2;

TABLE FILE CAR
PRINT
CAR
FOOTING
"&EFF_DATE "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
END

Ok, I've come across my next obstacle and have yet to find a solution.

I need to store the amper variable value in a field/column to store as a hold file which will be referenced in another procedure.

Having the variable in the heading or footing does not make it available outside of the procedure. I've tried creating a virtual field via a define as well as utilizing the compute function, neither seem to work. Any suggestions are greatly appreciated!

Not sure if this is what you want on your 2nd request:

-SET &VAR1 ='ANY VALUE' ;
-SET &VAR2 = 12345.67 ;
DEFINE FILE CAR
VAR1 /A15 = '&VAR1' ;
VAR2 /D12.2 = &VAR2 ;
END
TABLE FILE CAR
HEADING
"LINE 1 WITH VAR1: &VAR1 "
"LINE 2 WITH VAR2: &VAR2 "
PRINT SALES
RCOST
DCOST
VAR1 VAR2
BY COUNTRY
ON TABLE HOLD AS HOLD1
END
TABLE FILE HOLD1
HEADING
"LINE 1 WITH VAR1: &VAR1 "
"LINE 2 WITH VAR2: &VAR2 "
PRINT *
END
-RUN
-EXIT

Thank you Jim, this works great!