More About SQL Correlation Names

Published: March 12, 2008

Hey, Ted:

I hope my question is an easy one to answer. I have a file that stores the location of inventory in a warehouse. Location consists of a row (an aisle), column, and level. Why will an SQL UPDATE let me change the column and level, but not the row?

--Scott

You can update the column like this:

update qtemp/inventory 
   set column=4        
 where item = 'SL-701'

But updating the row gives you error SQL0104. (Token 4 was not valid. Valid tokens: ( : DAY CAST CHAR DATE DAYS HOUOUR LEFT TIME TRIM YEAR COUNT MONTH.)

update qtemp/inventory 
   set row=4           
 where item = 'SL-701'

SQL doesn't let you change the row because the field is named ROW, which has a reserved meaning in the SET clause of the UPDATE command. You'll need to use a correlation name so SQL will know that you mean the ROW column (field) in the inventory file.

update qtemp/inventory as inv 
   set inv.row=4              
 where item = 'SL-701'        

Another predefined value that has bitten me on more than one occasion is USER, which refers to the user profile under which the query is running. Let's assume that MYFILE has a field called USER, which stores the name of the user who last wrote or updated a record. What does the following query do?

select * from myfile 
 where user = 'DAN'  

If the user running the query is DAN, the SELECT returns all the rows (records) in MYFILE. Otherwise, the query returns an empty set.

To select the records that were last modified by DAN, do this instead.

select * from myfile as x 
 where x.user = 'DAN'     

--Ted

RELATED STORY

Good Reasons to Use Unrequired Correlation Names