Is there a way to pass a field from one request to another to use in the WHERE statement (all in the same procedure)?

TABLE FILE DIM_PERIOD
PRINT
AGT_TERM_DT
WHERE AGT_NBR EQ '01234';
END
-*
TABLE FILE DIM_PERIOD
SUM
NET_SALES
BY ACCT_NBR
WHERE INV_DT EQ (AGT_TERM_DT from above);
END

Thanks!!

Krysti

Try searching on Drill Downs...you should find lots of discussion.

It's not really a drill down though.

TABLE FILE DIM_PERIOD
PRINT
AGT_TERM_DT
WHERE AGT_NBR EQ '01234';
-**************************
ON TABLE SAVE
END
-RUN
-READ SAVE &AGT_TERM_DT.A8.  (If AGT_TERM_DT format is YYYYMMDD:   20071023) 
        OR &AGT_TERM_DT.A10. (If AGT_TERM_DT format is YYYY/MM/DD: 2007/10/23)
-**************************
TABLE FILE DIM_PERIOD
SUM
NET_SALES
BY ACCT_NBR
-**************************
WHERE INV_DT EQ &AGT_TERM_DT;
-**************************
END
-RUN

Edit: Typo's

This message has been edited. Last edited by: Tom Flynn,

I presume that you expect a single value to be PRINTed by the first process.
In that case, try WRITEing it to a text file, then READing it back into an AmberVariable and using the AmberVariable in the second process's WHERE statement.

There are many FocalPoint threads explaining the WRITE then READ code needed to convert table row-column(s) into an &var(s).

What's wrong with WHERE IN FILE?

TABLE FILE DIM_PERIOD
PRINT
AGT_TERM_DT
WHERE AGT_NBR EQ '01234';
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-*
TABLE FILE DIM_PERIOD
SUM
NET_SALES
BY ACCT_NBR
WHERE INV_DT IN FILE H001
END

You can do either (1) TABLE/SAVE and then -READ it in or if you are hardocding the value do a (2) filedef then -write it out, and then -read it in

(1)
TABLE FILE CAR
PRINT CAR BY COUNTRY
WHERE COUNTRY EQ 'ITALY'
ON TABLE SAVE AS THESAVE
END
-RUN
-READ THESAVE &COUNTRY.A10.&CAR.A16.
TABLE FILE CAR
PRINT CAR BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY'
END
-EXIT


(2)
-PART2
FILEDEF XXX DISK MYSAVE.DAT (LRECL 10
-SET &KOUNTRY = ' ';
-RUN
-SET &KOUNTRY = 'ITALY';
-WRITE XXX &KOUNTRY.A10.
-TYPE THE VALUE FOR COUNTRY IS &KOUNTRY
-RUN
TABLE FILE CAR
PRINT MODEL RCOST DCOST AND COMPUTE PRRFIT/D12.2 = RCOST - DCOST;
BY COUNTRY BY CAR
WHERE COUNTRY EQ '&KOUNTRY'
END
-EXIT

ira 5.3.6 aix

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

PERFECT!!

Thanks, everyone. I was able to do exactly what I needed to by using both a -READ and the WHERE IN FILE.

You guys/gals are so smart :-)

Most appreciative,

Krysti

You can also set your WHERE value as a global variable (-SET &&VAR='1234' and use it anytime within the session.

This is nice

100 ways to get what you want

SET ASNAMES=ON
TABLE FILE DIM_PERIOD
PRINT
AGT_TERM_DT AS NEWTIME
WHERE AGT_NBR EQ '01234';
ON TABLE HOLD AS H_TIME FORMAT FOCUS INDEX AGT_TERM_DT
END
-*
Now create an inner join over the two tables DIM_PERIOD and H_TIME 
-*
-*
TABLE FILE DIM_PERIOD
SUM
NET_SALES
BY ACCT_NBR
WHERE INV_DT EQ NEWTIME;
END

and an other one

SET ASNAMES=ON
TABLE FILE DIM_PERIOD
PRINT AGT_TERM_DT
WHERE AGT_NBR EQ '01234'; 
ON TABLE HOLD AS H_TIME FORMAT ALPHA
END 
-RUN
-* 
-* 
DEFINE FILE DIM_PERIOD
SELDATA/I1=DECODE AGT_TERM_DT(H_TIME ELSE 1);
END
-* 
TABLE FILE DIM_PERIOD
SUM NET_SALES 
BY ACCT_NBR 
WHERE SELDATA EQ 0; 
END

Nice program WebFocus....