IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 18 replies
  • Latest Post - ‏2019-05-23T07:15:42Z by PaulBrett
pratikd1987
pratikd1987
24 Posts

Pinned topic Unique Identifier Field Default Value

‏2019-05-01T14:03:41Z | @paulbrettibm

Hi Paul,

 

I am currently mapping to a  databasefield with UniqueIdentifier datatype.

Could you let me know how we can populate a default value in such a field.

 I have tried the NewID() function but WTX doesnot recognize it.

Also cant but NULL as it is a mandatory not null field.

Please advise

 

Thanks and Regards,

Pratik 

  • PaulBrett
    PaulBrett
    371 Posts
    ACCEPTED ANSWER

    Re: Unique Identifier Field Default Value

    ‏2019-05-20T15:02:05Z  

    I was able to successfully insert data into the table using the attached map.

    Paul

    Follow me on Twitter

    Attachments

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-02T07:15:34Z  

    Have you tried generating a unique id using ITX functions?  What format does this id need to be?  

    How about this:  https://www-01.ibm.com/support/docview.wss?uid=swg27049846

    Or this : https://www.ibm.com/developerworks/community/forums/html/topic?id=4c6fbb32-03bc-4016-b0f9-501793c64062

    Thank you.

    Paul

    Follow me on Twitter

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-02T15:48:03Z  
    • PaulBrett
    • ‏2019-05-02T07:15:34Z

    Have you tried generating a unique id using ITX functions?  What format does this id need to be?  

    How about this:  https://www-01.ibm.com/support/docview.wss?uid=swg27049846

    Or this : https://www.ibm.com/developerworks/community/forums/html/topic?id=4c6fbb32-03bc-4016-b0f9-501793c64062

    Thank you.

    Paul

    Follow me on Twitter

    Hey @paulbrettibm,

    The first link with GUID doesnot work.The design studio doesnot seem to treat it as a valid sunction.

    For the second link should i just use the "SYMBOL" statement as a UUID?

    I am trying to populate Unique Identifier as a datatype and wanted to populate a default value for that field.

    Let me know if you need more information.

     

    Thank you so much for the help.

    Thanks

     

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-02T16:42:34Z  

    For a unique value, what you need to do is:

    1.  Build the GenerateUUID map and copy the .MMC file into the same directory as your map.

    2.  In your map, where you need the unique ID, enter this rule:

    =RUN("GenerateUUID","-OE1")

    For a default value, the rule needs to be:

    ="DEFAULT"

    (I'm still not really clear on what you're trying to do.)

    Thank you.

    Paul

    Follow me on Twitter

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-02T16:45:47Z  
    • PaulBrett
    • ‏2019-05-02T16:42:34Z

    For a unique value, what you need to do is:

    1.  Build the GenerateUUID map and copy the .MMC file into the same directory as your map.

    2.  In your map, where you need the unique ID, enter this rule:

    =RUN("GenerateUUID","-OE1")

    For a default value, the rule needs to be:

    ="DEFAULT"

    (I'm still not really clear on what you're trying to do.)

    Thank you.

    Paul

    Follow me on Twitter

    Hi Paul,

     

    Thank you for the reply.

    All I am trying to do is fill a default value for a unique identifier field in the map rule.

    I tried GETITXUID () but there it wasnt recognized as a valid function.

     

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-02T16:51:22Z  

    =resourcelib->GETITXUID ()

    ...only works in ITX 9.0.0 and I guess you're using WTX 8.4.1 or earlier.

    How can a default value be unique?  Every row is going to end up with the same value.

    Thank you.

    Paul

    Follow me on Twitter

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-02T17:30:01Z  
    • PaulBrett
    • ‏2019-05-02T16:51:22Z

    =resourcelib->GETITXUID ()

    ...only works in ITX 9.0.0 and I guess you're using WTX 8.4.1 or earlier.

    How can a default value be unique?  Every row is going to end up with the same value.

    Thank you.

    Paul

    Follow me on Twitter

    Hey Paul,

     

    Thank you for the information above.

    I tried using the command above.however,it seems it doesnt match the output subtype which is a database column with type Unique Identifier.

    I am using Version: 9.0.0.2 
    Build id: 65 

    Thanks

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-03T16:28:07Z  

    What database?  Can you supply SQL to create an example table?

    Thank you.

    Paul

    Follow me on Twitter

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-03T16:53:05Z  
    • PaulBrett
    • ‏2019-05-03T16:28:07Z

    What database?  Can you supply SQL to create an example table?

    Thank you.

    Paul

    Follow me on Twitter

    Hi ,

     

    The db table i am trying to insert is something like this

     

    USE [sampledb]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[sample](
        [sample1] [int] IDENTITY(1,1) NOT NULL,
        [sample2] [uniqueidentifier] NOT NULL,
        [sample3] [uniqueidentifier] NOT NULL,
        
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].sample] ADD  DEFAULT (getdate()) FOR [sampledate]
    GO


     

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-08T08:01:08Z  

    Still not told us which database.

    Thank you.

    Paul

    Follow me on Twitter

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-17T15:47:33Z  
    • PaulBrett
    • ‏2019-05-08T08:01:08Z

    Still not told us which database.

    Thank you.

    Paul

    Follow me on Twitter

    @PaulBrettibm

    Hey Paul,

     

    Apologies for the late reply.

    I was OOO and away from an internet connection.

    We are using SQL Server 2016

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-20T14:49:42Z  
    • PaulBrett
    • ‏2019-05-08T08:01:08Z

    Still not told us which database.

    Thank you.

    Paul

    Follow me on Twitter

    Hey Paul,

     

    Let me know if you need more information .

    @paulbrettibm

     

    Thanks

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-20T15:02:05Z  

    I was able to successfully insert data into the table using the attached map.

    Paul

    Follow me on Twitter

    Attachments

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-21T17:51:55Z  
    • PaulBrett
    • ‏2019-05-20T15:02:05Z

    I was able to successfully insert data into the table using the attached map.

    Paul

    Follow me on Twitter

    Hey Paul,

     

    Thank you very much for the SQL map.

    However, when i try the resoucelib function I get a compilation error and am not able to compile the map.

    Unless i dont put the function in the sizedgroup field.

    If I put it insde the column then it compiles fines but doesnt put any data when i run the map.

    Please advise.

    Thanks

     

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-21T18:41:34Z  
    • PaulBrett
    • ‏2019-05-20T15:02:05Z

    I was able to successfully insert data into the table using the attached map.

    Paul

    Follow me on Twitter

    Hey Paul,

     

    PFA a dbl of the recent run.

    I have added comments inside the file to better demonstrate my situation.

    I have also attached a pic to show how i have used the getituxid function.

    Let me know if more information is required.

    Thanks

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-22T07:16:55Z  

    Your DBL indicates that your table has many more columns that the sample SQL you gave me.  Please create the table exactly as I did (only 3 columns) and then use my map unaltered.

    Thank you.

    Paul

    Follow me on Twitter

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-22T07:45:13Z  

    Here's a better pair of maps, each using a different method to generate the unique data.  I've also tweaked the rules to ensure a better insert.

    Thank you.

    Paul

    Follow me on Twitter

    Attachments

  • pratikd1987
    pratikd1987
    24 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-22T19:13:55Z  
    • PaulBrett
    • ‏2019-05-22T07:45:13Z

    Here's a better pair of maps, each using a different method to generate the unique data.  I've also tweaked the rules to ensure a better insert.

    Thank you.

    Paul

    Follow me on Twitter

    Hey Paul,

    I tried to use the logic above.However I am still getting the same error as before.

    I have attached the errors i received in the pics below.

    Also even though my maps have multiple columns they still have the same initial configurations for the unique identifier fields.

    Will it cause an issue if the number of columns are greater than the ones specified in the map?

    Thank you so much for your assistance in this matter.

    Thanks 

  • PaulBrett
    PaulBrett
    371 Posts

    Re: Unique Identifier Field Default Value

    ‏2019-05-23T07:15:42Z  

    If your map does not match your database columns, then yes, you're going to have a problem.

    It seems you need more help than I am reasonably able to provide in the context of a a developerWorks post.  I think the time has come for you to open a support Case with IBM.

    If you do not have a support contract with IBM, I suggest you get one.

    Thank you.

    Paul

    Follow me on Twitter