Using SQL in the Real World

[jsteil]

** This thread discusses the article: Using SQL in the Real World **
** This thread discusses the Content article: Using SQL in the Real World0

[jsteil]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Ooops, forgot to mention that I think this requires client access on the PC as well. Probably not a big deal for most, but just something to be aware of.

[eagarcia@earthlink.net]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Good article, but I think your example of aggregating data should be: SELECT ODITEM, DECIMAL(COUNT(*),5,0) AS LINES, DECIMAL(SUM(ODQORD),12,3) AS TOTAL_QTY FROM ORDDTL GROUP BY ODITEM ORDER BY ODITEM Not: SELECT ODITEM, DECIMAL(COUNT(*),5,0) AS LINES, SUM(ODQORD) FROM ORDDTL DECIMAL(SUM(ODQORD),12,3) AS TOTAL_QTY FROM ORDDTL GROUP BY ODITEM ORDER BY ODITEM

[efnkay]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Even if you don't have the "Data Perspective" tools you can generate DDL SQL statements from any database file or logical file whether or not it was created by compiling DDS specs or by running a DDL SQL statement. A great way to learn DDL statements I might add. Yes Joe, I say give them SQL like taking a sip from a fire-hydrant. It's been mentioned here in other places and I had written a command interface to the QSQGNDDL API some time ago...But this API (And you already have it.) will create a nice DROP table, and then CREATE TABLE, INDEX, VIEW, etc, source member DDL SQL script, that you can run via RUNSQLSTM if you happen to be so inclined. If you haven't already...Learn about this API and others you didn't know you had. It can't be avoided any longer...You need to know SQL. Start using it. Use it often. Use it (and many variations and extensions thereof)on any relational database out there. Guess what...bare naked ANSI SQL-89, SQL-92, SQL-99 standards compatibility generally, almost without exception, works on every dang one of them...DB2 UDB, Oracle, Informix, Sybase, Ingres, databases they haven't even created yet and oh-oh so many, many more!!!

[Pauly D]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
You don't have to get an after market SQL editor. There is one built into Ops nav.Since I started using the SQL editor in Ops nav , I hardly ever use STRSQL Green screen anymore. Also you can save your SQL statements in different text files with very descriptive names e.g "Update open Order amount.sql". Since you are in a client text editor you can easily cut and paste your code elsewhere(excel,etc) If I am doing a complex SQL statement in ILERPG I do it first in the editor to prove it works then cut & paste into an exec sql statement in the Rational source editor. To use : OPS Nav --> Databases -->Right click on selected database--> Choose "RunSQL Scripts". As with all client editors there is way more functionality : change fonts, color, test size, different run options,etc. There is even a Visual version of the query optimiser. Beats green screen anyday . . .

[jsteil]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
I've used that but prefer FROG because of the painful slowness of OpsNav (on my machines). OpsNav may be more functional, but FROG is a great tool and it is quick.

[Pauly D]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Interesting . . I am running ops nav from my IBM laptop with 4gb memory and it is lickety split. Still, I will check frog out . A man can never have enough tools lying around . . .

[jsteil]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
I wish I had an extra 2gig of memory laying around so I could get Ops Nav running that well...

[J.Pluta]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
You're right. The part of the second line after the "AS LINES," shouldn't be there. I'm not sure how I managed to cut and paste that. Thanks for catching it!

[J.Pluta]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
It's been a while, but I remember reading a couple of articles over the years about using the QSQGNDDL API. I'm sure a quick Google will turn up some examples of using the API. Thanks for the reminder!

[J.Pluta]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
I need to review the Ops Nav functionality. Or isn't it something like iSeries Navigator now? Anyway, there is a lot of functionality packed into that software. Unfortunately, it seems that lots of shops don't install it, or only install it with the basic features, so many of the things (such as the SQL capabilities) aren't available.

[J.Pluta]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Thanks for the heads-up on FROG. I actually looked at it a while back, but I never got into it, probably because I had so many other options and I try to stay within the realm of either 5250 or the Rational (WDSC) products. But having another free tool in the toolbelt is always a good thing!

[nrn0622]

Embedd SQLRPGLE

** This thread discusses the article: Using SQL in the Real World **
I'm working a project that need to use embedded SQLRPGLE(Dynamic SQL). I was once able to dipslay the data that had been read. I could not display data that read after I modified to have external describe file in the program. I removed the added code but I still can not disply the data I read. Here is the program that does not have external describe file. Please help!! Thanks!! * D data DS d vend 5a d fac# 5a d lname 20a d sqlstring s 32000a varying d inz('Select + d cvSVNCD, CVSFCCD, CVLNAME,+ d from filecco/cvdemmpi') * C/EXEC SQL c+ declare mainCursor Cursor c+ for mainstatement C/END-EXEC * C/EXEC SQL c+ prepare mainstatement c+ from :sqlstring * C/EXEC SQL c+ open mainCursor C/END-EXEC * * initial read * C/EXEC SQL c+ fetch next c+ from mainCursor c+ into ATA C/END-EXEC * c dow sqlstt <> '02000' C vend DSPLY C data DSPLY C/EXEC SQL c+ fetch next c+ from mainCursor c+ into ATA C/END-EXEC * C enddo C/EXEC SQL c+ close maincursor C/END-EXEC C eval *inlr = *on

[nrn0622]

I need help with embedded SQLRPGLE

** This thread discusses the article: Using SQL in the Real World **
I believe I made a mistake in define DS. anyway my program is still not work with dynamic SQL. Here is the more correct text in the program D data DS d cvsvncd 5a d cvsfccd 5a d cvplname 20a d sqlstring s 32000a varying d inz('Select + d cvSVNCD, CVSFCCD, CVPLNAME,+ d from filecco/cvdemmpi') * C/EXEC SQL c+ declare mainCursor Cursor c+ for mainstatement C/END-EXEC * C/EXEC SQL c+ prepare mainstatement c+ from :sqlstring C/END-EXEC * C/EXEC SQL c+ open mainCursor C/END-EXEC * * initial read * C/EXEC SQL c+ fetch next c+ from mainCursor c+ into ATA C/END-EXEC * c dow sqlstt <> '02000' C cvsvncd DSPLY C data DSPLY C/EXEC SQL c+ fetch next c+ from mainCursor c+ into ATA C/END-EXEC * C enddo C/EXEC SQL c+ close maincursor C/END-EXEC C eval *inlr = *on

[nrn0622]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Something very strange is that SQLSTT = '24501'. I believe a successful read the SQLSTT should = 0. Please help!! thanks!!