Transform group error 
Author Message
 Transform group error

---------------------------------------
Hello , guys :

I just insert the data into the table, the SQL is like below .
insert into Committee (oid,SSN,Name,Address,email,gender,passd,Role)
        \
 values(                                                                \
        Committee_t('1'),                                               \
        '123456789',                                                    \
        Name_t()..FirstName('Jodie')                                    \
                ..LastName('Chang'),                                    \
        Address_t()..Street('North Road')                               \
                   ..Zip('11790')                                       \
                   ..City('Stony Brook')                                \
                   ..State('NY')                                        \
                   ..Country('USA'),                                    \

        'F',                                                            \
        '123',                                                          \
        'M'                                                             \
        )                                                               \
After the data insert successfully , I try to select the committee
table ,
it show the error below :
==========================================================
db2 => select * from committee
SQL20015N  A transform group "DB2_PROGRAM" is not defined for data
type
"CSE532_4.ADDRESS_T".  SQLSTATE=42741

I read about the developer guide page 319 , it said if I don't specify
a tansform function , DB2 attempts to use the name "DB2_Function" for
function/method or "DB2_program" when precomplie a client program,
isn't it ?
If so , why the error said transform group "DB2_PROGRAM" is not
defined ?
I am really confused now, Any one knows what's wrong ?

I also attach my ADDRESS_T schema as below :

Create Type Address_t AS \
(                        \
 Street         Varchar(128), \
 Zip            Varchar(16),  \
 City           Varchar(64),  \
 State          Varchar(32),  \
 Country        VarChar(64)   \
)                             \
MODE DB2SQL                   \



Tue, 14 Sep 2004 15:08:57 GMT
 Transform group error


Quote:
> ---------------------------------------
> Hello , guys :

> I just insert the data into the table, the SQL is like below .
> insert into Committee (oid,SSN,Name,Address,email,gender,passd,Role)
>    \
>  values(                                                           \
>    Committee_t('1'),                                               \
>    '123456789',                                                    \
>    Name_t()..FirstName('Jodie')                                    \
>            ..LastName('Chang'),                                    \
>    Address_t()..Street('North Road')                               \
>               ..Zip('11790')                                       \
>               ..City('Stony Brook')                                \
>               ..State('NY')                                        \
>               ..Country('USA'),                                    \

>    'F',                                                            \
>    '123',                                                          \
>    'M'                                                             \
>    )                                                               \
> After the data insert successfully , I try to select the committee
> table ,
> it show the error below :
>==========================================================
> db2 => select * from committee
> SQL20015N  A transform group "DB2_PROGRAM" is not defined for data
> type
> "CSE532_4.ADDRESS_T".  SQLSTATE=42741

> I read about the developer guide page 319 , it said if I don't specify
> a tansform function , DB2 attempts to use the name "DB2_Function" for
> function/method or "DB2_program" when precomplie a client program,
> isn't it ?
> If so , why the error said transform group "DB2_PROGRAM" is not
> defined ?
> I am really confused now, Any one knows what's wrong ?

> I also attach my ADDRESS_T schema as below :

> Create Type Address_t AS \
> (                   \
>  Street    Varchar(128), \
>  Zip               Varchar(16),  \
>  City              Varchar(64),  \
>  State             Varchar(32),  \
>  Country   VarChar(64)   \
> )                        \
> MODE DB2SQL                      \

The behaviour is correct.  You are just missing one piece.

With a structured type, you have ta have a way to convert it to a single
scalar value.  The type for that value has to be based on one of the builtin
types (INTEGER, VARCHAR, BLOB, ...).  The problem is that db2 does not know
_how_ to convert a value of your Address_t to such a builtin type.  You have
to define a (FROM SQL) transform function for that.  Transform functions
have to be grouped into transform groups.

The information you refer to in the Application Development Guide explains
the algorithm that db2 uses to find the transform group.  If you did not
explicitly identify a group using the SQL statement SET CURRENT DEFAUST
TRANSFORM GROUP = ..., then db2 will use the DB2_PROGRAM group.  However,
you have to create that group for your type first.  You didn't do th't, so
db2 couldn't find it and you got the mentioned error.

To define the group, do something like this (untested):

CREATE FUNCTION AddressTransfm ( addr Address_t )
   RETURNS VARCHAR(300)
   LANGUAGE SQL
   RETURN addr..Street || ' ' || addr..city || ', ' || addr..state || ' ' ||
          addr..State;

(You can change the return clause in whichever way you want.)

CREATE TRANSFORM FOR Address_t DB2_PROGRAM (
   FROM SQL WITH FUNCTION AddressTransfm );

Do the same for the Name_t type.

Then run your SELECT again.  Now, db2 will find the DB2_PROGRAM group, see
that it should use the function AddressTransfm() to convert Address_t values
to a builtin type (VARCHAR in this case) and convert the address according
to what the function defined.

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab



Wed, 15 Sep 2004 00:48:26 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. how to transform rows into columns group by column

2. Transform groups (more FE/BE protocol issues)

3. Transform groups

4. In a Query: TRANSFORM, GROUP BY, HAVING AND PIVOT

5. What is a transform group?

6. ERROR: Unable to transform targetlist (internal error)

7. ERROR: Unable to transform targetlist (internal error)

8. Peculiar DTS Transform error

9. application error - transform data task

10. Error: DataDrivenQuery: one or more destination parameter columns had no transform

11. Transform Data Task Error

12. Error doesn't say which transform.


 
Powered by phpBB® Forum Software