Originally posted by: pratikd1987

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 

Originally posted by: PaulBrett

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

Originally posted by: pratikd1987

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

Originally posted by: PaulBrett

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

Originally posted by: pratikd1987

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.

Originally posted by: PaulBrett

=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

Originally posted by: pratikd1987

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

Originally posted by: PaulBrett

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

Thank you.

Paul

Follow me on Twitter

Originally posted by: pratikd1987

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

Originally posted by: PaulBrett

Still not told us which database.

Thank you.

Paul

Follow me on Twitter

Originally posted by: pratikd1987

@PaulBrettibm

Hey Paul,

Apologies for the late reply.

I was OOO and away from an internet connection.

We are using SQL Server 2016

Originally posted by: pratikd1987

Hey Paul,

Let me know if you need more information .

@paulbrettibm

Thanks

Originally posted by: PaulBrett

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

Paul

Follow me on Twitter

Attachment(s)

SQL.zip   2 KB 1 version

Originally posted by: pratikd1987

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

Originally posted by: pratikd1987

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

Attachment(s)

paulbrettdbl.txt   6 KB 1 version

Originally posted by: PaulBrett

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

Originally posted by: PaulBrett

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

Attachment(s)

SQL2.zip   6 KB 1 version

Originally posted by: pratikd1987

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 

I 

Originally posted by: PaulBrett

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