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.


Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Left Outer Join
 Login/Join
 
Gold member
posted
Wow....who knew a join could be so complicated. I have been trying to pull this report since Monday to no avail. Here's my issue...I have three tables. Table1 has financial aid (money), Table2 has Rooms, and Table3 has Meals. I want all people who have money and have a room, meal or both. I'm trying to create a left outer join because I realize that the query was only pulling students who had money of course, but had both room and meal. I want them even if they only have room or meal. I'm working in dev studio procedure viewer and I've changed the join to single instance left outer but the report still looks the same. The documentation is very vague. Is there something else I need to do. Please help!!!!


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Guru
posted Hide Post
Kitten,

Have you tried SET ALL=ON ?

Fernando


Prod WF 8.1.04, QA WF 8.2.03, Dev WF 8.2.03
 
Posts: 278 | Registered: October 10, 2006Report This Post
Gold member
posted Hide Post
No I haven't...where would i do that? Do i use the set icon (picture of light switch)? Hate to sound ignorant...but the truth is I am still learning. So much to digest!


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Expert
posted Hide Post
Yes, use the SET icon and look for ALL. I would also suggest that you go to Dev Studio help and read up on what it does for you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
You can open up the Fex in the Text Editor right click on it and stick it at the top. Or try the light switch...I've never done it that way peronally.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
Thanks that's exactly what I'm doing...reading up on the set tool.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Expert
posted Hide Post
I meant the ALL setting. That is what is going to control your JOIN.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Gold member
posted Hide Post
I've reviewed the ALL setting. I added the setting to the procedure, ran the report and got the same results. Any ideas??????


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Guru
posted Hide Post
Do you mind posting your focexec?

Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Expert
posted Hide Post
Also, what is your data source type? Can you post the masters as well if they are not too large?


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Gold member
posted Hide Post
SET ALL = ON

