1 / 21

SQL from the Trenches - PowerPoint PPT Presentation

SQL from the Trenches. Presented by: Robert Arce. CHAR DATE SUBSTR DAYS DECIMAL DIGITS Literals: CURRENT DATE CURRENT TIME TIMESTAMP NULL (is/is not). Examples: DECIMAL(Salary/52, 9, 2) It will round the result INTEGER(Salary) it will round the number to the next integer.

Related searches for SQL from the Trenches

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

PowerPoint Slideshow about 'SQL from the Trenches' - ismael

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Presented by:

Robert Arce

www.prismatech.net

DATE

SUBSTR

DAYS

DECIMAL

DIGITS

Literals:

CURRENT DATE

CURRENT TIME

TIMESTAMP

NULL (is/is not)

Examples:

DECIMAL(Salary/52, 9, 2) It will round the result

INTEGER(Salary)

it will round the number to the next integer.

COUNT(DISTINCT DEPT) Different departments

DAY( DATE(CURRENT DATE) - DATE('2003-01-01') )

Number of days on this year

Scalar Functions

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

2)FROM somewhere

3)WHERE condition(s)

4)GROUP BY column(s)

5)HAVING condition(s)

6)ORDER BY something

Valid Combination:

1,2,3

1,2,4

1,2,6

1,2,3,4

1,2,3,6

1,2,3,4,5

1,2,3,4,5,6

Selection Statement

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Select DEPARTMENT, count(DEPARTMENT), decimal(avg(ACUMSALES),9,2)

from STORDEPT group by DEPARTMENT;

HAVING COUNT(*) > 1; (gets departments with multiple Emp.)

HAVING Dept IN('D100', 'D300');

Remember: the Having clause can only have columns included in the group by statement.

When using Group By, every field in the Select clause except count(*) or the field(s) having the sum, avg, min, max, var functions must be included in the Group By clause.

Selection

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Select count(distinct DEPARTMENT) decimal(avg(ACUMSALES),9,2) from STORDEPT;

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Select DEPARTMENT, count(DEPARTMENT), decimal(avg(ACUMSALES),9,2) from STORDEPT group by DEPARTMENT;

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Selection – Case (21Jan04) decimal(avg(ACUMSALES),9,2)

Select substr(digits(day(current date)),9,2) ||

(case month(current_date)

when 01 then 'Jan' when 02 then 'Feb'

when 03 then 'Mar' when 04 then 'Apr'

when 05 then 'May' when 06 then 'Jun'

when 07 then 'Jul' when 08 then 'Aug'

when 09 then 'Sep' when 10 then 'Oct'

when 11 then 'Nov’ else 'Dec' end) ||

substr(digits(year(current date)),9,2)

From lib/file

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Functions and result decimal(avg(ACUMSALES),9,2)

current date  01/21/04

day(current date)  21 (numeric)

digits(day(current date))  0000000021

substr(digits(day(current date)),9,2)  21 alpha

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Inner Selection decimal(avg(ACUMSALES),9,2)

Select * from CUSMS

where custno in (

Select custnum from CUSSLS

where cussales > 2000000 and yearsls = 2003)

• Selects records from CUSMS file only if the customer number is found in CUSSLS file …

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Left Outer Join decimal(avg(ACUMSALES),9,2)

Select * from

CUSMS left outer join SALE

on CUSMS.CUSTID = SALE.CUSTID

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Left Inner Selection decimal(avg(ACUMSALES),9,2)

where not exist

where A.company=B.company and

A.customer=B.customer)

• It will bring only records from A file that don't exist in the B file.

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Insert decimal(avg(ACUMSALES),9,2)

Insert into DEPT

(DEPT_ID, DEPT_NAME, MANAGER) VALUES ('D444', ,'334339')

• A comma has been used as a place holder.

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Insert entire table decimal(avg(ACUMSALES),9,2)

Insert into DEPT

Select * from TEST_DEPT

where DEPT_NAME = ‘IMPORT’

• Assumes that the TEST_DEPT table has the same number of columns in the same order as DEPT.

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Delete decimal(avg(ACUMSALES),9,2)

Delete from EMPLOYEE

where SALARY > 45000

or DEPT_ID = ‘D999’

• Only deletes WHOLE rows. Select first and then change the selection for a deletion.

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Update decimal(avg(ACUMSALES),9,2)

update CUSMS set CMCSCL='99'

where cmcsno in

(select SMCUS from REPSUP, CUSMS

where SMCUS = CMCSNO

and SMREPT <> 'WR' and CMCSCL <> '99'

and SMSUSP <> 'S')

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Update decimal(avg(ACUMSALES),9,2)

Update CUSMS as C

where C.custnbr=NA.custnbr)

where custnbr in

• Updates fields in file C from file NA making the join by the cusnbr field.

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

STRSQL decimal(avg(ACUMSALES),9,2)

F13=Services this function will allow you to access the session services. One of the most important options is: 1. Change session attributes. In this option you can set the session to create a file that you specify as the output of you selection instead of just being display on your screen.

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

IBM iSeries Navigator decimal(avg(ACUMSALES),9,2)

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Runsqlstm – text source decimal(avg(ACUMSALES),9,2)

/* 00212 Robert Arce (PrismaTech, Inc.) */

/* DL Dedicated Locations */

Delete from DEDLOC; marks end of statement

Insert into DEDLOC

(select substr(WLITNO,1,20) ITEM,

integer(WLRSLV) MIN, integer(WLRSQT) MAX from LOCATIONS

where WLITNO <> ' ' and WLASCD ='P');

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

Runsqlstm – CL decimal(avg(ACUMSALES),9,2)

RUNSQLSTM SRCFILE(&LIBSRC/QSQLSRC) SRCMBR(mytextsource) COMMIT(*NONE)

• Set your file overrides and library list for your sql to use them when executing.

SQL from the Trenches by Robert Arce from PrismaTech, Inc.

year(CURRENT DATE) - Integer(substr('1997XXX',1,4)) decimal(avg(ACUMSALES),9,2)

Thank you !!!

Result is 7

Good Luck

SQL from the Trenches by Robert Arce from PrismaTech, Inc.