Coding in RPG (IBM i/AS400). Checking if a record exists in embedded SQL.

In this video, I explore two different ways to check if a record exists in SQL when working with embedded SQL in RPG on IBMi! 🛠️

If you need to verify whether a record is present before updating or inserting data, this video is for you. I’ll show you how to do it using WHERE EXISTS (with two different ways) and COUNT, explaining the pros and cons of each method.

No matter your experience level with SQL on IBMi, this will help you write cleaner and more efficient queries!

Do you have any other better method ?? please share!

Enjoy!

#IBMi #IBM #RPGLE #RPG #SQLRPGLE #RPGProgramming #IBMiDevelopment #AS400 #Codefori #VSCode #COMMON #ibmchampion

0:00 Introduction
0:31 Let's see the content of the table that we are going to work with, using DB2 for i
1:07 Creating a new SQLRPGLE source for the example using Code for i
2:01 First method: using WHERE EXISTS.
5:00 Checking how it works
6:04 First method: using WHERE EXISTS (version two).
6:46 Checking how it works
7:25 Second method: using COUNT.
8:40 Checking how it works
9:15 Final thoughts and wrap-up

[johnhester]

Interesting. I really like the second version of exists. I normally use the count(*) method or if efficiency is an issue I use something like this:-
dcl-s dummy char(10);
exec sql select description into :dummy
from clv1.colours
where description = 'Purple' limit 1;

if sqlCode = 0;
snd-msg 'Purple exists';
else;
snd-msg 'Purple doesn''t exist';
endif;

But I think I'll switch to
exec sql select '1' into :exists
from clv1.colours
where description = 'Purple';
if exists;
snd-msg 'Purple exists';
else;
snd-msg 'Purple doesn''t exist';
endif;

It looks better.

[amansingh-cvdf]

The fastest way to know if the record exists or not -

Select ‘1’ into :exists
From table
Where description = Purple
Limit 1

In other 2 ways, should there be multiple records the sql has to read and list out all of them to find the result. But the above sql gets the processing completed as soon as it gets the first record.

This is helpful if there are huge number of records in a table

[Jimi-bxxf]

How to get json from ifs in sqlrpgle ?