SQL400 Tips and Tricks - PowerPoint PPT Presentation

1 / 56

About This Presentation

Title:

SQL400 Tips and Tricks

Description:

Books A Million. Why? Industry standard / Cross platform. Learn four statements / Big payback ... condition is not used for a cross join. Percent of Total ... – PowerPoint PPT presentation

Number of Views:620

Avg rating:3.0/5.0

Slides: 57

Provided by: gob3

Category:

more less

Transcript and Presenter's Notes

Title: SQL400 Tips and Tricks

1
SQL/400Tips and Tricks

Bill Fortenberry
Books A Million

2
Why?

Industry standard / Cross platform
Learn four statements / Big payback
Interactive SQL
Embedded SQL / Dynamic SQL

CUSTOMER LISTING
STATE AL
BY DESC YTD ORDERS
CUST NAME ORDERS SALES REP
------------ ------ --------------
ABC Corp. 75 Freddy Smith
Smith Smith 63 Joe Jackson
DEF Company 55
EF Inc. 40 Freddy Smith

Select cusmst.cuname
cusmst.cuytdc
Ifnull(repmst.rename,
)
From cusmst left outer join repmst
on cusmst.curep repmst.rerep
Where custat StateID
Order by 2 desc, 1

Select cusmst.cuname
cusmst.cuytdd
Ifnull(repmst.rename
)
From cusmst left outer join repmst
on cusmst.curep repmst.rerep
Where custat StateID
Order by 2 desc, 1

Select cusmst.cuname
cusmst.cuytdd /
cusmst.cuytdd
Ifnull(repmst.rename,
)
From cusmst left outer join repmst
on cusmst.curep repmst.rerep
Where custat StateID
Order by 2 desc, 1

Select cusmst.cuname
cusmst.cuytdd /
cusmst.cuytdd
Ifnull(repmst.rename,
)
From cusmst left outer join repmst
on cusmst.curep repmst.rerep
Where custat StateID
Order by 3, 1

Select from invmst
Select inumbr, idept, idesc
from invmst
Select inumbr, idept, idesc
from invmst
where istat A

Select inumbr, idept, idesc
from invmst
where istat A
order by inumbr
Select inumbr, idept, upper(idesc),
qtyohd avgcst as invval
from invmst
where istat A and ibuyer 173
order by idept desc, inumbr

Delete from invmst
Delete from invmst
where idept 12

Insert into cumast
(cunbr, cuname, cucity)
Values
(678987, Atlas Inc., Birmingham)

Insert into cumast
(cunbr, cuname, cucity)
select cwidno, cwcnam, cutown
from newcus
where cutown Atlanta

Update cumast
set cuname Acme Builders,
cudisc cudisc .01
where custid 123456
Update customer
set row (Atlas, 13246,
Meridian)
where custid 2354

14
Interactive SQL

Allows prompting for statement syntax.
Good learning tool.
Reduces the need for fixit programs.
Programming tool for Embedded SQL.
Allows you to quickly destroy data.

15
Convert Select to Delete

1. Press F9 to retrieve the select command.
Select from ordhdr where ohornm 123456
2. Type DELETE over SELECT .
Delete from ordhdr where ohornm 123456
3. Press enter.

16
Convert Select to Insert

1. Press F9 to retrieve the select command.
Select from orddtl where odornm 123456
2. Press F15 to push the select statement down a
line, then type the insert command.
insert into mylib/orddtl
Select from orddtl where odornm 123456
3. Press enter.

17
Convert Select to Update

1. Press F9 to retrieve the select command.
Select from orddtl where odornm 123456
2. Type UPDATE over SELECT and use the delete key
to remove FROM.
update orddtl where odornm 123456
3. Place the cursor on the W in where, and press
F15 twice. This will split the statement at the
where clause and push it down two lines.
update orddtl
where odornm 123456
4. Use the blank line for your set clause.
update orddtl
set odstat P
where odornm 123456

18
Static SQL

C/Exec SQL
C Update cumast
C set cuname Acme Builders
C where cunmbr 12345
C/End-Exec
C/Exec SQL
C Update cumast
C set cuname NewName
C where cunmbr Customer
C/End-Exec

