This program code was working before we upgraded to webfocus 7, so I think there should be a work around, but haven't been able to figure it out.

TABLE FILE TABLE_NAME
COUNT ID AS 'COUNT'
BY COLL_CODE
BY DEPT1
BY COURSE
BY CREDIT_HOURS
BY COURSE_TITLE
BY CRN_KEY
BY TERM_CODE_KEY
ACROSS GRDE_CODE_FINAL AS 'COURSE GRADE' COLUMNS 'A' AND 'B' AND 'C' AND 'D' AND 'F'
WHERE (TERM_CODE_KEY EQ '200620');
IF SEQ_NUMBER GE 01
IF GRDE_CODE_FINAL NE 'DR'
IF DEPT1 EQ '44'
ON TABLE COLUMN-TOTAL
ON TABLE HOLD AS TEMP1
END

The code always crashes on the "COLUMNS 'A' AND 'B' AND 'C' AND 'D' AND 'F'" part. In my hold file, I want to be able to call the fields as COUNTA, COUNTB, COUNTC, COUNTD, COUNTF (or some other field name) just like I used to, for each of the different courses. I even tried taking the above line out, and calling the fields like E01, E02, etc, but for some reason it isn't assigning this alias names to the columns.

Anyone have any ideas how I can get it to work again?

SET ASNAMES = ON
TABLE FILE CAR
COUNT SALES AS COUNT BY COUNTRY ACROSS CAR
COLUMNS BMW AND JAGUAR
ON TABLE HOLD
END
TABLE FILE HOLD PRINT *
END
..make sure you have SET ASNAMES = ON
and you get

COUNTRY COUNTBMW COUNTJAGUAR 
ENGLAND 0 2 
W GERMANY 6 0 

if you leave the AS phrase out, you get

COUNTRY SALBMW SALJAGUAR 
ENGLAND 0 2 
W GERMANY 6 0 

, the first 3 letters of the verb object measure.
I put SET ASNAMES = ON in my edasprof, just 'cause i use it all the time; maybe you had it in your pre-upgrade edasprof and not in your current one?

I tried this:
SET ASNAMES=ON
TABLE FILE TABLE_NAME
COUNT ID AS COUNT BY CRN_KEY ACROSS GRDE_CODE_FINAL
COLUMNS A AND B AND C AND D AND F
WHERE (TERM_CODE_KEY EQ '200620');
ON TABLE HOLD
END
TABLE FILE HOLD PRINT *
END

And got the following:
Unknown error occurred.
Agent on reporting server EDASERVE may have crashed.
Please investigate reporting server log.

ERROR:



I then tried this:
SET ASNAMES=ON
SET NODATA = ,$
SET PAGE-NUM=ON
-*******************************************************************************
TABLE FILE TABLE_NAME
COUNT ID AS COUNT BY CRN_KEY ACROSS GRDE_CODE_FINAL
-*COLUMNS A AND B AND C AND D AND F
WHERE (TERM_CODE_KEY EQ '200620');
ON TABLE HOLD
END
TABLE FILE HOLD PRINT *
END

And got the following:

CRN_KEY COUNTA COUNTA& COUNTA COUNTA& COUNTA COUNTA COUNTA COUNTA COUNTA COUNTA .........
20001 7 0 0 0 0 0 0 0 0 .......
20002 7 0 0 0 0 0 0 0 0 .......
20003 5 0 0 0 0 0 0 0 0 .......
20004 1 0 0 0 0 0 0 0 0 .......
20005 5 0 0 0 0 0 0 0 0 .......
................................

I'm getting around 15+ duplicates for each field. I can look for just the A(IF GRDE_CODE_FINAL EQ A) and get only 1 column, but when I look for all of them in my if, I get all the duplicate columns again.

looks like you've got whacky data in your grade field.
Do this:
TABLE FILE tablename
BY GRDE_CODE_FINAL
END
..and then show us what you get

The table is just a little too big to run something like this against, but I did run a count for all grades for the term I was checking:
10098,A
229,A&
17,AT
7232,B
5,B&
4183,C
1,C&
1369,D
4,DR
1240,F
7,F&
300,I
38,I/A
15,I/B
6,I/C
4,I/D
4,I/F
37,IP
7,P
312,W

They all seem to be valid entries.