JOIN
LEFT_OUTER AT_AR_BALANCE_BY_ENTITY.AT_AR_BALANCE_BY_ENTITY.PIDM_KEY IN
AT_AR_BALANCE_BY_ENTITY TO UNIQUE SLRRASG.SLRRASG.SLRRASG_PIDM IN SLRRASG AS J4
END
JOIN
LEFT_OUTER AT_AR_BALANCE_BY_ENTITY.AT_AR_BALANCE_BY_ENTITY.PIDM_KEY IN
AT_AR_BALANCE_BY_ENTITY TO UNIQUE SLRMASG.SLRMASG.SLRMASG_PIDM IN SLRMASG AS J3
END
DEFINE FILE AT_AR_BALANCE_BY_ENTITY
Name/A40V=LAST_NAME || (', ' | FIRST_NAME) || (' ' | MIDDLE_INITIAL);
END
TABLE FILE AT_AR_BALANCE_BY_ENTITY
BY Name NOPRINT
BY ID
BY Name
BY SLRRASG_TERM_CODE AS 'Term Code'
BY SLRMASG_MRCD_CODE AS 'Meal Plan'
BY SLRMASG_BEGIN_DATE AS 'Meal Begin Date'
BY SLRMASG_END_DATE AS 'Meal End Date'
BY SLRMASG_AR_IND AS 'Meal AR IND'
BY SLRRASG_BLDG_CODE AS 'Building'
BY SLRRASG_ROOM_NUMBER AS 'Room No.'
BY SLRRASG_BEGIN_DATE AS 'Room Begin Date'
BY SLRRASG_END_DATE AS 'Room End Date'
BY AMOUNT_DUE AS 'Amount Due'
BY MEMO_BALANCE AS 'Memo Balance'
HEADING
""
FOOTING
""
WHERE SLRRASG_TERM_CODE EQ '&SLRRASG_TERM_CODE.Term Code.';
WHERE SLRRASG_ASCD_CODE EQ 'AC';
WHERE SLRMASG_TERM_CODE EQ '200809';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=BOLD,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BACKCOLOR=( 'WHITE' RGB(207 213 183) ),
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
COLUMN=ROWTOTAL(1),
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BACKCOLOR=( RGB(220 227 234) RGB(243 246 248) ),
STYLE=BOLD,
$
TYPE=TITLE,
COLOR='WHITE',
BACKCOLOR=RGB(153 6 0),
STYLE=-UNDERLINE+BOLD,
$
TYPE=TITLE,
COLUMN=ROWTOTAL(1),
BACKCOLOR=RGB(0 51 102),
STYLE=BOLD,
$
TYPE=TABHEADING,
BORDER-TOP=OFF,
BORDER-BOTTOM=OFF,
BORDER-LEFT=OFF,
BORDER-RIGHT=OFF,
SIZE=12,
COLOR=RGB(153 6 0),
JUSTIFY=CENTER,
$
TYPE=TABFOOTING,
SIZE=8,
COLOR=RGB(153 6 0),
$
TYPE=HEADING,
BORDER-TOP=HEAVY,
BORDER-BOTTOM=HEAVY,
BORDER-LEFT=OFF,
BORDER-RIGHT=OFF,
BORDER-TOP-STYLE=GROOVE,
BORDER-TOP-COLOR=RGB(153 6 0),
BORDER-BOTTOM-COLOR=RGB(153 6 0),
BORDER-LEFT-COLOR=RGB(0 51 102),
BORDER-RIGHT-COLOR=RGB(0 51 102),
COLOR=RGB(153 6 0),
JUSTIFY=CENTER,
$
TYPE=FOOTING,
SIZE=8,
$
TYPE=SUBHEAD,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
SIZE=12,
COLOR=RGB(153 6 0),
BACKCOLOR='SILVER',
JUSTIFY=CENTER,
$
TYPE=SUBHEAD,
BY=2,
BORDER-TOP=MEDIUM,
BORDER-BOTTOM=MEDIUM,
BORDER-LEFT=MEDIUM,
BORDER-RIGHT=MEDIUM,
$
TYPE=SUBHEAD,
BY=2,
LINE=1,
JUSTIFY=LEFT,
$
TYPE=SUBHEAD,
BY=2,
LINE=2,
JUSTIFY=LEFT,
$
TYPE=SUBFOOT,
COLOR=RGB(153 6 0),
BACKCOLOR=RGB(212 212 212),
$
TYPE=SUBTOTAL,
BORDER-TOP=HEAVY,
BORDER-BOTTOM=OFF,
BORDER-LEFT=OFF,
BORDER-RIGHT=OFF,
BORDER-TOP-COLOR=RGB(153 6 0),
COLOR=RGB(153 6 0),
BACKCOLOR='SILVER',
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(153 6 0),
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLOR=RGB(0 12 25),
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Gold member
posted Hide Post
FILENAME=AT_AR_BALANCE_BY_ENTITY, SUFFIX=SQLORA , $
SEGMENT=AT_AR_BALANCE_BY_ENTITY, SEGTYPE=S0, $
FIELDNAME=PIDM_KEY, ALIAS=PIDM_KEY, USAGE=P9, ACTUAL=P5, $
FIELDNAME=ENTITY_IND, ALIAS=ENTITY_IND, USAGE=A1V, ACTUAL=A1V,
MISSING=ON, $
FIELDNAME=ID, ALIAS=ID, USAGE=A9V, ACTUAL=A9V, $
FIELDNAME=SSN, ALIAS=SSN, USAGE=A9V, ACTUAL=A9V,
MISSING=ON, $
FIELDNAME=LAST_NAME, ALIAS=LAST_NAME, USAGE=A60V, ACTUAL=A60V, $
FIELDNAME=FIRST_NAME, ALIAS=FIRST_NAME, USAGE=A15V, ACTUAL=A15V,
MISSING=ON, $
FIELDNAME=MIDDLE_NAME, ALIAS=MIDDLE_NAME, USAGE=A15V, ACTUAL=A15V,
MISSING=ON, $
FIELDNAME=MIDDLE_INITIAL, ALIAS=MIDDLE_INITIAL, USAGE=A1V, ACTUAL=A1V,
MISSING=ON, $
FIELDNAME=NAME_PREFIX, ALIAS=NAME_PREFIX, USAGE=A20V, ACTUAL=A20V,
MISSING=ON, $
FIELDNAME=NAME_SUFFIX, ALIAS=NAME_SUFFIX, USAGE=A20V, ACTUAL=A20V,
MISSING=ON, $
FIELDNAME=ACCOUNT_BALANCE, ALIAS=ACCOUNT_BALANCE, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=AMOUNT_DUE, ALIAS=AMOUNT_DUE, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=MEMO_BALANCE, ALIAS=MEMO_BALANCE, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=DEPOSIT_BALANCE, ALIAS=DEPOSIT_BALANCE, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=OLDEST_EFFECTIVE_DATE, ALIAS=OLDEST_EFFECTIVE_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
MISSING=ON, $



