I am fairly new to WebFocus so I'm sure this won't be my only post.

Currenty I am running a report that I need to list all email addresses that student has listed. I have tried defining each email and doing a hold file for each email, then joining all those and I still end up with each email being listed in its own row.

I have eliminated the hold files and just done joins (keeping my defines) and still get output on different rows. When I sum everything, if in my defines I have an "else ' '" then since the last row only has 1 of the emails and all others are blank, when summed it only shows that last row and "overwrites" the others that should be listed. When I take out the "else ' '" in my define, it doesn't overwrite but instead, when a student actually doesn't have one of the emails, it will fill in with another person's email address.

I have also tried, joining to my email table 3 separate times and then doing a where statement to get the output I want. This actually seemed to work as with each join in WebFocus in puts a J1, J2 etc in front of the table name. I stopped using this as I was told by others in our state that this is not the best way to do this.

What is really need is it to output everything all on 1 line and if a student has 1 email listed, then list the 1 email and leave the other columns blank, but if a student has 3 listed then list the emails in the 3 respective columns.

Thanks!
Malinda

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

What you need is some sort of "pivot in reverse" in which you want to transpose multiple records into a single column ... I think we need a new version of the McGyver technique for this one

Anyway, here's something you can attempt:

1. Make sure you select the list of students and their email addresses in a HOLD file *sorted* by Student Id.

2. Using this hold, you can DEFINE a virtual field that analyzes the previous record for the same student (check documentation for the LAST function) and if the same, appends that email address to the new one ... the OVRLAY function comes to my mind but please check documentation on this one as well to make sure it is suited to your needs.

3. Using the SUM verb, get the last instance of the "virtual" field you just created (LST.) which should contain all of the email addresses in a single string.

Regards,
- Neftali.

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

This will give you an idea, though its little complicated.

-SET &ECHO=ALL;

TABLE FILE CAR
SUM
CAR
BY COUNTRY
ACROSS CAR AS ''
ON TABLE HOLD AS HLD_1
END

?FF HLD_1


TABLE FILE HLD_1
PRINT
COUNTRY
COMPUTE CAR_S/A1024 =E02 | E03 | E04 | E05 | E06 | E07 | E08 | E09 | E10 | E11;
END

TABLE FILE CAR
BY COUNTRY
BY CAR
END

Thanks Neftali. I will try this and see what I can do. I am just learning all this language

Malinda

Neftali,

I have other tables that i will need to join to, is it better to create that hold file sorted by ID for the emails first and then join that hold as a child table to my parent table?

Here's an example using the CAR table to illustrate the concept I originally expressed:

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE HOLD AS HCARS
END
-*
DEFINE FILE HCARS
CAR_LIST/A255 = IF COUNTRY NE LAST COUNTRY THEN CAR ELSE 
                OVRLAY(LAST CAR_LIST, 255, ','|CAR, ARGLEN(21, CAR, 'I4')+1, ARGLEN(255, LAST CAR_LIST, 'I4') + 1, CAR_LIST);
END
TABLE FILE HCARS
SUM LST.CAR_LIST
BY COUNTRY
END

Now, based on your last question, this is something you can try:

1. Using the technique described above, get the list of students and their email addresses in a single column. HOLD this new result as a FOCUS file with an INDEX on student id. This will be used as a look-up table later. Let's call this file HSTUDENT.

2. Issue a new query in you database to retrieve whatever transaction information you need for all of your students (forget about emails since we already have those "offline") and HOLD that result set sorted by student ID as well. Let's call this new one HTRANS.

3. Now it's time to join your HTRANS file (transactions by student) to our new look-up table (HSTUDENT) based on student ID.

4. Voilà! You should have all of the information now.

Hope this helps,
- Neftali.

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

Malinda,

The sample I provided can be executed as is so you can grasp the concept.