Part of the reason for naming the columns, is that I don't need all of them. And since the ASNAMES isn't naming them properly, I can't call them without naming them.

Have you tried -

TABLE FILE TABLE_NAME
WRITE CNT.ID AS COUNT BY CRN_KEY ACROSS GRDE_CODE_FINAL 
-*COLUMNS A AND B AND C AND D AND F
WHERE (TERM_CODE_KEY EQ '200620');
ON TABLE HOLD 
END
TABLE FILE HOLD
PRINT *
END

Without your data, or a good representation, I am unable to test it. However, it's worth a try ...

T

j,
you have values in your grade field
of things other than A,B,C,D,F
you have I, F&, W, etc, 20 values in all
so you'ld get more than 5 across values.
so your results; COUNTA COUNTA& ...etc
are exactly what your data are.
If you only want grades A,B,C,D,F
then put a WHERE statement in your fex.
[the code i had asked you to run would give you just 20 lines of output, 1 line for each Grade value.]
ASNAMES isn't going to affect the values in the grade field. ASNAMES is only going to affect the name you give the measure field, in this case COUNT.
the columns are named by a concatenation of the measure field COUNT and the grade value A A& etc, so ASNAMES isn't the issue.

Are you saying that , in general, in version 7 (which release?) that the COLUMNS command is just flat busted? Can you try it on the CAR file?

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

T:
When I do this:

TABLE FILE TABLE_NAME
WRITE CNT.ID AS COUNT BY CRN_KEY ACROSS GRDE_CODE_FINAL
-*COLUMNS A AND B AND C AND D AND F
WHERE (TERM_CODE_KEY EQ '200620');
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
END

I get around 120 columns with each column repeating. Such as 15 column COUNTA. But only 1 column of COUNTA has a count in it. (See above example of my output, its way too big to post here)


S:
I did get the COLUMNS command to work using the car example:
COUNTRY COUNTBMW COUNTJAGUAR
ENGLAND 0 2
W GERMANY 6 0

However it still won't work on my example. Still working on it.

Looks like our version is 7.1.3

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

Here's the results when I run the trace on it:
Code:
SET XRETRIEVAL = OFF
-*-- Activate tracing ---
SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
-******************
SET ASNAMES=ON
SET NODATA = ,$
SET PAGE-NUM=ON
DEFINE FILE TABLE_NAME
CRS1/A6 = EDIT (CRSE_NUMBER, '99-999');
DEPT1/A2 = EDIT (CRSE_NUMBER, '99');
COURSE/A10 = CRS1||'-'||SEQ_NUMBER;
END
TABLE FILE TABLE_NAME
COUNT ID AS 'COUNT'
BY CRN_KEY
BY TERM_CODE_KEY
ACROSS GRDE_CODE_FINAL AS COURSE_GRADE COLUMNS A AND B AND C AND D AND F AND P AND I AND AT AND W AND IP AND A& AND B& AND C& AND D& AND F&
IF TERM_CODE_KEY EQ '200620'
IF SEQ_NUMBER GE 01
IF GRDE_CODE_FINAL NE 'DR'
IF DEPT1 EQ '44'
ON TABLE HOLD
END
TABLE FILE HOLD PRINT *
END



Results:
AGGREGATION DONE ...
SELECT T1."CRN_KEY",T1."TERM_CODE_KEY",T1."GRDE_CODE_FINAL",
COUNT(*) FROM BANINST1.TABLE_NAME T1 WHERE
(T1."GRDE_CODE_FINAL" <> 'DR') AND (T1."SEQ_NUMBER" >= '01')
AND (T1."TERM_CODE_KEY" = '200620') AND (T1."GRDE_CODE_FINAL"
IN('A', 'B', 'C', 'D', 'F', 'P', 'I', 'AT', 'W', 'IP', 'A&',
'B&', 'C&', 'D&', 'F&')) AND ((SUBSTR(T1."CRSE_NUMBER", 1, 2))
= '44') GROUP BY T1."CRN_KEY",T1."TERM_CODE_KEY",
T1."GRDE_CODE_FINAL" ORDER BY T1."CRN_KEY",T1."TERM_CODE_KEY",
T1."GRDE_CODE_FINAL";
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


Does anything look out of the ordinary here?