Categories
Code Talking

AS400 SQL

I had to use more SQL than usual the other day. It had been a while since I had much call to use SQL in my job for something more than a simple query, however. I needed to use it to update some records in a file as…

I had to use more SQL than usual the other day. It had been a while since I had much call to use SQL in my job for something more than a simple query, however. I needed to use it to update some records in a file as I really just didn’t want to write a program to do the work. SQL should have been much faster, had I a clue what I needed to do. Eventually, I struggled my way through it and it was much faster – assuming you didn’t count the time spent on the learning curve to get to where I could actually use it!

The difficulty really came from the fact that I needed to access some files that had unusual names – specifically, there are dots in the names. AS/400 SQL doesn’t seem to handle these as it does typical file names. The first thing I learned is that you need to put the filename, in all capital letters, inside a pair of double quotes. For instance: select * from “FILE.NAME”.

While I did have the library containing this file in my library list, I ended up at one point needing to reference something in another library that was not in my library list. My natural instinct would be to put that library inside the quotes. Not a good choice. The library in this case needs to go outside the quotes: select * from library/“FILE.NAME”. Presumably if the library had dots in it, I’d need to put it in capital letters inside another set of quotes, keeping the slash outside of both sets. But I didn’t, so I didn’t test that.

Finally, I needed to delete some records based on the contents of another file. This is because the field I needed wasn’t in the file I was trying to use with the delete operation. I toyed with some joins, but couldn’t easily get the results I wanted. In the end, I used a subselect (not sure if that’s the right term) instead: delete from “FILE.NAME” where fieldname in (select fieldname from “OTHER.FILE” where otherfield = some condition).

What do you know? It worked!