MetaData Mismatch Warning

abhilashnair · Post by **abhilashnair** » Thu Nov 16, 2006 10:59 pm

I am using SUM(AMT) in the DB2 stage itself. The metadata is AMT(9,2) non nullable.. the query goes like this

SELECT SUM(AMT) AS AMT FROM <TABLENAME>

abhilashnair · Post by **abhilashnair** » Fri Nov 17, 2006 12:54 am

thebird wrote:I have faced similar problem- but when using the Aggregator Stage to do a summing. For the new aggregated column (which contains the aggregated value), the same warning was thrown, eventhough it was defined as Nullable NO.When I changed this to nullable YES, this warning disappeared.

I then included a modify stage to convert this field to a Nullable NO field.

Regards

The Bird

What exactly did u specify in the modify stage?

ArndW · Post by **ArndW** » Fri Nov 17, 2006 5:21 am

abhilashnair,

TheBird is using an aggregator stage, so the direct solution there does not apply, but it does illustrate what could be your problem. The metadata of "AMT" is not guaranteed to be the same as that for "SUM(AMT)" from the DB.

abhilashnair · Post by **abhilashnair** » Fri Nov 17, 2006 7:56 am

I am not able to view premium content

ArndW · Post by **ArndW** » Fri Nov 17, 2006 8:23 am

Sure, you can sign up for premium content at any time you wish to see a lot of informational and (hopefully) helpful content.

AMT(SUM) is a new column and not in your DDL, it can have different metadata. When you are sure that a warning message is handled appropriately, you can demote it to informational.

abhilashnair · Post by **abhilashnair** » Fri Nov 17, 2006 8:27 am

SUM(AMT) is basically summing AMT. So it is a derived function. It is not a new field. It is performing summation on the existing field AMT. So how can SUM(AMT) differ in metadata?

ArndW · Post by **ArndW** » Fri Nov 17, 2006 8:38 am

Let's see... you remove the SUMT() and it works, add the SUM() and you get a DS warning... so wouldn't it seem that this is what DS is doing?

ray.wurlod · Post by **ray.wurlod** » Fri Nov 17, 2006 9:13 am

Aggreagator calculated columns are delivered as dfloat.

There is a "Decimal Output" property that you can deploy to manage them as decimal. This is available for the output link as a whole (as Default to Decimal Output), or for individual fields (as Decimal Output).

abhilashnair · Post by **abhilashnair** » Fri Nov 17, 2006 9:52 am

I am afraid but I can't view the Premium Content. Anyway, we are getting off the track here. Where does this dfloat come from? I have used Aggregator in my job and have been able to see off one warning(Refer my first post in this thread. The first warning is not appearing now after using Aggregator). Now the only warning that remains is given below:

When checking operator: When binding output interface field "AMT" to field "AMT": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur; use the modify operator to
specify a value to which the null should be converted.

I dont know why this is happening. How can it tell that the source is nullable...when I am damn sure it is not. Everything right from the source DB2 table to target flat file is non nullable I repeat NON NULLABLE. Then why DS is saying "Converting a nullable source to a non-nullable result"

As TheBird suggested I am thinking of using modify stage to convert nullable to non nullable. But in modify stage, it asks for specification. What should I give there? What is the syntax for that?

ray.wurlod · Post by **ray.wurlod** » Fri Nov 17, 2006 11:37 am

Sign up. My previous post explained where the dfloat comes from. We need the funding to help to support the site - none of the premium posters is paid - it all goes to purchasing bandwidth and administration of the website.

abhilashnair · Post by **abhilashnair** » Sun Nov 19, 2006 12:11 am

My problem is un resolved as of yet. Can anyone tell me the syntax of the function for converting nullable to non nullable? I wil use this in the modify stage. I am trying to use NullToValue function on the field AMT. I want the modify stage to convert any null value coming out of the previous stage to be converted to 0.00.

ray.wurlod · Post by **ray.wurlod** » Sun Nov 19, 2006 9:59 am

There is an error in the Parallel Job Developer's Guide in the Modify stage chapter. The name of the function that handles null values is incorrectly given as NullToValue(), which is the Transformer stage's function.

In a Modify stage the function you need to use is handle_null().

But didn't this thread begin as a question about generation of dfloat data by the Aggregator stage? How has it morphed into handling NULL in the Modify stage?!!

abhilashnair · Post by **abhilashnair** » Mon Nov 20, 2006 5:32 am

I dont have access to premium content. What is the syntax for handling null values in modify stage. I want to convert null to non null

ray.wurlod · Post by **ray.wurlod** » Mon Nov 20, 2006 7:38 am

Sign up or Search. My earlier post explained where the dfloat comes from. We need the funding to help to support the site - none of the premium posters is paid - it all goes to purchasing bandwidth and administration of the website.

abhilashnair · Post by **abhilashnair** » Wed Nov 22, 2006 1:26 am

The problem has been resolved now. It was very simple. I wanted to carry forward the sum of the values of the field AMT from DB2 stage to a flat file. The metadata for AMT was decimal (9,2) and non nullable in the original table. But I came to know that when we perform a sum function in PX it always is defaulted as decimal(31,2) and nullable. So I just changed the metadata for the particular columnin the DB2 stage to suit the above. However the requirement was that in the flat file AMT should be decimal (9,2) and non nullable.

I put a transformer between the two stages wherein I declared a stage variable which had a null handling function. So the value of SUM(AMT) was loaded into the flat file as non nullable. In short decimal(31,2) was implicitly converted to decimal (9,2). That was cool and PX took care of that. No warnings were thrown.

One other thing..i am unable to mark this topic as resolved.Some bug in that. Please take a note