Ram's code shows a key element that you must keep in mind: you have to have a clear expectation as to how many email addresses will be found and how long the result would be after you concatenate them all in a single string. Ram decided to use an alpha field of 1024 bytes which, in my opinion, is much more appropriate that the shy 255 I used for my test. You'll have to make this call on your own as only you know your data but be careful not to exceed the limitation in length of an alphanumeric field which should be well documented (sorry, I don't have the number in my head right now).

Best,
- Neftali.

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

quote:

I am just learning all this language

WebFOCUS is a very powerful tool and although I've found this forum to be an excellent source of help, it just cannot provide you with enough fundamental knowledge of what WebFOCUS is about.

You will most likely need some training not only in the language but the platform itself as well as lots (and lots and lots) of documentation reading.

Good luck!
- Neftali.

Thanks for that I and will work on it. I want to some training in the Fall but that was really a basic introduction. I am finding out a lot just by using and coming up with lots of questions!

Malinda

On another note, somebody also mentioned to me that it is best to avoid hold files? Doesn't make sense to me and guessing on your previous responses that is not true. They were the ones that suggested joining to the table 3 times to get what I need. Joining to a table multiple times - should this even be used?

Malinda

quote:

it is best to avoid hold files?

Whoever told you that probably does not have a clear understanding of what WebFOCUS is about. The possibility of saving temporary information, transforming it, aggregating it, "massaging" it if you will is one of the things that set WebFOCUS apart when it comes to other "reporting" tools, but WF is much more than just reporting!

Yes, I can agree to a certain extent that in an ideal world, all of the information you'd ever need for your reports should be readily available and efficiently aggregated in a database and all we would ever need is just to query, put some makeup and deliver. And don't get me wrong, it is always advisable to let the database do as much of the data crunching and filtering as possible since most frequent than not it will be much more efficient than reading a whole lot of information through the network, saving it to temporary files and reading it again.

In a more realistic world however, it is not always possible to achieve that unless you can consider in advance ALL of your possible information needs and design your database accordingly. Yes, that should be part of a more holistic design but in most environments I've worked that is not the case and you just have to use whatever is available.

Creating a new table or view or materialized view or whatever structure in a centralized database is not something *we* as WF developers can always do; there are dependencies on data architects and DBAs as well and unless what we need fits in a more general-purpose or global benefit to the whole organization, they won't just alter database structures for the benefit of a couple of reports. Of course, I'm generalizing here as not all environments are like that plus you get the idea.

HOLD files give you some degree of control over what you need to implement (data aggregation, transposing as in your case, join to other sources unknown to the database itself) so why not take advantage of them?

Could you live without HOLD files? Yes, but it won't be easy.

Should you avoid using them? Well, it all depends. Don't use them unnecessarily! If you need to produce a detail report with 60,000 student transactions in the last year and you can accomplish that reading directly from the database and formatting it in WF, then why would you save 60,000 records to a HOLD file just to read them all over again?

Sorry, a simple NO to your question would have been sufficient but I just couldn't HOLD it (no pun intended)

- Neftali.

quote:

Joining to a table multiple times - should this even be used?

Well, it may be necessary in some cases such as recursive joins. For instance, let's say you have an EMPLOYEE table which lists all of the employees in your organization. The table might have an EMPLOYEE_ID, NAME, ROLE, etc. as well as a MANAGER_ID field (which naturally corresponds to the Employee ID of a manager).

If you want a report of employees by manager, you'll need to join the table to itself in order to have access to both employee and manager's information.

Thanks for the reply on the hold files.

Recursive joins - so this could maybe be stated that if you need a report sort by field 1 and a value in field 1 could also be a valid value in another field that would be listed as a detail? So if I want to list email (which has 3 different types) and list all 3 types if a student has them in respective columns would this justify joining table2 to table1 3 times?

quote:

if you need a report sort by field 1 and a value in field 1 could also be a valid value in another field that would be listed as a detail

If the value in field1 can logically be "linked" to the value in a different field in the same table in such a way that you can have some sort of hierarchy, then I would say yes! Recursive joins are generally applicable when you have certain hierarchy or dependency among records in the same table. For example:

Employee <-> Manager, when manager is also an employee and both records exist in the same EMPLOYEE table but obviously in different records.

City <-> State, State <-> Country would make an interesting hierarchy if there is an entry for a "geographical location" along with a field that specifies the "parent geographical location" both of which exist as individual records in the same table.

Sooooo, if you can somehow visualize some kind of hierarchy among the students' email addresses then a recursive join would be useful but as I said before, only you have the knowledge of your data so you are the one in the best position to determine what approach should be taken.

There are usually more than one solutions to a single problem and you'll just have to try different approaches until you find the one which hopefully has the less complexity (to avoid maintenance nightmares in the future) while providing an acceptable performance.

Again, please go over the fundamental concepts of WebFOCUS but keep an eye open to what your database and other tools in your overall platform can do for you. Making use of one old saying, WebFOCUS makes an excellent hammer but not every requirement is a nail!

Regards,
- Neftali.

quote:

Now, based on your last question, this is something you can try:

1. Using the technique described above, get the list of students and their email addresses in a single column. HOLD this new result as a FOCUS file with an INDEX on student id. This will be used as a look-up table later. Let's call this file HSTUDENT.

2. Issue a new query in you database to retrieve whatever transaction information you need for all of your students (forget about emails since we already have those "offline") and HOLD that result set sorted by student ID as well. Let's call this new one HTRANS.

3. Now it's time to join your HTRANS file (transactions by student) to our new look-up table (HSTUDENT) based on student ID.

Here is my code for the 1st part of this - maybe you can tell me where I am going wrong

TABLE FILE CSPRDSC_SCC_EMAIL_QVW
PRINT
'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.E_ADDR_TYPE'
'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR'
BY 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS EMAILHLD FORMAT FOCUS INDEX 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
ENDSTYLE
END
DEFINE FILE EMAILHLD
EMAIL_LIST/A1024=IF CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID NE LAST CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID THEN CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR ELSE OVRLAY ( LAST EMAIL_LIST,1024, ','|CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR, ARGLEN(21,CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR, 'I4')+1, ARGLEN(1024, LAST EMAIL_LIST, 'I4')+1, EMAIL_LIST);
END
TABLE FILE EMAILHLD
SUM LST.EMAIL_LIST
BY CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID
ON TABLE HOLD AS EMHOLD2 FORMAT FOCUS INDEX 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
END

Using the max function worked. Thanks!