Sql from the trenches
Download
1 / 21

SQL from the Trenches - PowerPoint PPT Presentation


  • 111 Views
  • Updated On :

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.

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

PowerPoint Slideshow about 'SQL from the Trenches' - ismael


An Image/Link below is provided (as is) to download presentation

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
Sql from the trenches
SQL from the Trenches

Presented by:

Robert Arce

www.prismatech.net


Scalar functions

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.

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.


Selection statement

1)SELECT something(s)

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.


Selection

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 from stordept
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
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
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
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
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
Left Outer Join decimal(avg(ACUMSALES),9,2)

Select * from

CUSMS left outer join SALE

on CUSMS.CUSTID = SALE.CUSTID

  • Gets found and not found records setting null values to the not found record fields from the second file.

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


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

Select * from CUSMSADD A

where not exist

(select * from CUSMSADDSV B

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
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
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
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
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.


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

Update CUSMS as C

set (custaddr1, custaddr2, custcity, cusstate, custzip) =

(Select addr1, addr2, city, state, zip

from NEWADDR as NA

where C.custnbr=NA.custnbr)

where custnbr in

(Select custnbr from NEWADDR)

  • 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
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
IBM iSeries Navigator decimal(avg(ACUMSALES),9,2)

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


Runsqlstm text source
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,

substr(WLLOCA,1,3) WRHS, substr(WLLOCA,4,7)ADDR,

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
Runsqlstm – CL decimal(avg(ACUMSALES),9,2)

Add this statement to your CL:

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
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.


ad