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!!!!

Kitten,

Have you tried SET ALL=ON ?

Fernando

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!

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.

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.

Thanks that's exactly what I'm doing...reading up on the set tool.

I meant the ALL setting. That is what is going to control your JOIN.

I've reviewed the ALL setting. I added the setting to the procedure, ran the report and got the same results. Any ideas??????

Do you mind posting your focexec?

Sayed

Also, what is your data source type? Can you post the masters as well if they are not too large?

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

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', $

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.

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.

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.

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.

OK I tried the Set MULTIPATH...both simple and compound....no luck. I'll read up more on the match file and try it.

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.

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

Well it was a thought. Keep playing. Try Mickey's suggestion with a Match. Anyone else have an Idea?

thanks for your patience....i've already started the match.

We need to be able to remote into peoples computer sometimes

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.

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,

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.

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.....

OK guys...i'm sorry i found the webfocus technical documentation on the webfocus website. so i'm reading it now....pray for me

kitten,

In DS Help, Contents, Search type in:

"conditional join"

with the double quotes...

Tom

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 (((. Not that easy!!!!!

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