Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Using variables in the where clause.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Using variables in the where clause.
 Login/Join
 
newtofocus
Platinum Member
posted
Hi I tried to add to my previous post of Accepting Parameteres but it would not let me do that. So I had to start a new one.

Steve,

Thanks for the response. How do I use these values in my where clause the following code refuses to work.

DEFINE FILE CUSTBT
BILL_MONTH/M=HI_BILLING_8_DATE;
BILL_YEAR/YY=HI_BILLING_8_DATE;
ND/YY=&INPUT_YEAR;
ND1/YYMD=ND;
YEAR_BEGIN/I8YYMD = DATECVT(ND1, 'YYMD', 'I8YYMD');
YEAR_END/YYMD = DATEMOV(ND1,'EOY');
END
TABLE FILE CUSTBT
SUM
CA_CUST_NAME
HI_EXT_PRICE
BY
CB_CUST_NUM
WHERE ( CB_MAJ_ACCT_NUM EQ '00004' ) AND ( HI_BILLING_8_DATE GE YEAR_BEGIN ) AND ( HI_BILLING_8_DATE LE YEAR_END );

I want the YEAR_BEGIN and YEAR_END values to be substituted. I doubt that I am not doing it right because when I see the SQL trace, I donot see the where clause passed to DB2 engine.

Thanks.
 
Posts: 176 | Location: Desplaines | Registered: August 05, 2004Report This Post
Leah
Virtuoso
posted Hide Post
It's my understanding when using a define based selection against DB2, the where is performed after the DB2 fetch. Do you have any real fields you can select on also?
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
GCohen
Platinum Member
posted Hide Post
If you want to substitute values for any object
in a report you need to use Amper variable, ie
&STARTDATE, and &ENDATE ..
Your WHERE should be something like..
WHERE ( CB_MAJ_ACCT_NUM EQ '00004' ) AND ( HI_BILLING_8_DATE GE &YEAR_BEGIN.Enter Beginning date. ) AND ( HI_BILLING_8_DATE LE &YEAR_END.Enter Ending date. );

Try running this in Developer Studio and a default Form should be generated asking you
for these two values.

You can then make up your own form, in Layout Tool, or whatever.
 
Posts: 226 | Registered: June 08, 2003Report This Post
Steve C
Silver Member
posted Hide Post
Well, there are a couple of issues. First, the date values in your database, are they Integer format, that is the date expressed as a number (ie. 20040916) or are they date format, which is expressed as the number of seconds since some base date? (You should be able to see this from the Actual format of the field in your Master file)

Your variable END_YEAR is in Date format (in Focus terms known as in new date format) and your YEAR_BEGIN is in Integer format (aka Old or Legacy date format in Focus). This will never work! You need to use the same format for both dates, as they are being compared to the same field.

Once you fix that, you need to use &INPUT_DATE with a prompt to get the user to enter the date. Something like

ND/YY='&INPUT_YEAR.Enter Year';
This will not work in Developer Studio, but is OK under MRE. How will you deploy this focexec in the end? That should dictate how you collect the data from the user. You may have to build a simple input screen in HTML to launch your procedure if it is not running through MRE.
 
Posts: 44 | Location: New York City | Registered: May 23, 2004Report This Post
Piipster
Guru
posted Hide Post
Dialog Manager Variables are the way to go.

-PROMPT &ENTER_YEAR.Enter the year.I4.
-SET &BEG_DATE = '01/01/' |
- EDIT(ENTER_YEAR,'$$99');
-SET &END_DATE = '12/31/' |
- EDIT(ENTER_YEAR,'$$99');


(if you want the 4 char year just use &ENTER_YEAR)

and in the WHERE statement

WHERE db_date GE 'BEG_DATE';
WHERE db_date LE '&END_DATE';
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Using variables in the where clause.

Copyright © 1996-2020 Information Builders