1 / 21

SQL from the Trenches

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.

deirdre
Download Presentation

SQL from the Trenches

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL from the Trenches Presented by: Robert Arce www.prismatech.net

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

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

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

  5. Select count(distinct DEPARTMENT) from STORDEPT; SQL from the Trenches by Robert Arce from PrismaTech, Inc.

  6. Select DEPARTMENT, count(DEPARTMENT), decimal(avg(ACUMSALES),9,2) from STORDEPT group by DEPARTMENT; SQL from the Trenches by Robert Arce from PrismaTech, Inc.

  7. Selection – Case (21Jan04) 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.

  8. Functions and result 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.

  9. Inner Selection 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.

  10. Left Outer Join 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.

  11. Left Inner Selection 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.

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

  13. Insert entire table 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.

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

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

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

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

  18. IBM iSeries Navigator SQL from the Trenches by Robert Arce from PrismaTech, Inc.

  19. Runsqlstm – text source /* 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.

  20. Runsqlstm – CL 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.

  21. year(CURRENT DATE) - Integer(substr('1997XXX',1,4)) Thank you !!! Result is 7 Good Luck SQL from the Trenches by Robert Arce from PrismaTech, Inc.

More Related