1.  Home
  2. MIDRANGE-L
  3. April 2006

RE: Another SQL question

John, for reference material go to www.iseries.ibm.com/db2 then click on the tab that says Support, and then you will have a link to the manuals 

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzahf/rzahfms1.htm
It's for V5R2 but that hardly matters when starting out. It has lists of tasks and examples that really explain well what to do. Great site! You can download the manuals from there, too. Or go to infocenter to get the latest and greatest.
I cut my SQL teeth starting about 13 years ago on the interactive SQL of the 400 - STRSQL. F4 is your friend and will guide you very nicely. It does not handle some of the new stuff directly - it lets you use it but does not prompt it. Still, for basic stuff, it's fine.
The above-named DB2 site also has white papers and code samples and tech tips - great site for all this stuff.
Then there are lots of tutorials on the net - simple SQL is almost the same everywhere - do a google on "SQL tutorial" and explore. I also recommend http://www.w3schools.com which has lots of tutorials, including one on SQL. This site is one I use for things like JavaScript, XML, all kinds of webbish things, and find it very helpful. It gives you info in small bits - again, a good starter. 

HTH
Vern

At 11:38 AM 4/4/2006, you wrote:


I certainly didn't take it that way.I was playing Mike. I understand it's
just that I don't know much about SQL and I really don't have any reference
material

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Michael_Schutte@xxxxxxxxxxxx
Sent: Tuesday, April 04, 2006 11:20 AM
To: Midrange Systems Technical Discussion
Subject: RE: Another SQL question

I'm sorry that sounded rude, but I didn't mean it that way.  Learn by
doing!

Anyway, look in your WHERE EXISTS statement...  maybe you just copied it
that way, but the there needs to be a space before the   FROM.

Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx



             Michael_Schutte@b
             obevans.com
             Sent by:                                                   To
             midrange-l-bounce         Midrange Systems Technical
             s@xxxxxxxxxxxx            Discussion
                                       <midrange-l@xxxxxxxxxxxx>
                                                                        cc
             04/04/2006 12:11
             PM                                                    Subject
                                       RE: Another SQL question

             Please respond to
             Midrange Systems
                 Technical
                Discussion
             <midrange-l@midra
                 nge.com>






Sounds like you need to put your thinking cap on and try to determine why
it's not working.

Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx



             "John Candidi"
             <jacandidi@rutger
             sinsurance.com>                                            To
             Sent by:                  "'Midrange Systems Technical
             midrange-l-bounce         Discussion'"
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>
                                                                        cc

             04/04/2006 12:07                                      Subject
             PM                        RE: Another SQL question


             Please respond to
             Midrange Systems
                 Technical
                Discussion
             <midrange-l@midra
                 nge.com>






Elvis, this produces the following error.

UPDATE jacfiles.agcy_ext r1 SET r1.bus_name = (SELECT r2.acnm_name1 FROM
rugr80dat.pmspag00 r2 WHERE r1.mco = r2.agnm_mco and r1.agcy =
r2.agnm_agcy)
WHERE EXISTS (SELECT r2.acnm_name1FROM rugr80dat.pmspag00 r2 WHERE r1.mco =
r2.agnm_mco and r1.agcy = r2.agnm_agcy)

[SQL0104] Token . was not valid. Valid tokens: , FROM INTO. Cause . . . . .
:   A syntax error was detected at token ..  Token . is not a valid token.
A partial list of valid tokens is , FROM INTO.  This list assumes that the
statement is correct up to the token.  The error may be earlier in the
statement, but the syntax of the statement appears to be valid up to this
point. Recovery  . . . :   Do one or more of the following and try the
request again: -- Verify the SQL statement in the area of the token ..
Correct the statement.  The error could be a missing comma or quotation
mark, it could be a misspelled word, or it could be related to the order of
clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL
statement because it does not end with a valid clause.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Tuesday, April 04, 2006 10:38 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Another SQL question

UPDATE jacfiles.agcy_ext r1
SET r1.bus_name = (SELECT r2.acnm_name1
                   FROM rugr80dat.pmspag00 r2
                   WHERE r1.mco = r2.agnm_mco and r1.agcy = r2.agnm_agcy)
WHERE EXISTS (SELECT r2.acnm_name1
              FROM rugr80dat.pmspag00 r2
              WHERE r1.mco = r2.agnm_mco and r1.agcy = r2.agnm_agcy)

First subselect sets the value and second ensures only matching rows are
updated.

Elvis

-----Original Message-----
Subject: FW: Another SQL question

I know the following sql syntax is not correct (I'm new at attempting SQL)
but this is what I want to do. I am attempting to pull the field acnm_name1
from rugr80dat/pmspag00 table and put it into the bus_name field in the
jacfiles.agcy_ext table. These 2 files are tied by agcy_mco=mco and
agnm_agcy = agcy. Any help (without laughing) would be appreciated.

UPDATE jacfiles.agcy_ext r1

SET bus_name = acnm_name1

WHERE EXISTS (SELECT 1 FROM rugr80dat.pmspag00 r2)

WHERE r1.mco = r2.agnm_mco and r1.agcy = r2.agnm_agcy



John A Candidi

Rutgers Insurance Companies

IT Director - AS/400 Manager

856-779-2274


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.