RE: SQL Question

["kempenich" <kempej01@xxxxxxxxx>]

The i5 supports SQL very well.  The biggest issue that non-i5 developers run 
into is understanding how tables/views are resolved by the system.  The i5 
supports 2 ways of resolving table/views: *SYS naming or *SQL naming.  

*SYS naming uses the "/" as a seperator between the library and the table/view 
and uses the library list of the job to find any tables/views (this is what we 
are use to)

*SQL naming used the "." as a separator between the library and the table/view 
and looks for the table/view in a library with the same name as the user that 
is currently signed on.

Looking at his SQL he does not have the table/view qualified.  

If he is currently connected to the i5 using *SYS naming, the QZDAINIT jobs 
need to have the library added to the library list or the table/view qualified 
with the correct library.

If he is currently connected to the i5 using *SQL naming, he needs to be signed 
into the i5 with a user profile with the same name as the library that contains 
the table/view (I know that this might sound strange, but this is really how it 
works).

If this does not take care of the problem, please repost with additional detail 
on the error(s) he is receiving.

Note: the job logs for the QZDAINIT jobs contain a wealth of information that 
could give additional insight on what is wrong.  Typically I have found that 
the SQL developers don't have enough experience with the i5 and that they need 
guidance on how to work with it (its not like SQL Server, Oracle, DB2).  Once 
they understand the benefits of the system and its integrated database, they 
usually become converts.

Joe Kempenich


--- On Thu 03/16, DebbieKelemen < DebbieKelemen@xxxxxxxxxxx > wrote:

From: DebbieKelemen [mailto: DebbieKelemen@xxxxxxxxxxx]
To: midrange-l@xxxxxxxxxxxx
Date: Thu, 16 Mar 2006 15:46:07 -0700
Subject: SQL Question

I am asking this question on behalf of my web developer, who does not belong
to any list related to the iSeries (even though he should in my mind).
Anyway, here goes nothing. He would like to be able to something similar to
the following:
____________________________________________________
CREATE PROCEDURE CWMPINH.GETACTIVEITEMSKUS
(IN IN_COMPANY CHARACTER(3),
IN IN_OFFER CHARACTER(3)) 
RESULT SETS 1
LANGUAGE SQL
BEGIN

declare selectfromtable CHARACTER(30);
set selectfromtable = 'realtablename' + IN_COMPANY;

DECLARE c1 CURSOR FOR 

select IN_COMPANY as company
from selectfromtable <-- this guy needs to be evaluated

OPEN c1;
SET RESULT SETS CURSOR c1;
END;
_______________________________________________________________

In the above he is attempting make the table in his procedure something he
declares & selects from on the fly because it will vary based on whether he
is going over our live or test data. But the iSeries is fighting him over
this.

Can anyone help?

Debbie