cancel
Showing results for 
Search instead for 
Did you mean: 

Inserting two records in output table

Former Member
0 Kudos
53

Hello,

I have two columns in source table and need to insert two records in a single column in target table.

For example -

Source.ColA > 0 then insert 'XYZ' in target column

Source.ColB >0 then insert 'ABC' in target column

If both Source. Col A and Source.Col B >0 then insert 'XYZ' as one record and 'ABC' as other record.

How could i do that?

Appreciate your help.

Thanks,

Arun

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Connect your source table to a Query transform - say Query1.

Inside Query1:

In the Schema Out, create two fields - say FLAG1 and FLAG2 with type varchar (4)

For FLAG1, the mapping should be

IFTHENELSE(MAX(TABLE_NAME.ColA)>0,'XYZ',NULL)

For FLAG2, the mapping should be

IFTHENELSE(MAX(TABLE_NAME.ColB)>0,'ABC',NULL)

Use a PIVOT transform next to Query transform.

Inside Pivot transform:

Select both FLAG1 and FLAG2 as Pivot Columns. Leave the others untouched.

Connect one more Query transform - say Query2 after PIVOT transform.

Inside Query2:

You can see three input fields in Schema In - PIVOT_SEQ, PIVOT_HDR and PIVOT_DATA

Choose only PIVOT_DATA to the output and Rename the column as you wish. Should be of type varchar (4).

In the Where tab of Query2, say PIVOT_DATA IS NOT NULL

Connect Query 2 to a TARGET table. This will have one column with values generating as per your requirement.

Regards,

Suneer

Former Member
0 Kudos

Thanks Suneer.

I will try this and come back to you.

regards,

Arun

Former Member
0 Kudos

Suneer,

If the both the columns are > 0, then i need to have two records, one with XYZ and other with ABC.

The records will be re written or duplicated. For example if there are 10 columns in the output, then values in each column will be rewritten with one column value having XYZ and other with ABC.

Is that possible with the logic you mentioned.?

Appreciate your help.

Arun

Former Member
0 Kudos

Cool!! It is working Suneer.

Thankyou,

Arun

Former Member
0 Kudos

Hi Suneer,

I have another scenario, where i have to insert three records.

It is like this :

if input col IS NULL then insert record with 'NEW' in target table

if input col >0 then insert two records, one with 'NEW' and other with 'REPAIRED' in the target table.

I tried with your logic of PIVOT table, but in the output i am getting only two records. 'NEW' and 'REPAIRED'

For example i have in one input column with two records. First record is NULL and other record is >0. So in this case, i need three records - NEW, NEW and REPAIRED.

Appreciate if you can help me on this.

Arun

Former Member
0 Kudos

I understand the input is based on values in one column (Col_1).

Try this:

Map a query transform to your source table. As discussed earlier, let there be two fields - Flag_1 and Flag_2 with datatype varchar (10) in the output of Query transform.

In the mapping of Flag_1, use this: 'NEW' (hardcoded)

In the mapping of Flag_2, use this:

decode(TABLE.Col_1 > 0 ,'REPAIRED',NULL)

Map it into a PIVOT transform, then a Query transform, and target table after that. Inside configuration follows what has been mentioned in the earlier post.

Regards,

Suneer Mehmood.

Former Member
0 Kudos

If i am doing as suggested, only records with 'REPAIRED' is displayed and no records with 'NEW'.

Arun

Former Member
0 Kudos

Please don't use MAX in first Query transaformation mapping as it was in the earlier logic. That should definitely work

The below logic also will work and will be better i think. Try this

Query1:

Flag_1 :

DECODE(TABLE.Col1 <= 0,NULL,'NEW')

Flag_2 :

DECODE(TABLE.Col1>0,'REPAIRED',NULL)

This follows a PIVOT and a Query2 (Remember to use Where - PIVOT_DATA IS NOT NULL)

Regards,

Suneer.

Former Member
0 Kudos

I am getting two records with 'NEW' (for null values) and 'REPAIRED' for >0. But i am not getting 'NEW' for >0.

Arun

Former Member
0 Kudos

Here is how it works:

As per the logic mentioned above,

If input > 0,

Flag_1 value will be 'NEW' as the mapping logic is:

DECODE(TABLE.Col1 <= 0,NULL,'NEW')

(Maintain the datatype for Flag_1 - varchar (4) atleast)

Flag_2 output will be 'REPAIRED' as the mapping logic is:

DECODE(TABLE.Col1>0,'REPAIRED',NULL)

(Maintain the datatype for Flag_2 - varchar (10) atleast)

If input is <Null>:

Flag_1 output will be 'NEW'

Flag_2 output will be <Null>

(as per the same logic)

ie.

CASE 1 : If the input is like this

Col1

1

<Null>

Query 1 output will be

Flag_1, Flag_2

NEW,REPAIRED (both NEW and REPAIRED for Col1 > 0)

NEW,<Null> (only NEW when Col1 is <Null>)

The PIVOT output will be

PIVOT_DATA

NEW

REPAIRED

NEW

<Null>

The Query 2 output (WHERE PIVOT_DATA IS NOT NULL)

NEW

REPAIRED

NEW

CASE 2 : If the input is as below (reversed)

Col1

<Null>

1

First query output will be

Flag_1, Flag_2

NEW,<Null> - (only NEW when Col1 is <Null>)

NEW,REPAIRED - (both NEW and REPAIRED for Col1 > 0)

The PIVOT output will be

PIVOT_DATA

NEW

<Null>

NEW

REPAIRED

The Query 2 output (WHERE PIVOT_DATA IS NOT NULL)

