Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Problem : Insert null values

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default Problem : Insert null values

    Attachment: trans_Customer.ktr Hi !


    I have got a table_input called Customers follows by a select_values.
    I have got a get_system_info that I join with my select_values with a cartesian product. Then in output I have got table_output.
    Problem : no insert because spoon say it can't be able to put an "null" values in primary key but I don't give it a "null" value !



    When I change my output with a Dimension update/lookup, it works but at each execution of the transformation spoon insert a row with only "null" values and the other are correct !
    How can I solve my problem ?
    Thanks

  2. #2
    Join Date
    May 2006
    Posts
    138

    Default RE: Problem : Insert null values

    Dimension update/lookup and table output supply different functionality and the 0 row is intended behaviour for dimension update/lookup.


    Can you run your original transformation with "row level" logging on, and supply us the logging part around where spoon complains about null values. You will probably see null values there.



    If possible also supply the DDL for the table (not from the step, but extracted from your database).



    Regards,
    Sven

  3. #3
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Problem : Insert null values

    Attachment: trans_Customer_table_output.ktr With an insert/update and a table_output, the error is :
    Insert / Update.0 - ERROR : Error in step, asking everyone to stop because of:
    Insert / Update.0 - ERROR : Error inserting row
    Insert / Update.0 - ERROR : Impossible d'insérer la valeur NULL dans la colonne 'Client_ID', table 'DW_Northwind.dbo.D_Client'. Cette colonne n'accepte pas les valeurs NULL. INSERT a échoué


    My output table :
    Client_ID char(5) pk not null
    Entreprise varchar (40)
    Nom varchar(30)
    Pays varchar(15)
    Maj datetime



    My customers table doesn't contain an ID with a null value ! I don't understand...



    Thanks for your help.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: Problem : Insert null values

    Specify the key as well in the lower part of the dialog.

    Matt

  5. #5
    Join Date
    May 2006
    Posts
    138

    Default RE: Problem : Insert null values

    Can you also supply the information you get with "row level" logging... for the rows causing the problems


    Regards
    Sven

  6. #6
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Problem : Insert null values

    The key is the Client_ID.

    Nicolas

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: Problem : Insert null values

    I know. Specify Client_ID also in the list of update fields as suggested by the "Get update fields" button.

    Also: consider using slowly changing dimensions for your data warehouse. You'll thank me later.

    HTH,
    Matt

  8. #8
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: Problem : Insert null values

    (Please set the content format of your messages to Plain, thanks, Matt)

    DW_Northwind - Setting preparedStatement to [SELECT Maj, Entreprise, Nom, Pays FROM D_Client WHERE Client_ID = ? ]
    DW_Northwind - Preparing statement:
    DW_Northwind - INSERT INTO D_Client(Maj, Entreprise, Nom, Pays) VALUES ( ?, ?, ?, ?)
    DW_Northwind - Setting update preparedStatement to [UPDATE D_Client
    DW_Northwind - SET Maj = ?
    DW_Northwind - , Entreprise = ?
    DW_Northwind - , Nom = ?
    DW_Northwind - , Pays = ?
    DW_Northwind - WHERE Client_ID = ? ]
    Insert / Update.0 - Checking row: Client_ID=ALFKI, Entreprise=Alfreds Futterkiste, Nom=Roger, Pays=France, Maj=2006/05/31 14:16:17.821
    Insert / Update.0 - Field [Client_ID] has nr. 0
    Insert / Update.0 - Field [Maj] has nr. 4
    Insert / Update.0 - Field [Entreprise] has nr. 1
    Insert / Update.0 - Field [Nom] has nr. 2
    Insert / Update.0 - Field [Pays] has nr. 3
    Insert / Update.0 - Values set for lookup: [Client_ID=ALFKI]
    Insert / Update.0 - Insert row: !Client_ID=ALFKI, Entreprise=Alfreds Futterkiste, Nom=Roger, Pays=France, Maj=2006/05/31 14:16:17.821
    Insert / Update.0 - ERROR : Error in step, asking everyone to stop because of:
    Insert / Update.0 - ERROR : Error inserting row
    Insert / Update.0 - ERROR : Impossible d'insérer la valeur NULL dans la colonne 'Client_ID', table 'DW_Northwind.dbo.D_Client'. Cette colonne n'accepte pas les valeurs NULL. INSERT a échoué.
    Insert / Update.0 - Signaling 'output done' to 1 output rowsets.

    Client_ID=ALFKI, it is not null !

    Thanks for your help.

    Nicolas

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: Problem : Insert null values

    Again: specify Client_ID also in the list of update fields as suggested by the "Get update fields" button.

  10. #10
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Problem : Insert null values

    Thank you very much for your help ! It works !!!
    I didn't want to put this key because I thought spoon would have update it !

    PS : sorry for the content format, I didn't see the option.

    Again : thanks for your help !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.