IBM Mainframe Forum

Posted: **Fri May 07, 2010 2:57 am**

I have a table with following strucure

Sales_Date , Date
Transaction_id , SMALLINT
Amount , Decimal(11,2)

Every 4 hrs I get the file with following structure .

Sales_Date (10 Positions),
Amount (Decimal 10,2)

Transaction_id is a sequence number .

The job to load the table will run every 6 times in a day.
When the job is run second time and onwards , I have to fetch the maximum Transaction_id from a table , need to add 1 to it and assign it to the first record of the file and then increment it for each record .

File Structure for load is similar to the table structure .

Select all

Sales_Date (1:10)
Transaction_id (11-13)
Amount (14:25)

Can anyone help me with this ?

Additional information ...if required ..please let me know .

Posted: **Fri May 07, 2010 3:21 am**

Please provide sample data records and desired output so that we can better assist you. Thank you.

Posted: **Fri May 07, 2010 3:31 am**

Okay...

1st job loaded following Data into table

Select all

Sales_Date Transaction_id Amount
2010-05-05 1 123456.12
2010-05-05 2 34678.12
2010-05-05 3 823796.12
2010-05-05 4 83456.12

I have a input file with following data

Select all

Sales_Date Amount
2010-05-05 978.18
2010-05-05 1234.21

Need to create the output file which will have sequence number . But that sequence number should be now 5 for first record and 6 for second record as we already have 4 records in the table .

So output file will be

Select all

Sales_Date Transaction_Id Amount
2010-05-05 5 978.18
2010-05-05 6 1234.21

Once this File is loaded into table ..the table should look like

Select all

Sales_Date Transaction_id Amount
2010-05-05 1 123456.12
2010-05-05 2 34678.12
2010-05-05 3 823796.12
2010-05-05 4 83456.12
2010-05-05 5 978.18
2010-05-05 6 1234.21

Posted: **Fri May 07, 2010 9:36 pm**

Your sample records do not match your column descriptions above. Please make sure that the field positions in your sample records are correct, otherwise the solution we provide may not be accurate.

Posted: **Sat May 08, 2010 12:32 am**

Sales_Date 10 Positions
transaction_id smallint ...with 3 positions in a file
Amount Decimal(11,2) , it would take 12 positions in a file

Posted: **Sat May 08, 2010 1:22 am**

Do the sales_date and Amount appear in the same positions in the input file and in the table?

Posted: **Sat May 08, 2010 3:13 am**

Yes...That is correct .

Posted: **Mon May 10, 2010 9:15 pm**

Here is a sample SyncSort for z/OS job you can use:

Select all

//SORT1 EXEC PGM=SORT
//SORTIN DD *
SALES_DATE ID AMOUNT
2010-05-05 1 123456.12
2010-05-05 2 34678.12
2010-05-05 3 823796.12
2010-05-05 4 83456.12
// DD *
SALES_DATE AMOUNT
2010-05-05 978.18
2010-05-05 1234.21
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
OMIT COND=(1,1,CH,EQ,C'S')
SORT FIELDS=COPY
OUTFIL OVERLAY=(11:SEQNUM,3,ZD),
HEADER1=(1:C'SALES_DATE ID AMOUNT')
/*

This is the output produced:

Select all

SALES_DATE ID AMOUNT
2010-05-05001 123456.12
2010-05-05002 34678.12
2010-05-05003 823796.12
2010-05-05004 83456.12
2010-05-05005 978.18
2010-05-05006 1234.21

If your files do not have actual header records, then you can simply remove the OMIT and HEADER statements.

Posted: **Wed May 12, 2010 8:31 pm**

Thanks Alisa , it worked .

IBM Mainframe Forum

Variable Sequence Number

Variable Sequence Number

Re: Variable Sequence Number

Re: Variable Sequence Number

Re: Variable Sequence Number

Re: Variable Sequence Number

Re: Variable Sequence Number

Re: Variable Sequence Number

Re: Variable Sequence Number

Re: Variable Sequence Number