RE: SQL

Sachin,

You cant query an index, just query the file the index is based on, and the
SQL engine will use the index if it thinks it needs to.

cheers
Colin.W

http://as400blog.blogspot.com
 
Extension   5800
Direct dial   0870 429 5800


-----Original Message-----
From: Chaudhary, Sachin (GE Equipment Services, Consultant)
[mailto:sachin.chaudhary@xxxxxx] 
Sent: 06 April 2005 00:04
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL
Importance: High


Hey Rob

I ran a query on an INDEX and it gives an error!
IMPILN00L1 in ILMOLIB not table, view, or physical file.
It took around 1 hour for index to be built and now it is not accepted!!
Don't know why only Physical Files/Logical Files(not useful in SQL as it
starts building its own index at run time) AND Views are accepted by SQL
Optimizer???

Thanks,
Sachin

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On
Behalf Of rob@xxxxxxxxx
Sent: Thursday, March 31, 2005 4:12 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL


Before starting your query, start debugging.  For example: STRDBG
UPDPROD(*YES) STRSQL Then run your query.  When you exit the STRSQL do
DSPJOBLOG You may see messages in there like "Dude, I might run faster if
you create 
an index on file IMPILN00 in library ILMOLIB over the 
ILNSOLDTO field."

So, if you want to speed things up, you do a
CREATE INDEX ILMOLIB/IMPILN00L1
ON ILMOLIB/IMPILN00
(ILNSOLDTO ASC)

Now, there is a difference between Index, View and Logical File. An index
only creates a sort order.  You do not more than one physical 
file.  You do not select which fields to appear.  You do not select which 
records to appear.
A View can be created over one or more files.  You can select which fields 
to appear.  You can select which records to appear.  You CANNOT set a sort 
order.
A logical file can combine both of these functions.  There are some things 
a logical cannot do.  Like BLOB fields, UDF's and a host of other things. I
forget which, but only one of the index or view family can be used on a 
F spec in RPG, if you want to use traditional I/O versus imbedded SQL on 
occasion.  Basically that is the ONLY advantage of a logical file.  Well, 
I suppose if you want to get into the debate on whether it's better to 
store your source in QDDSSRC or QSQLSESS you can go there, but that's more 
a matter of style.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Douglas W. Palme" <dns@xxxxxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/31/2005 04:49 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> cc

Subject
Re: SQL






That helped, thanks, another question kinda related, I noticed that when 
the 
query starts it displays the following: "Query Running.  Building Access 
Path for file IMPILN00 in ILMOLIB"

Can someone explain what that means building access path?


On Thu, 31 Mar 2005 13:43:20 -0800, Tony Carolla wrote
> Try this:
> 
> select ilnsoldto, sum(ilneprice) from impiln00 group By ilnsoldto
> 
> On Thu, 31 Mar 2005 13:40:20 -0800, Douglas W. Palme 
> <dpalme@xxxxxxxxxxxxxxxxxxxxx> wrote:
> > As a follow up to the questions I had the other day, I've ordered 
> > the appropriate license key for the SQL and have started working on
learning
> > embedded sql to use with my rpg.
> > 
> > Needless to say, it'll delay some reports a bit but I think in the
long 
run
> > it'll help.
> > 
> > One question I do have, I am running the sql interactive query and I
am 
just
> > playing with it a bit, but have run into a problem that maybe one of
you 
can
> > help me out with; considering the slightly different syntax between
sql 
for
> > the iSeries and what I am used to.
> > 
> > I have a PF that cotnains among other fields, ilneprice (which
contains
> > extended price amount), and a ilnsoldto field which is a reference
field 
for
> > the customer number.
> > 
> > When I run 'select ilnsoldto' by itself it runs fine, when I run sum
> > (ilneprice) by itself its fine but when I run the following command 
> > it

tells
> > me the ilnsoldto field is incorrect:
> > 
> > 
> > 
> > Suggestions? Comments?
> > 
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> > mailing
list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives at 
> > http://archive.midrange.com/rpg400-l.
> > 
> >
> 
> --
> "Enter any 11-digit prime number to continue..."
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L)
>  mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-
> L-request@xxxxxxxxxxxx Before posting, please take a moment to 
> review the archives at http://archive.midrange.com/rpg400-l.


--
Open WebMail Project (http://openwebmail.org)

-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

This e-mail has been sent by a company of Bertram Group Ltd, whose registered 
office is 1 Broadland Business Park, Norwich, NR7 0WF. 
This message, and any attachments, are intended solely for the addressee and 
may contain privileged or confidential information.  If you are not the 
intended recipient, any disclosure, copying, distribution or any action taken 
or omitted to be taken in reliance on it, is prohibited and may be unlawful.  
If you believe that you have received this email in error, please contact the 
sender immediately. Opinions, conclusions and statements of intent in this 
e-mail are those of the sender and will not bind a Bertram Group Ltd company 
unless confirmed in writing by a director independently of this message. 
Although we have taken steps to ensure that this email and any attachments are 
free from any virus, we advise that in keeping with good computing practice the 
recipient should ensure they are actually virus free.