I want to improve the methodology for data modeling and I was interested in creating a crosstab.
But I cannot resolve.
Could you give me a hand?
CrossTable (Anno, PRIORITY)
RECNO AS NUMRECORD,
But is it normal that I could create a "new table containing only 2 fields?
I don't see a CONNECT statement in the section of the script in your screenshot. Perhaps you should move that section down below the section where you connect to the database.
sequence of my tables is.
1) newtable (table using tableticket fields)
2) TABLETICKET (table using as400 fields)
table is created using sql select DATA1 DATA2 DATA3 from ...
3) odbc connection (as400) containing
LIB CONNECT TO 'myapp';
Your problem here is that you have Anno and PRIORITY mentioned in the CROSSTABLE() and they also appear in the LOAD which is not permitted.
The CROSSTABLE prefix for LOAD or SELECT is meant to make a wide table with repeating columns into a much taller table with no repeating columns. The classic example is a budget table:
LOAD * INLINE [
Account, Jan, Feb, Mar, Apr, May, June ......
ABC, 10 , 11 , 12 , 10 , ......
DEF, 5 , 6 , 7 , 8 , 7 , .....
This can be transformed into a table that would look like this:
Account, Month, Budget
ABC, Jan, 10
ABC, Feb, 11
ABC, Mar, 12
DEF, May, 7
The CROSSTABLE LOAD would look like this:
Account,Jan,Feb,Mar,Apr,May,June .... // or simply *
NOTICE! Month and Budget are two NEW fields and they cannot appear in the following LOAD.
So this is often what is called an UNPIVOT operation pivoting you data clock-wise from a wide to a tall format.
The syntax is this:
CROSSTABLE( NewAttributeField, NewDataField, <number-of-existing-attribute-fields> )
The third parameter is optional and defaults to 1 if not specified. That's why the example above doesn't need it.
It is a specification of all attributes that should be kept as is and not be unpivoted.