Trying to write a query that pulls a student's previous transfer colleges. I need the college name, beginning date, and ending date. The user wants a field for College 1, College 2, etc. The dates have to be in their own fields, too.

  
The database:

STUDENT ID   SEQUENCE   COLLEGE      BEGIN DATE   END DATE
1234         1          1st College  201301       0
             2          2nd College  200208       200305


What user wants:

STUDENT ID   COLLEGE 1     BEGIN    END   COLLEGE 2    BEGIN   END
1234         1st College   201301   0     2nd College  200208  200305

I solved the problem of assigning the college names to their own fields with this:

DEFINE FILE COLHIST1
	COL1/A32 = IF (SEQUENCE EQ 1) THEN COLLEGE_NAME;
	COL2/A32 = IF (SEQUENCE EQ 2) THEN COLLEGE_NAME;
	COL3/A32 = IF (SEQUENCE EQ 3) THEN COLLEGE_NAME;
END
  

But when I tried the same thing with the dates, all of them are completely off.

DEFINE FILE COLHIST1
	BEG_DT1/I9 = IF (SEQUENCE EQ 1) THEN BEGIN_DT;
	BEG_DT2/I9 = IF (SEQUENCE EQ 2) THEN BEGIN_DT;
	BEG_DT3/I9 = IF (SEQUENCE EQ 3) THEN BEGIN_DT;
	END_DT1/I9 = IF (SEQUENCE EQ 1) THEN END_DT;
	END_DT2/I9 = IF (SEQUENCE EQ 2) THEN END_DT;
	END_DT3/I9 = IF (SEQUENCE EQ 3) THEN END_DT;
END
  

I am wondering whether it has something to do with the date being a 9-character integer, because obviously what the database is showing is not 9 characters. And on the user's display screen, it shows up MM-YYYY. I have double-checked and I am grabbing the right field.

Any suggestions would be appreciated.

This message has been edited. Last edited by: <Kathryn Henning>,

Could you check the data type of BEGIN_DT and END_DT?

One way of checking is using:

-SET &ECHO=ALL;
... program ...
?FF COLHIST1
-EXIT

Then assign the same data type to BEG_DTx/END_DTx.

How about using ACROSS, you shouldn't have to worry about column formats:

TABLE FILE COLHIST1
SUM
COLLEGE
BEGIN DATE
END DATE
BY STUDENT_ID
ACROSS SEQUENCE AS ''
END

Thank you JL and Francis. Francis, I am not sure an ACROSS would work since this is just a very small part of a much larger program. The final report will have over 150 columns. (Yeah, they asked the new guy with no experience to do it. )

JL, that is a seriously awesome bit of code! That will help so much in the future. Thank you for that new tool!

Anyway, this is what the report says that the beginning and ending dates actually are

  
 BEGIN_DT                E05                     I11
 END_DT                  E06                     I11

Another programmer just stepped into my office and said something about the dates have to be DECODED and something about "packed" and pointed to our FOCUS manual. So, I guess I have some reading to do.

Since the fields are defined as I11, they're not "packed" and they don't need to be DECODED. Just use the same format in your DEFINE.

I'm not sure why ACROSS would not work here. If you create a HOLD file using ACROSS, you will end up with field names similar to the ones in your DEFINE, the SEQUENCE field makes the names different:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
TABLE FILE COLHIST1
SUM
COLLEGE
BEGIN DATE
END DATE
BY STUDENT_ID
ACROSS SEQUENCE
ON TABLE HOLD AS H001
END
-RUN

?FF H001
-RUN

The generated names will be abbreviated and perhaps not to your liking, but that shouldn't matter - it avoids having to define virtual fields for every SEQUENCE.

Perhaps "ACROSS SEQUENCE NOPRINT" in conjuction with LAST or MIN, or MAX (BEGIN or END DATEs) may be useful...

Sam,
I assume that for each student you have a sequence from 1 to whatever. Since you say that there is more than meets the eye (btw, who would want a report with 150 columns??? crazy!!!), I would suggest using some dialog manager to create fields for each college.

  
TABLE FILE COLHIST1
SUM MAX.SEQUENCE
ON TABLE SAVE
END
-RUN
-READ SAVE,&MAXSEQ
-RUN
DEFINE FILE COLHIST1
-REPEAT #FIELDS FOR &I FROM 1 TO &MAXSEQ;
COLLEGE&I / A32 = IF SEQUENCE EQ &I THEN COLLEGE_NAME ELSE ' ';
BDATE&I / I6 = IF SEQUENCE EQ &I THEN BEGIN_DATE ELSE 0;
EDATE&I / I6 = IF SEQUENCE EQ &I THEN END_DATE ELSE 0;
-#FIELDS
END

TABLE FILE COLHIST1
SUM 
-REPEAT #PRINT FOR &I FROM 1 TO &MAXSEQ;
MAX.COLLEGE&I MAX.BDATE&I MAX.EDATE&I
-#PRINT
BY STUDENT_ID
END

Thank you everyone for the suggestions. I just learned, however, that the data entry clerks use neither rhyme nor reason when entering the sequences. So, that puts the breaks on my efforts.

I do think these suggestions are fantastic. I want to try every one when I get back to that point. Again, thank you.

Why complicate matters?

TABLE FILE COLHIST1
PRINT
BEGIN DATE
END DATE
BY STUDENT_ID
ACROSS COLLEGE
END

You can have as many columns as you like per college...

Print and across?

I would say sum and across might work, but with a print you get not the report you like

Using an ACROSS gave me an error of "Max of 255 across instances in online report exceeded." But I think I have it figured out now. The real problem turned out to be that the SEQUENCE numbers were entered by the data entry clerks without rhyme or reason. So, I had to define my own. Once I did, a simple JOIN caused everything fall into place.

I appreciate everybody's help.

Oops ... You're right Frank. Should be SUM. And I echo someone else's remark - Who wants a report with more than 200 columns ??? I know that wide screens are available now, but still ...

George,
One hears so many.
When a customer asks me in WebFOCUS can retrieve 10,000 records, I ask, with incredulity, who might want to leaf through 10,000 lines of a report? The whole idea of BI is to allow the user to zoom in to what is desired.
Oh well...

I do agree to Danny on this matter.
BI is business intelligente. Convert data to Information.
Many users ask for data in a spreadsheet but in the end they are going to make a pivot report on the data we deliver.
When they take the time to look at the deeper possibilities we can offer, some make the next step.