FILENAME=SLRMASG, SUFFIX=SQLORA ,
REMARKS='Meal Assignment Table', $
SEGMENT=SLRMASG, SEGTYPE=S0, $
FIELDNAME=SLRMASG_PIDM, ALIAS=SLRMASG_PIDM, USAGE=P9, ACTUAL=P5,
DESCRIPTION='This field defines the internal identifier associated with the meal assignments', $
FIELDNAME=SLRMASG_TERM_CODE, ALIAS=SLRMASG_TERM_CODE, USAGE=A6V, ACTUAL=A6V,
DESCRIPTION='This field defines the term code associated with the meal assignments', $
FIELDNAME=SLRMASG_MRCD_CODE, ALIAS=SLRMASG_MRCD_CODE, USAGE=A4V, ACTUAL=A4V,
DESCRIPTION='This field defines the meal plan code', $
FIELDNAME=SLRMASG_BEGIN_DATE, ALIAS=SLRMASG_BEGIN_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field identifies the first day of the meal assignment', $
FIELDNAME=SLRMASG_END_DATE, ALIAS=SLRMASG_END_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field identifies the last day of the meal assignment', $
FIELDNAME=SLRMASG_TOTAL_DAYS, ALIAS=SLRMASG_TOTAL_DAYS, USAGE=P11.5, ACTUAL=P5,
DESCRIPTION='This field identifies the total number of days of the meal assignment', $
FIELDNAME=SLRMASG_TOTAL_MONTHS, ALIAS=SLRMASG_TOTAL_MONTHS, USAGE=P9.5, ACTUAL=P4,
DESCRIPTION='This field identifies the total number of months of the meal assignment', $
FIELDNAME=SLRMASG_TOTAL_TERMS, ALIAS=SLRMASG_TOTAL_TERMS, USAGE=P9.5, ACTUAL=P4,
DESCRIPTION='This field identifies the total number of terms of the meal assignment', $
FIELDNAME=SLRMASG_MSCD_CODE, ALIAS=SLRMASG_MSCD_CODE, USAGE=A2V, ACTUAL=A2V,
DESCRIPTION='This field identifies the meal plan status code of the meal assignment', $
FIELDNAME=SLRMASG_MSCD_DATE, ALIAS=SLRMASG_MSCD_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field defines the date the meal plan status code was last updated', $
FIELDNAME=SLRMASG_ONL_OR_BAT, ALIAS=SLRMASG_ONL_OR_BAT, USAGE=A1V, ACTUAL=A1V,
DESCRIPTION='This field identifies whether the meal assignment was created on-line or batch using the batch scheduler', $
FIELDNAME=SLRMASG_ACTIVITY_DATE, ALIAS=SLRMASG_ACTIVITY_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field identifies the date the record was created or last updated', $
FIELDNAME=SLRMASG_AR_IND, ALIAS=SLRMASG_AR_IND, USAGE=A1V, ACTUAL=A1V,
DESCRIPTION='This field identifies whether the meal assignment charges have been processed', $
FIELDNAME=SLRMASG_ROLL_IND, ALIAS=SLRMASG_ROLL_IND, USAGE=A1V, ACTUAL=A1V,
MISSING=ON,
DESCRIPTION='This field controls whether the meal assignment can be rolled using the roll forward process', $
FIELDNAME=SLRMASG_ASSESS_NEEDED, ALIAS=SLRMASG_ASSESS_NEEDED, USAGE=A1V, ACTUAL=A1V,
DESCRIPTION='This field identifies whether fee assessment is needed for the meal assignment', $
FIELDNAME=SLRMASG_DATA_ORIGIN, ALIAS=SLRMASG_DATA_ORIGIN, USAGE=A30V, ACTUAL=A30V,
MISSING=ON,
DESCRIPTION='DATA SOURCE: Source system that created or updated the row', $
FIELDNAME=SLRMASG_USER_ID, ALIAS=SLRMASG_USER_ID, USAGE=A30V, ACTUAL=A30V,
MISSING=ON,
DESCRIPTION='USER ID: User who inserted or last update the data', $



