Difference between revisions of "SQL"
From MidrangeWiki
MrDolomite (talk | contribs) m (→Tips: add link to www.mcpressonline.com article with code for RUNSQL custom command) |
Starbuck5250 (talk | contribs) (+ select *) |
||
| Line 8: | Line 8: | ||
:-- Code 3 -- {{code|&FILE}} is not journaled, or you do not have authority to the journal. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal. | :-- Code 3 -- {{code|&FILE}} is not journaled, or you do not have authority to the journal. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal. | ||
*Resolution: Use {{code|[[RUNSQLSTM]]}} with parameter {{code|COMMIT(*NONE)}} | *Resolution: Use {{code|[[RUNSQLSTM]]}} with parameter {{code|COMMIT(*NONE)}} | ||
| + | |||
| + | ;SELECT * | ||
| + | :For the sake of example, use two files, {{code|master}} and {{code|trans}}. We want to select all the columns in {{code|trans}} but only the name column from table {{code|master}}. | ||
| + | :If using *SYS naming you can execute {{code|SELECT trans.*, name FROM trans JOIN master ON...}} | ||
| + | :If using *SQL naming, we need to use a correlation name: {{code|SELECT t.*, name FROM trans t JOIN master ON...}} | ||
| + | |||
;RUNSQL command for ad-hoc SQL statements | ;RUNSQL command for ad-hoc SQL statements | ||
:[http://www.mcpressonline.com/mc/.6b4f1e2e "Partner TechTip: Blend SQL and RPGLE to Make Better Tools"] by [http://www.mcpressonline.com/mc/224@@5c0085ad@1@@.6b4f1e2e Kevin Forsythe] | :[http://www.mcpressonline.com/mc/.6b4f1e2e "Partner TechTip: Blend SQL and RPGLE to Make Better Tools"] by [http://www.mcpressonline.com/mc/224@@5c0085ad@1@@.6b4f1e2e Kevin Forsythe] | ||
Revision as of 19:36, 16 November 2007
SQL, or Structured Query Language, is a platform independent way of accessing databases. However, the version of SQL which runs on the AS/400, known as SQL400 or SQLDB2, does have syntax differences from standard wikipedia:SQL.
- See also: SQL Primer and SOUNDEX
Tips
- SQL7008 error
&FILEin&LIBRARYnot valid for operation.- -- Code 3 --
&FILEis not journaled, or you do not have authority to the journal. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.
- Resolution: Use
RUNSQLSTMwith parameterCOMMIT(*NONE)
- SELECT *
- For the sake of example, use two files,
masterandtrans. We want to select all the columns intransbut only the name column from tablemaster. - If using *SYS naming you can execute
SELECT trans.*, name FROM trans JOIN master ON... - If using *SQL naming, we need to use a correlation name:
SELECT t.*, name FROM trans t JOIN master ON...
- RUNSQL command for ad-hoc SQL statements
- "Partner TechTip: Blend SQL and RPGLE to Make Better Tools" by Kevin Forsythe