NEW

NEW

REPAIRED

Suggest you to have a close check on your design. Check the output of each step to be the same as explained and reply where it is missing.

By the way, have you checked "Select Distinct" in your Query2? If so, uncheck it.

Regards,

Suneer

Former Member
0 Kudos

Hey Suneer,

Thanks for the information. I am trying out the same. I could not login to SAP forums for a while for some technical issue.

Will update once i am done.

Arun

Former Member
0 Kudos

i debugged and checked the transform but i am getting only two records one with NEW and other with REPAIRED. I have a feeling that the third record with 'NEW' will be a violation of primary key. Because for one primary key there cannot be a record with two 'NEW' values.

Would that be a reason for not displaying two records with 'NEW'?

Arun

Former Member
0 Kudos

i debugged and checked the transform but i am getting only two records one with NEW and other with REPAIRED

Please debug and see the output of Query1 as explained above. There are two fields as output in Query1. Check the output of each.

Yes. Do not maintain Primary Key for this column in the output table as two 'NEW' records is a primary key violation and it will throw an error if 'Autocorrect load' is not checked.

Regards,

Suneer

Former Member
0 Kudos

Hey Suneer,

My ouput from Query 1 is a single record with 'NEW' in one field and 'REPAIRED' in other. Where as output from query 2 is two record with NEW and REPAIRED in one field.

As per the code you gave, the answer is right. But that is not the output i need.

As per the code, decode (table1.ColA <=0,NULL,'NEW'), which means if it is greater than zero then 'NEW'

second code, decode(table1.ColB>0,'REPAIRED',NULL), which means if it is greater than zero then 'REPAIRED'

so the output is meeting both the conditions like if it is >0 then two records -NEW and REPAIRED. But it is not checking the value, if <=0 then 'NEW' and insert a new record with 'NEW'.

For example i have records with ColA = 0 and >0 in that case i need three record outputs with NEW, NEW and REPAIRED.

Arun

Former Member
0 Kudos

If you look at your earlier post

I have another scenario, where i have to insert three records.

It is like this :

if input col IS NULL then insert record with 'NEW' in target table

if input col >0 then insert two records, one with 'NEW' and other with 'REPAIRED' in the target table

And later, you have changed <Null> to 0 in your requirement.

For example i have records with ColA = 0 and >0 in that case i need three record outputs with NEW, NEW and REPAIRED.

Thats why the confusion as <Null> and 0 are different.

Change the field1 mapping in Query1 to

decode (table1.ColA <0,NULL,'NEW')

Now, this will not give you an output if the ColA < 0. Should this consider negative as well with value 'NEW', let me know.

ie. If ColA = -2, then should the output be 'NEW' ?

Regards,

Suneer

Former Member
0 Kudos

Yes i agree, both NULL and 0 are different. It is my bad, apologize for trouble.

Anyway i think the issue is sorted out now. My customer changed their transformation condition, earlier there was only one primary key, now they changed to three. So i did a transformation and checked it. The output is coming out correctly.

Thanks for your help.

Arun

Former Member
0 Kudos

Can you just try this method:

Query_1:

Define one output column of varchar type and size 15. Name it as "Output"

Column Mapping:

decode(InputColumn is null,'NEW', InputColumn > 0, 'NEW,REPAIRED','NEW')

Place a Row Generation TRanform and set the following properties:

Row number starts at 1

Row count 2

Query_2:

Join Query 1 and Row Generation (No need to mention anything in WHERE clause as we need cartesian product)

Drag the column "Output" from the Query 1 and give mapping as:

word_ext( Query_1."OUTPUT",Row_Generation.DI_ROW_ID,',')

Query_3:

Propogate "output" from Query 2 and in the where Clause give:

Query_2."OUTPUT" is not null

Use Query_3 as input for your target table.

Please let me know if it works for you.

Regards,

Shine

Former Member
0 Kudos

Good one.

A few pointers:

The mapping of Query_1 can be cut short from

decode(InputColumn is null,'NEW', InputColumn > 0, 'NEW,REPAIRED','NEW')

to

decode(InputColumn > 0, 'NEW,REPAIRED','NEW')

as this would suffice. Both should give the same output.

@Arun: Should there be a 'NEW' record for negative numbers as well ?

If so, please change the Field1 mapping to 'NEW' (direct hard code) in the solution using Pivot.

Regards,

Suneer

Former Member
0 Kudos

Shine,

Thank you. It is working well.

Appreciate your help.

Arun

Former Member
0 Kudos

No Suneer. There is no negative values in the input. It is either NULL or NOT NULL (integer values).

Arun

Former Member
0 Kudos

Suneer,

Client added one more condition to the output. I have to insert in a seperate target column for the records which are NEW is BLANK and Repaired is Z

For example if the output record is NEW, NEW, REPAIRED then i need to insert BLANK, BLANK and Z in another column corresponding to it.

Could it be done using the same pivot transform? Any other way i can do this?

Thanks,

Arun

Former Member
0 Kudos

Yes of course.

Add a column - say TARGET_COL_2 with datatype varchar (1) atleast in the last Query transform (Query2)

ie. The Query transform after PIVOT

In the mapping tab, use this:

DECODE(PIVOT_DATA = 'NEW','',PIVOT_DATA = 'REPAIRED','Z',NULL)

This is considering that Pivot transform feeds Query2. Else, PIVOT_DATA will be replaced by the incoming field which has the values NEW and REPAIRED.

Regards,

Suneer

Former Member
0 Kudos

Thanks, it is done.

Very much appreciate your help.

Arun

Answers (0)