FILENAME=SLRRASG, SUFFIX=SQLORA ,
REMARKS='Room Assignment Table', $
SEGMENT=SLRRASG, SEGTYPE=S0, $
FIELDNAME=SLRRASG_PIDM, ALIAS=SLRRASG_PIDM, USAGE=P9, ACTUAL=P5,
DESCRIPTION='This field defines the internal identifier associated with the room assignments', $
FIELDNAME=SLRRASG_BLDG_CODE, ALIAS=SLRRASG_BLDG_CODE, USAGE=A6V, ACTUAL=A6V,
DESCRIPTION='This field identifies the building code of the room assignment', $
FIELDNAME=SLRRASG_ROOM_NUMBER, ALIAS=SLRRASG_ROOM_NUMBER, USAGE=A10V, ACTUAL=A10V,
DESCRIPTION='This field identifies the room number of the room assignment', $
FIELDNAME=SLRRASG_TERM_CODE, ALIAS=SLRRASG_TERM_CODE, USAGE=A6V, ACTUAL=A6V,
DESCRIPTION='This field defines the term associated with the room assignments', $
FIELDNAME=SLRRASG_RRCD_CODE, ALIAS=SLRRASG_RRCD_CODE, USAGE=A4V, ACTUAL=A4V,
DESCRIPTION='This field defines the room rate code associated with the assignment', $
FIELDNAME=SLRRASG_BEGIN_DATE, ALIAS=SLRRASG_BEGIN_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field identifies the first day of the room assignment', $
FIELDNAME=SLRRASG_END_DATE, ALIAS=SLRRASG_END_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field identifies the last day of the room assignment', $
FIELDNAME=SLRRASG_TOTAL_DAYS, ALIAS=SLRRASG_TOTAL_DAYS, USAGE=P11.5, ACTUAL=P5,
DESCRIPTION='This field identifies the total number of days of the room assignment', $
FIELDNAME=SLRRASG_TOTAL_MONTHS, ALIAS=SLRRASG_TOTAL_MONTHS, USAGE=P9.5, ACTUAL=P4,
DESCRIPTION='This field identifies the total number of months of the room assignment', $
FIELDNAME=SLRRASG_TOTAL_TERMS, ALIAS=SLRRASG_TOTAL_TERMS, USAGE=P9.5, ACTUAL=P4,
DESCRIPTION='This field identifies the total number of terms of the room assignment', $
FIELDNAME=SLRRASG_ASCD_CODE, ALIAS=SLRRASG_ASCD_CODE, USAGE=A2V, ACTUAL=A2V,
DESCRIPTION='This field identifies the status code of the room assignment', $
FIELDNAME=SLRRASG_ASCD_DATE, ALIAS=SLRRASG_ASCD_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field defines the date the room plan status code was last updated', $
FIELDNAME=SLRRASG_ONL_OR_BAT, ALIAS=SLRRASG_ONL_OR_BAT, USAGE=A1V, ACTUAL=A1V,
DESCRIPTION='This field identifies whether the room assignment was created on-line or batch using the batch scheduler', $
FIELDNAME=SLRRASG_ACTIVITY_DATE, ALIAS=SLRRASG_ACTIVITY_DATE, USAGE=HYYMDS, ACTUAL=HYYMDS,
DESCRIPTION='This field identifies the date the record was created or last updated', $
FIELDNAME=SLRRASG_AR_IND, ALIAS=SLRRASG_AR_IND, USAGE=A1V, ACTUAL=A1V,
DESCRIPTION='This field identifies whether the room assignment charges have been processed', $
FIELDNAME=SLRRASG_OVERLOAD_IND, ALIAS=SLRRASG_OVERLOAD_IND, USAGE=A1V, ACTUAL=A1V,
MISSING=ON,
DESCRIPTION='This field identifies whether an overload condition existed to allow the room assignment', $
FIELDNAME=SLRRASG_ROLL_IND, ALIAS=SLRRASG_ROLL_IND, USAGE=A1V, ACTUAL=A1V,
MISSING=ON,
DESCRIPTION='This field controls whether the room assignment can be rolled using the roll forward process', $
FIELDNAME=SLRRASG_OVERRIDE_ERROR, ALIAS=SLRRASG_OVERRIDE_ERROR, USAGE=A1V, ACTUAL=A1V,
MISSING=ON,
DESCRIPTION='This field identifies whether an error override condition existed to allow the room assignment', $
FIELDNAME=SLRRASG_ASSESS_NEEDED, ALIAS=SLRRASG_ASSESS_NEEDED, USAGE=A1V, ACTUAL=A1V,
DESCRIPTION='This field identifies whether fee assessment is needed for the room assignment', $
FIELDNAME=SLRRASG_DATA_ORIGIN, ALIAS=SLRRASG_DATA_ORIGIN, USAGE=A30V, ACTUAL=A30V,
MISSING=ON,
DESCRIPTION='DATA SOURCE: Source system that created or updated the row', $
FIELDNAME=SLRRASG_USER_ID, ALIAS=SLRRASG_USER_ID, USAGE=A30V, ACTUAL=A30V,
MISSING=ON,
DESCRIPTION='USER ID: User who inserted or last update the data', $


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Expert
posted Hide Post
Here is one thing that you can look up:

SET MULTIPATH

However, since your data source is an RDBMS, i.e. Oracle, using this setting or the SET ALL setting will disable optimization and WebFOCUS will do the join anyway. If your tables are large, this might not be a good thing to do. I could go on and on here about relational efficiencies but I won't for now. Why don't you play with this for now and let us know how it goes.

