Help with a SQL problem.

[corvair61]

Good Morning. I could use some help with writing a SQL statement.


I have a process I am finishing to deal with Disabled/Deleted profiles. As a person leaves our organization the profile is added to a daily table of profiles to disable, and in a certain period after, delete.

There is a table of profiles that must be protected from deletion. Below are several protected profiles and some masked profiles. The masked profiles protect numerous profiles starting with the letters preceding the asterisk.

(iProfile)
ALL*
ARG0021105
ARV*
ARV0015501
BACKUP
BCAS*
BCASPGMR

Here is the sql statement that I need help with:

Select count(*) into :cnt from filename
where iprofile like trim(:zprofile) concat '%'
and locate('*', iprofile) <> 0;

Examples:

Profile = ‘ALLACC’ – Slq statement comes back with a count of 1, as it should.
Profile – ‘ARVBORG’ – Same as above. Works fine telling me I got a hit and should not disable the profile.
Profile – ‘BCA’ – My statement returns a count of 1, here is my issue. I want to stop at the ‘S’ in ‘BCAS’.

Is there a way for me to tell it to do the LIKE for the number of positions up to the asterisk so I do not pick up profiles that I do not want?

‘BCASAAA’ – Protected and I expect a count of 1
‘BCASB’ – Same as above’
‘BC’ – Returns me a count of 1, but I need it to stop at the ‘S’ , only profiles that contain all the letters up to the asterisk.

I hope I am clear. I could use some help.

[corvair61]

for count = %len(%trim(cprofile)) by 1 downto 1 ;
zProfile = %replace(' ':cProfile:count + 1 :%len(cProfile)
- count + 1);

exec sql
Select count(*) into :cnt
from uprp001c
where substring(trim(iprofile),1,locate('*',iprofile)-1)
= :zprofile
and locate('*', iprofile) <> 0;

if cnt <> 0;
return '1';
endif;

endfor;

[Vectorspace]

That will work, but there is a much, much simpler way.

You were originally trying to do iProfile like zProfile, which failed because it matched too much. Just do zProfile like iProfile instead

In addition, you do not need to count the number of matches,m you only need to know if there is at least one match. So you can make the statement more efficient by limiting it to the first match

dcl-s found ind;

found = *off;

exec sql
  select '1' into :found
   from filename
  where trim(:zProfile) like replace(trim(iProfile),'*','%');
  limit 1;

return found;

Transform the table column iProfile into a like type match, by replacing * with %,

• BCAS* -> BCAS%
• BCA like BCAS% = false
• BCASB like BCAS% = true

You can do a like comparison without a %:

• 'BACKUP' like 'BACKUP' = true

Then acts just like an Equals comparison.


This may not be very efficient as it will need to check every record in the exceptions table, but I think that's true of any method.


You say that "As a person leaves our organization the profile is added to a daily table of profiles to disable"
From that I assume that your program is looping through that daily list to disable and/or delete?
In which case, you may be able to make it more efficient if you construct an SQL statement that joins the daily table with the protected profiles table, and loop through that. As a general rule of thumb, it is more efficient to do any matching entirely within SQL, rather than within RPGLE