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     Cannot get IF THEN ELSE TO WORK

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Cannot get IF THEN ELSE TO WORK
 Login/Join
 
Jim Dale
Silver Member
posted
I have the following code:
JOIN CDTACCT WITH CDTCOCODE IN AMZRCDTA TO ALL PUBACCTNBR IN AMZRPUBA AS J1

DEFINE FILE AMZRCDTA
CDTACCT/P10 = IF CDTSTANBR NE 0 THEN CDTSTANBR ELSE CDTBTANBR;
END

TABLE FILE AMZRCDTA
PRINT CDTACCT CDTSTANBR PUBACCTNBR
WHERE RECORDLIMIT EQ 10
END
-RUN

I receive the following error:
Unknown error occurred.
Agent on reporting server EDASERVE may have crashed.
Please investigate reporting server log.
ERROR

We're using version 7.13 WebFocus and 7.14 developer studio. Unix - Solaris with Oracle tables.

Any help is most appreciated.

Thanks,
Jim
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report This Post
Jim Dale
Silver Member
posted Hide Post
I tried the following IF statement and it worked:

CDTACCT/P10 = IF CDTSTANBR NE 0 THEN CDTSTANBR ELSE IF CDTSTANBR EQ 0 THEN CDTBTANBR ;

Does anyone know why the 1st method wouldn't work?

Thanks,
Jim
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report This Post
Darin Lee
Virtuoso
posted Hide Post
Is CDTSTANBR ever null? This is not the same as 0. Your second statement would basically ignore null records, while your first statement would try to set them to 0 and then perform the join. Whenever I create a define-based join, I make sure that I have evaluated EVERY possible value. Your second one hits closer to that mark.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Jim Dale
Silver Member
posted Hide Post
Thanks Darin for the quick turn around.

I checked and the field does not have any NULLs.

At least we got it to work, I was just wondering why the 1st statement didn't work.

Jim
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report This Post
Piipster
Guru
posted Hide Post
quote:
CDTACCT/P10 =
IF CDTSTANBR NE 0
THEN CDTSTANBR
ELSE
IF CDTSTANBR EQ 0
THEN CDTBTANBR ;


I'd actually be wary about using an IF-THEN-ELSE statement that doesn't have the ELSE. Your second one is missing the ELSE portion of the statement. If for some reason you ever run into a situation where the THEN can't be applied you may have unexpected results on one or more rows.

How did you determine there are no null values?
Did you investigate the Reporting Server Log as per the error message?
Are you running the request from Dev Studio, Managed Reporting, or from the Reporting Server console.

I would probably want to run the failing request from the reporting server console(to eliminate other layers from the problem diagnostic) with tracing turned on to determine what the real issue is.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
hammo1j
Master
posted Hide Post
You need to trace the SQL created using XRETRIEVAL=OFF etc - various methods posted round on forum. I know nested IF then ELSE is not passed thru correctly on Oracle adapter.

Here are some params to allow you to vary translation OPTIFTHENELSE being most obvious.

ENGINE SQLORA SET ORACHAR VAR
ENGINE SQLORA SET OPTIFTHENELSE ON
ENGINE SQLORA SET FETCHSIZE 5000
ENGINE SQLORA SET INSERTSIZE 500
ENGINE SQLORA SET VARCHAR OFF


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Alan B
Virtuoso
posted Hide Post
I agree with kp that not having an ELSE in your statement could lead to anomolies.

If

CDTACCT/P10 = IF CDTSTANBR NE 0 THEN CDTSTANBR ELSE IF CDTSTANBR EQ 0 THEN CDTBTANBR ;

works, then so should:

CDTACCT/P10 = IF CDTSTANBR NE 0 THEN CDTSTANBR ELSE CDTBTANBR ;

As that fails, then you do have a value/values which cannot be compared to 0, so would follow kp's advice.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Jim Dale
Silver Member
posted Hide Post
Thanks for everyone's reponse. I checked for NULLS by issuing the following command:

COUNT *
WHERE CDTSTANBR IS MISSING
and received a 0.

COUNT *
WHERE CDTBTANBR IS MISSING
and received a 0

I also displayed the field with
BY CDTBTANBR and lowest number I got was a 0,
the rest were 9 byte account numbers.

Did the same with the CDTSTANBR field.

I'm running the fex from Dev Studio.

Jim
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report This Post
Alan B
Virtuoso
posted Hide Post
I suppose that if it's working, and you are getting the correct number of records then you have to say this works. For completness I would use:
CDTACCT/P10 = IF CDTSTANBR NE 0 THEN CDTSTANBR ELSE IF CDTSTANBR EQ 0 THEN CDTBTANBR ELSE 0 ;

It does seem a bit of a weirdy to me though.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Jim Dale
Silver Member
posted Hide Post
I checked with the mainframe programmers and those 2 fields I’ve referred to are required fields. If the user does not place an account number in the field, then it is auto-filled with a zero. The mainframe programmers checked the VSAM file (the source) and there are no NULL fields.

I guess for the sake of getting this project finished (migrating mainframe reports to webfocus) we’re going to go with:

CDTACCT/P10 = IF CDTSTANBR NE 0 THEN CDTSTANBR ELSE IF CDTSTANBR EQ 0 THEN CDTBTANBR ELSE 0 ;

Thanks everyone for your responses, they are most appreciated.

Jim
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report 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     Cannot get IF THEN ELSE TO WORK

Copyright © 1996-2020 Information Builders