Hi all,
many thanks for your help, I try this solutions:
Select Progressivo
From Final Table (
Insert Into Gestsimdat/DirEse0f
Select current timestamp, 'E', 'M', 'I6910014202382', Coalesce(Max(Progressivo) +1, 1),
'execiddiprova', nullif('', ''), Date(nullif(0, 0)), nullif('', ''), nullif('', ''),
nullif('', ''), nullif('', 'tvtic'), nullif('', '')
From Gestsimdat/Direse0f
Where Riferimento = 'I6910014202382');
Many thanks to all
------------------------------
Paolo Salvatore
------------------------------
Original Message:
Sent: Fri April 08, 2022 02:02 AM
From: Birgitta Hauser
Subject: Replace RPG code with sql
IMHO the easiest way is this statement:
Select Coalesce(Max(MlePrg) + 1, 1) into :YourHostVar
From YourTable
Where KnRif = :Mlenrif;
It will return the next MlePrg, if a row for knRif is found. If there is no row for KnRif it will return 1.
You can embedd it in an RPG function or you may just write an SQL function which includes only this statement:
Create Or Replace Function YourSchema/YourFunction (
ParKnRif Varchar(15))
Returns Integer
Language Sql
Return (Select Coalesce(Max(MlePrg) + 1, 1)
From YourTable
Where KnRif = ParKnRif);
------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
Original Message:
Sent: Thu April 07, 2022 09:07 PM
From: Satid Singkorapoom
Subject: Replace RPG code with sql
Mr. Paolo
So, you want running sequence numbers for each unique order number. Then I think one way you can do in SQL is to use a IF EXISTS statement to check if the order number already exist or not. If not, then you assign sequence number as 1. If it already exists, you increase its max value by 1. The statement looks something like this :
IF EXISTS (SELECT MAX(<progressive column>) INTO <SQL variable> FROM lib.table WHERE <order number column> = <another SQL variable> )
THEN SET <SQL variable> = <SQL variable> + 1
ELSE SET <SQL variable> = 1
END IF ;
INSERT ......
------------------------------
Satid Singkorapoom
Original Message:
Sent: Thu April 07, 2022 07:27 AM
From: Paolo Salvatore
Subject: Replace RPG code with sql
Hi Satiq,
many thanks for your answer,
there's only a bit difference, because I want to reset this progressive number every time then change the Knrif value, something like:
ORDERKEY | Progressive |
---|
order1 | 1 |
order1 | 2 |
order2 | 1 |
order3 | 1 |
order2 | 2 |
order3 | 2 |
order1 | 3 |
order4 | 1 |
------------------------------
Paolo Salvatore
Original Message:
Sent: Thu April 07, 2022 04:20 AM
From: Satid Singkorapoom
Subject: Replace RPG code with sql
Mr. Paolo
By your "progressive number", I understand it is an integer value that increases by 1 for a new row added to the file. If this is the case, you can simplify this part of SQL coding by letting Db2 insert this column value for you automatically by declaring this column as what is called an IDENTITY COLUMN. This article can explains what this is : https://www.as400andsqltricks.com/2021/12/identity-column-in-ddl-table.html. I also attach another article for your to read.
If you use identity column, you just use a straightforward SQL statement INSERT to add the new order entry to the file right away while omitting the identity column value with a NULL. Otherwise, you need to retrieve the current latest value of the progressive number first into an SQL variable and increase it before using it in the INSERT statement.
SELECT MAX(<progressive column>) INTO <SQL variable> FROM lib.table;
<increase the column value by 1>
INSERT INTO ......
------------------------------
Satid Singkorapoom
Original Message:
Sent: Thu April 07, 2022 03:16 AM
From: Paolo Salvatore
Subject: Replace RPG code with sql
Hi I've a procedure inside an rpg program like this:
Dcl-Pi SetPrg Zoned(8); KNRif Like(mlenrif); End-Pi SetPrg; ______________________________________ MlePrg = *HiVal; SetGt (KnRif :MlePrg) MleEse0f; ReadPE KnRif RMleEse; If %Eof(MleEse0f); MlePrg = 1; Else; MLePrg += 1; EndIf; MleNRif = KNrif; // valorize some other fields Write RMLeEse; Return MlePrg;
The file MleEse0f have two keys, the firstone is my order number, and the second a simple progressive.
This procedure receive in input the order number, make a setgt with the order number and then a readEP, So obtain the last progressive number add 1 and write the record.
How can I remove this logic with a sql?
Many thanks.
------------------------------
Paolo Salvatore
------------------------------