19
RPG code to delete records

C OrderNum chain rordhdr
C if found
C delete rordhdr
C endif
C
C OrderNum setll rorddtl
C OrderNum reade rorddtl
C dow not eof
C delete rorddtl
C OrderNum reade rorddtl
C enddo

20
SQL code to delete records

C/Exec SQL
C Delete from ordhdr
C where ohornm OrderNum
C/End-Exec
C/Exec SQL
C Delete from orddtl
C where odornm OrderNum
C/End-Exec

21
Selecting one row from a table

C/Exec SQL
C Select idept, idescr
C Into DeptNum, SkuDescr
C From invmst
C Where inumbr SkuNumber
C/End-Exec

22
Selecting multiple rows

Declare the cursor
C/Exec SQL
C Declare c1 Cursor for
C Select inumbr, idept, idescr
C From invmst
C Where idept DeptNumbr
C/End-Exec
Open the cursor
C/Exec SQL
C Open c1
C/End-Exec

Fetch rows into host fields until
SQLStt indicates an error
C dow SQLStt '00000'
C/Exec SQL
C fetch next
C from c1
C into inumbr, idept, idescr
C/End-Exec

If the status field is OK
then process this record.
C if SQLStt '00000'
C exsr ProcessRecord
C endif
fetch next row
C enddo

Close the cursor
C/Exec SQL
C close c1
C/End-Exec

26
Calculated Column Values

Select skunum,
upper(idesc),
qtyoh,
avgcst,
qtyoh avgcst
from invmst

27
Using a literal column value

insert into taxdpt
select 123, txdept, txflag
from taxdpt
where txstrn 734

28
Using row functions in RPG

C eval JDEdate 102003
C/Exec SQL
C set ISOdate DATE(DIGITS(DECIMAL(
C JDEdate1900000,7,0)))
C/End-Exec

29
Counting the records in a file

select count() from invmst
COUNT ( )
530,610

30
Department Subtotals

select idept, count()
from invmst
group by idept
order by idept
Department COUNT ( )
1 30,610
2 26,416
3 74,297
5 23,272
6 40,789

31
Limiting groups with Having

select idept, count()
from invmst
group by idept
having count()
order by idept

32
Selecting records with a subquery

select cunmbr, cuname
from cusmst
where cuytds (select avg(cuytds)
from cusmst)

33
Using a correlated subquery

select cunmbr, cuname
from cusmst as c1
where cuytds (select avg(cuytds)
from cusmst as c2
where c2.custat
c1.custat)

34
The In Predicate

Select from orddtl
where odsku in (select skunbr
from invmst
where idept 4)

35
The Exists Predicate

update cuswrk
set curnme
(select rpname from slsrep
where cuswrk.repnum
slsrep.repnum)
where exists (select from slsrep
where cuswrk.repnum
slsrep.repnum)

36
Old Join Syntax

select ordhdr.ohornm,
ordhdr.ohcusn,
cusmst.cuname
from ordhdr, cusmst
where ordhdr.ohstat O and
ordhdr.ohcusn cusmst.cusnmr

37
New Join Syntax

select ordhdr.ohornm,
ordhdr.ohcusn,
cusmst.cuname
from ordhdr join cusmst
on ordhdr.ohcusn cusmst.cusnmr
where ordhdr.ohstat O

38
Join Types

JOIN or INNER JOIN
Each row in table to left will be joined with one
or more rows in table to right using the join
condition. Any rows in left table that do not
have corresponding rows in the right table will
not be in the result table. Produces the same
results as separating the tables with a comma and
using the where clause to provide the join
condition.

39
Join Types

LEFT JOIN or LEFT OUTER JOIN
Each row in table to left will be joined with one
or more rows in table to right using the join
condition. Any rows in left table that do not
have corresponding rows in the right table will
be in the result table with null values for
fields from the right table.

40
Join Types

EXCEPTION JOIN
Only rows in the left table that have no
corresponding row in the right table using the
join condition will be in the result table. Nulls
are returned for any fields from the right table.

41
Join Types