We may have to create some sample data and construct a model for you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
It sounds like this would be easier if you used MATCH FILE to find the people you are looking for. MATCH FILE is built for just this type of comparison.

I do not use DS so I cannot tell you how to create this in DS.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
Sorry I stepped out to lunch. GinnyJakes...I will try the Set Multipath.

mgrackin it's interesting you said that I was just reading up on that before I went to lunch. Now to figure out how to use it in DS.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Virtuoso
posted Hide Post
Kitten,

DS can be a big help. But you may want to learn how to use the code...because it is a big help to understanding what you are doing. As you can see by the majority of posts here on the Forum...they are mainly referencing code. So don't let it scare ya. Wink
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
OK I tried the Set MULTIPATH...both simple and compound....no luck. I'll read up more on the match file and try it.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Virtuoso
posted Hide Post
Your problem could be 2 things...before you try doing a Match
1. Your Where Statments try elimintating one or more and see what you get
2. taking out the word Unigue and the Left Outer Join. With SET ALL=ON it is Equivalent to a left outer Join when using ALL. Try changing Unique to ALL. or eliminating all together.
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
Hi Prarie...I tried the 2nd suggestion. I changed the single instance to multiple and change the joins from left outer to unspecified and set All on. Is that the same?????? Well here's the error I got.

0 ERROR AT OR NEAR LINE 171 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Virtuoso
posted Hide Post
Well it was a thought. Keep playing. Try Mickey's suggestion with a Match. Anyone else have an Idea?
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
thanks for your patience....i've already started the match.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Virtuoso
posted Hide Post
We need to be able to remote into peoples computer sometimes Wink
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Platinum Member
posted Hide Post
One problem is that you have WHERE clauses in your Table and logically these are saying that any missing data fails the Where test. Hence they are negating the Left outer join.

You have to put the WHERE statements inside of the join. Its a feature called JOIN-WHERE and is documented.


Release 7.6.9
Windows
HTML
 
Posts: 226 | Registered: June 08, 2003Report This Post
Virtuoso
posted Hide Post
The setting of ALL is irrelevant for a Unique join. But as it stands the joins are not unique.

The key is to move the Where's regarding room assignment and mealplan assignment attributes into the respective joins (making them what the WF documentation refers to as "conditional" joins). That tells Focus that the uniqueness is true only after applying the Where condition(s).

Focus should generate appropriate SQL to pull balance-table columns, and the matching room-table columns (populated if a matching qualified row it exists; nulls/zeros/blanks if not), and the matching meal-table columns (similarly).

Verify: inspect the returned data, and desk-check the generated SQL. The data at this point should pick up all students satifying balance conditions, and their current term's room and meal assignment data (populated or null as the case may be). You would expect that all four possible combinations
of room present or null and meal present or null will be representetd in the answerset.

Then add a condition to the TABLE request:
WHERE TOTAL (...) OR (...);
WF should add a corresponding HAVING clause in the generated SQL, to exclude any answer-set rows where both room and meal data returned are 'null'.

Verify again, and that should do it.

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
Hi GeraldCohen,

Your suggestion makes a lot of sense. Since yesterday I've been fighting with the match and getting the same results. I do believe it is my where clause. So now I'm back to the join....however, I can't find the documentation on Join-Where. I've been reading up on conditional joins. In Dev Studio..in the join dialog box I don't see a tool that allows for a Join-Where. How do I put the where statements on the join? I'm becoming very discouraged....it seems you have to know some focus language to get reports.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Gold member
posted Hide Post
Hi j.g....

Where is this documentation? I'm using the help in Dev Studio. Is there something else I should be looking at that could help me more? Someone may have given me the info....but when I took the class there was so much info given.....


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Gold member
posted Hide Post
OK guys...i'm sorry i found the webfocus technical documentation on the webfocus website. so i'm reading it now....pray for me


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Expert
posted Hide Post
kitten,

In DS Help, Contents, Search type in:

"conditional join"

with the double quotes...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
Thanks Tom...it's the same info i've been reading. Most of it gives syntax examples. I guess I have to learn focus language. They sold us on the fact that we could do most things with the gui tool Frowner(((. Not that easy!!!!!


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Expert
posted Hide Post
kitten,

GUI only?? Well, there are plenty of threads on here regarding GUI only philosophy.

Anyway, the 5th one down, Creating a Join in Developer Studio, explains how to do a conditional join in the GUI.

That may help...

Learning the syntax, though, will take you a lot further with WF, and, you'll be able to show off to your peers.

Have fun!!

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders