1 / 37

FOCUS to SQL DeMystified

FOCUS to SQL DeMystified. Walter Brengel June, 2008. FOCUS To SQL DeMystified - Agenda. SQL Overview SQL Traces Verb Objects Sorting Record Selection Defining Fields Combining Files. FOCUS To SQL Demystified – What IS SQL?.

joella
Download Presentation

FOCUS to SQL DeMystified

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. FOCUS to SQL DeMystified Walter Brengel June, 2008

  2. FOCUS To SQL DeMystified - Agenda • SQL Overview • SQL Traces • Verb Objects • Sorting • Record Selection • Defining Fields • Combining Files

  3. FOCUS To SQL Demystified – What IS SQL? • Language Used By The Relational Database Management System (RDBMS). • Generic Across Databases • Mostly – Generic Statement Will Work Across Databases • Differences: • WHERE Masking Using LIKE (% vs *) • Functions • Allows Selecting, Inserting And Updating Records Into A Table

  4. FOCUS To SQL DeMystified – What Is SQL? Simple Select Statement: select t1.column1, sum(t1.column2) from tablename t1 where t1.columnname = 'value' group by t1.column1 order by t1.column1;

  5. FOCUS To SQL DeMystified – TRACE Settings How To See The SQL Generated By FOCUS: SET TRACEOFF=ALL SET TRACEON=SQLAGGR//CLIENT SET TRACEON=STMTRACE//CLIENT SET TRACESTAMP=OFF SET TRACEUSER=ON SET XRETRIEVAL=OFF

  6. FOCUS To SQL Demystified – TRACE Settings • SQLAGGR • Displays Data Adapter‑to‑RDBMS Aggregation And Join Analysis. • Used Only For Focus Reporting Operations Such As Table, Graph, And Match File. • If The Data Adapter Is Able To Pass All Join, Sort, And Aggregation Operations, The SQLAGGR Trace Is Not Populated. The Message "AGGREGATION DONE..." Appears. • Formerly FSTRACE3.

  7. FOCUS To SQL DeMystified – TRACE Settings • STMTRACE • Records SQL SELECT Statements Generated By The Data Adapter For FOCUS Report Requests. • Modify Procedures, Or Direct Sql Passthru Select Requests. It Also Records The SQL Data. • Definition Language (DDL) Statements Generated By The Create File Command. • Display The Trace Information Online. • Store It In A File Or Sequential Data Set. • The Data Adapter Terminates Its Generated SQL Select Statements With A Semicolon. • You Can Submit Them To The RDBMS For Processing, Interactively Or In Batch. • Use STMTRACE for: • Debugging. • Performance Tuning. • Capturing SQL Data Definition And Data Manipulation Statements To Reuse. • Formerly FSTRACE4.

  8. FOCUS To SQL DeMystified Northwind Orders Table MASTER: FILENAME=ORDERS, SUFFIX=SQLMSS , $ SEGMENT=ORDERS, SEGTYPE=S0, $ FIELDNAME=ORDERID, ALIAS=OrderID, USAGE=I11, ACTUAL=I4, FIELDTYPE=R,$ FIELDNAME=CUSTOMERID, ALIAS=CustomerID, USAGE=A5, ACTUAL=A5, MISSING=ON, $ FIELDNAME=EMPLOYEEID, ALIAS=EmployeeID, USAGE=I11, ACTUAL=I4, MISSING=ON, $ FIELDNAME=ORDERDATE, ALIAS=OrderDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=REQUIREDDATE, ALIAS=RequiredDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=SHIPPEDDATE, ALIAS=ShippedDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=SHIPVIA, ALIAS=ShipVia, USAGE=I11, ACTUAL=I4, MISSING=ON, $ FIELDNAME=FREIGHT, ALIAS=Freight, USAGE=P21.4, ACTUAL=P10, MISSING=ON, $ FIELDNAME=SHIPNAME, ALIAS=ShipName, USAGE=A40V, ACTUAL=A40V, MISSING=ON, $ FIELDNAME=SHIPADDRESS, ALIAS=ShipAddress, USAGE=A60V, ACTUAL=A60V, MISSING=ON, $ FIELDNAME=SHIPCITY, ALIAS=ShipCity, USAGE=A15V, ACTUAL=A15V, MISSING=ON, $ FIELDNAME=SHIPREGION, ALIAS=ShipRegion, USAGE=A15V, ACTUAL=A15V, MISSING=ON, $ FIELDNAME=SHIPPOSTALCODE, ALIAS=ShipPostalCode, USAGE=A10V, ACTUAL=A10V, MISSING=ON, $ FIELDNAME=SHIPCOUNTRY, ALIAS=ShipCountry, USAGE=A15V, ACTUAL=A15V, MISSING=ON, $ ACCESS: SEGNAME=ORDERS, TABLENAME=Northwind.dbo.Orders, CONNECTION=CON01, KEYS=1, $

  9. FOCUS To SQL DeMystified – PRINT TABLE FILE ORDERS PRINT ORDERID CUSTOMERID EMPLOYEEID ORDERDATE END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."OrderID",T1."CustomerID",T1."EmployeeID", T1."OrderDate" FROM Northwind.dbo.Orders T1; ...RETRIEVAL KILLED 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

  10. FOCUS To SQL DeMystified – Sorting TABLE FILE ORDERS PRINT ORDERID CUSTOMERID ORDERDATE BY EMPLOYEEID END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."OrderID",T1."CustomerID",T1."EmployeeID", T1."OrderDate" FROM Northwind.dbo.Orders T1 ORDER BY T1."EmployeeID"; ...RETRIEVAL KILLED 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

  11. FOCUS To SQL DeMystified Master File: FILENAME=ORDER_DETAILS, SUFFIX=SQLMSS , $ SEGMENT=ORDER_DETAILS, SEGTYPE=S0, $ FIELDNAME=ORDERID, ALIAS=OrderID, USAGE=I11, ACTUAL=I4, $ FIELDNAME=PRODUCTID, ALIAS=ProductID, USAGE=I11, ACTUAL=I4, $ FIELDNAME=UNITPRICE, ALIAS=UnitPrice, USAGE=P21.4, ACTUAL=P10, $ FIELDNAME=QUANTITY, ALIAS=Quantity, USAGE=I6, ACTUAL=I2, $ FIELDNAME=DISCOUNT, ALIAS=Discount, USAGE=D20.2, ACTUAL=D8, $ Access File: SEGNAME=ORDER_DETAILS, TABLENAME=Northwind.dbo."Order Details", CONNECTION=CON01, KEYS=2, $

  12. FOCUS To SQL DeMystified – SUM TABLE FILE ORDER_DETAILS SUM UNITPRICE QUANTITY BY ORDERID END FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity") FROM Northwind.dbo."Order Details" T1 GROUP BY T1."OrderID" ORDER BY T1."OrderID";

  13. FOCUS To SQL DeMystified – SUM FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity") FROM Northwind.dbo."Order Details" T1 GROUP BY T1."OrderID" ORDER BY T1."OrderID"; ? 2589 (FOC2589) AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS %1%2%3%4 This is the first line of the Interface FSTRACE3 output that is generated when RDBMS aggregation has been performed, but may produce different re- sults than FOCUS would have if optimization had been disabled and FOCUS had managed the JOINs and aggregation. Examine report results carefully.

  14. FOCUS To SQL DeMystified – COMPUTE TABLE FILE ORDER_DETAILS SUM UNITPRICE QUANTITY COMPUTE TTLPRICE/D12.2=UNITPRICE * QUANTITY; BY ORDERID END FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity") FROM Northwind.dbo."Order Details" T1 GROUP BY T1."OrderID" ORDER BY T1."OrderID";

  15. FOCUS To SQL DeMystified – DEFINE DEFINE FILE ORDER_DETAILS TTLPRICE/D12.2=UNITPRICE * QUANTITY; END TABLE FILE ORDER_DETAILS SUM UNITPRICE QUANTITY TTLPRICE BY ORDERID END FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity"), SUM((T1."UnitPrice" * T1."Quantity")) FROM Northwind.dbo."Order Details" T1 GROUP BY T1."OrderID" ORDER BY T1."OrderID";

  16. FOCUS To SQL DeMystified – DEFINE DEFINE FILE ORDER_DETAILS TTLPRICE/D12.2=UNITPRICE * QUANTITY; COSTFLAG/A10=IF TTLPRICE GT 5000.00 THEN 'HIGH' ELSE IF TTLPRICE GT 1000.00 THEN 'MEDIUM' ELSE 'LOW'; END TABLE FILE ORDER_DETAILS SUM UNITPRICE QUANTITY TTLPRICE BY ORDERID WHERE COSTFLAG EQ 'HIGH' END SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity"), SUM((T1."UnitPrice" * T1."Quantity")) FROM Northwind.dbo."Order Details" T1 WHERE ((CASE WHEN ((T1."UnitPrice" * T1."Quantity") > 5000) THEN 'HIGH' WHEN ((T1."UnitPrice" * T1."Quantity") > 1000) THEN 'MEDIUM' ELSE 'LOW' END) = 'HIGH') GROUP BY T1."OrderID" ORDER BY T1."OrderID";

  17. FOCUS To SQL DeMystified – Settings SQL engine ? (FOC1450) CURRENT SQLMSS INTERFACE SETTINGS ARE : (FOC1656) DEFAULT SERVER NAME - : CON01 cp01211dt1\dt1_sql (FOC1502) USERID AND PASSWORD ARE - : <Trusted> (FOC1758) CURRENT ISOLATION LEVEL IS - : RC (FOC1496) AUTODISCONNECT OPTION IS - : ON FIN (FOC1499) AUTOCOMMIT OPTION IS - : ON COMMAND (FOC1491) FETCH BUFFERING FACTOR - : 100 (FOC1755) CURSORS TYPE IS - : SERVER (FOC1723) TRANSACTION MODE IS - : LOCAL (FOC1441) WRITE FUNCTIONALITY IS - : ON (FOC1445) OPTIMIZATION OPTION IS - : ON (FOC1763) IF-THEN-ELSE OPTIMIZATION IS - : OFF (FOC1709) NCHAR OPTION IS - : SBCS

  18. FOCUS To SQL DeMystified – OPTIFTHENELSE SQL SQLMSS SET OPTIFTHENELSE ON DEFINE FILE ORDER_DETAILS TTLPRICE/D12.2=UNITPRICE * QUANTITY; COSTFLAG/A10=IF TTLPRICE GT 5000.00 THEN 'HIGH' ELSE IF TTLPRICE GT 1000.00 THEN 'MEDIUM' ELSE 'LOW'; END TABLE FILE ORDER_DETAILS SUM UNITPRICE QUANTITY TTLPRICE BY ORDERID WHERE COSTFLAG EQ 'HIGH' END SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity"), SUM((T1."UnitPrice" * T1."Quantity")) FROM Northwind.dbo."Order Details" T1 WHERE ((((T1."UnitPrice" * T1."Quantity") > 5000))) GROUP BY T1."OrderID" ORDER BY T1."OrderID";

  19. FOCUS To SQL DeMystified – JOINs Type of JOINS INNER LEFT OUTER FULL

  20. FOCUS To SQL DeMystified – JOINs MASTER FILE: FILENAME=EMPLOYEETERRITORIES, SUFFIX=SQLMSS , $ SEGMENT=EMPLOYEETERRITORIES, SEGTYPE=S0, $ FIELDNAME=EMPLOYEEID, ALIAS=EmployeeID, USAGE=I11, ACTUAL=I4, $ FIELDNAME=TERRITORYID, ALIAS=TerritoryID, USAGE=A20V, ACTUAL=A20V, $ MASTER FILE: FILENAME=TERRITORIES, SUFFIX=SQLMSS , $ SEGMENT=TERRITORIES, SEGTYPE=S0, $ FIELDNAME=TERRITORYID, ALIAS=TerritoryID, USAGE=A20V, ACTUAL=A20V, $ FIELDNAME=TERRITORYDESCRIPTION, ALIAS=TerritoryDescription, USAGE=A50, ACTUAL=A50, $ FIELDNAME=REGIONID, ALIAS=RegionID, USAGE=I11, ACTUAL=I4, $

  21. FOCUS To SQL DeMystified – INNER JOINs JOIN INNER TERRITORYID IN EMPLOYEETERRITORIES TO TERRITORYID IN TERRITORIES AS J1 TABLE FILE EMPLOYEETERRITORIES PRINT TERRITORYDESCRIPTION BY EMPLOYEEID BY TERRITORYID END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."EmployeeID",T1."TerritoryID", T2."TerritoryDescription" FROM Northwind.dbo.EmployeeTerritories T1,Northwind.dbo.Territories T2WHERE (T2."TerritoryID" = T1."TerritoryID") ORDER BY T1."EmployeeID",T1."TerritoryID";

  22. FOCUS To SQL DeMystified – LEFT OUTER JOIN JOIN LEFT_OUTER TERRITORYID IN EMPLOYEETERRITORIES TO TERRITORYID IN TERRITORIES AS J1 END TABLE FILE EMPLOYEETERRITORIES PRINT TERRITORYDESCRIPTION BY EMPLOYEEID BY TERRITORYID END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."EmployeeID",T1."TerritoryID",T2."TerritoryID", T2."TerritoryDescription" FROM ( Northwind.dbo.EmployeeTerritories T1 LEFT OUTER JOIN Northwind.dbo.Territories T2 ON T2."TerritoryID" = T1."TerritoryID" ) ORDER BY T1."EmployeeID",T1."TerritoryID";

  23. FOCUS To SQL DeMystified – JOIN from HOLD TABLE FILE EMPLOYEETERRITORIES PRINT EMPLOYEEID TERRITORYID ON TABLE HOLD END JOIN TERRITORYID IN HOLD TO TERRITORYID IN TERRITORIES AS J1 TABLE FILE HOLD PRINT TERRITORYDESCRIPTION BY EMPLOYEEID BY TERRITORYID END

  24. FOCUS To SQL DeMystified – JOIN from HOLD TABLE FILE EMPLOYEETERRITORIES PRINT EMPLOYEEID TERRITORYID ON TABLE HOLD END JOIN TERRITORYID IN HOLD TO TERRITORYID IN TERRITORIES AS J1 TABLE FILE HOLD PRINT TERRITORYDESCRIPTION BY EMPLOYEEID BY TERRITORYID END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."EmployeeID",T1."TerritoryID" FROM Northwind.dbo.EmployeeTerritories T1; ...RETRIEVAL KILLED 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

  25. FOCUS To SQL DeMystified – JOIN from HOLD TABLE FILE EMPLOYEETERRITORIES PRINT EMPLOYEEID TERRITORYID ON TABLE HOLD END JOIN TERRITORYID IN HOLD TO TERRITORYID IN TERRITORIES AS J1 TABLE FILE HOLD PRINT TERRITORYDESCRIPTION BY EMPLOYEEID BY TERRITORYID END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2599 - NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT) SELECT T2."TerritoryDescription" FROM Northwind.dbo.Territories T2 WHERE (T2."TerritoryID" = ?); ...RETRIEVAL KILLED 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

  26. FOCUS To SQL DeMystified – INTEGER Arithmetic DEFINE FILE ORDER_DETAILS QTRDEF/P10.3=QUANTITY / 4; END TABLE FILE ORDER_DETAILS SUM QUANTITY QTRDEF COMPUTE QTRCOMP/P10.3=QUANTITY / 4; BY ORDERID WHERE ORDERID LE 10250 END FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P SELECT T1."OrderID", SUM(T1."Quantity"), SUM((T1."Quantity" / 4)) FROM Northwind.dbo."Order Details" T1 WHERE (T1."OrderID" <= 10250) GROUP BY T1."OrderID" ORDER BY T1."OrderID"; 0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3 ORDERID QUANTITY QTRDEF QTRCOMP ------- -------- ------ ------- 10248 27 6.000 6.750 10249 49 12.000 12.250 10250 60 13.000 15.000

  27. FOCUS To SQL DeMystified – INTEGER Arithmetic DEFINE FILE ORDER_DETAILS QTRDEF/P10.3=QUANTITY / 4; END TABLE FILE ORDER_DETAILS SUM QUANTITY QTRDEF COMPUTE QTRCOMP/P10.3=QUANTITY / 4; BY ORDERID SUM QUANTITY QTRDEF BY ORDERID BY PRODUCTID WHERE ORDERID LE 10250 END ORDERID QUANTITY QTRDEF QTRCOMP PRODUCTID QUANTITY QTRDEF ------- -------- ------ ------- --------- -------- ------ 10248 27 6.000 6.750 11 12 3.000 42 10 2.000 72 5 1.000 10249 49 12.000 12.250 14 9 2.000 51 40 10.000 10250 60 13.000 15.000 41 10 2.000 51 35 8.000 65 15 3.000

  28. FOCUS To SQL DeMystified – INTEGER Arithmetic SQL SQLMSS SET OPT NOAGGR DEFINE FILE ORDER_DETAILS QTRDEF/P10.3=QUANTITY / 4; END TABLE FILE ORDER_DETAILS SUM QUANTITY QTRDEF COMPUTE QTRCOMP/P10.3=QUANTITY / 4; BY ORDERID WHERE ORDERID LE 10250 END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2616 - AGGREGATION DISABLED BY USER SELECT T1."OrderID",T1."Quantity" FROM Northwind.dbo."Order Details" T1 WHERE (T1."OrderID" <= 10250) ORDER BY T1."OrderID"; ORDERID QUANTITY QTRDEF QTRCOMP ------- -------- ------ ------- 10248 27 6.750 6.750 10249 49 12.250 12.250 10250 60 15.000 15.000

  29. FOCUS To SQL DeMystified – SQLAGGR SQL engine SET OPT AGGR SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity"), SUM((T1."UnitPrice" * T1."Quantity")) FROM Northwind.dbo."Order Details" T1 WHERE ((((T1."UnitPrice" * T1."Quantity") > 5000))) GROUP BY T1."OrderID" ORDER BY T1."OrderID"; SQL engine SET OPT NOAGGR SELECT T1."OrderID", SUM(T1."UnitPrice"), SUM(T1."Quantity"), SUM((T1."UnitPrice" * T1."Quantity")) FROM Northwind.dbo."Order Details" T1 WHERE ((CASE WHEN ((T1."UnitPrice" * T1."Quantity") > 5000) THEN 'HIGH' WHEN ((T1."UnitPrice" * T1."Quantity") > 1000) THEN 'MEDIUM' ELSE 'LOW' END) = 'HIGH') GROUP BY T1."OrderID" ORDER BY T1."OrderID";

  30. FOCUS To SQL DeMystified – DATE Conditions Northwind Orders table Master File: FILENAME=ORDERS, SUFFIX=SQLMSS , $ SEGMENT=ORDERS, SEGTYPE=S0, $ FIELDNAME=ORDERID, ALIAS=OrderID, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $ FIELDNAME=CUSTOMERID, ALIAS=CustomerID, USAGE=A5, ACTUAL=A5, MISSING=ON, $ FIELDNAME=EMPLOYEEID, ALIAS=EmployeeID, USAGE=I11, ACTUAL=I4, MISSING=ON, $ FIELDNAME=ORDERDATE, ALIAS=OrderDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=REQUIREDDATE, ALIAS=RequiredDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=SHIPPEDDATE, ALIAS=ShippedDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=SHIPVIA, ALIAS=ShipVia, USAGE=I11, ACTUAL=I4, MISSING=ON, $ FIELDNAME=FREIGHT, ALIAS=Freight, USAGE=P21.4, ACTUAL=P10, MISSING=ON, $ FIELDNAME=SHIPNAME, ALIAS=ShipName, USAGE=A40V, ACTUAL=A40V, MISSING=ON, $ FIELDNAME=SHIPADDRESS, ALIAS=ShipAddress, USAGE=A60V, ACTUAL=A60V, MISSING=ON, $ FIELDNAME=SHIPCITY, ALIAS=ShipCity, USAGE=A15V, ACTUAL=A15V, MISSING=ON, $ FIELDNAME=SHIPREGION, ALIAS=ShipRegion, USAGE=A15V, ACTUAL=A15V, MISSING=ON, $ FIELDNAME=SHIPPOSTALCODE, ALIAS=ShipPostalCode, USAGE=A10V, ACTUAL=A10V, MISSING=ON, $ FIELDNAME=SHIPCOUNTRY, ALIAS=ShipCountry, USAGE=A15V, ACTUAL=A15V, MISSING=ON, $ Access File: SEGNAME=ORDERS, TABLENAME=Northwind.dbo.Orders, CONNECTION=CON01, KEYS=1, $

  31. FOCUS To SQL DeMystified – DATE Conditions DEFINE FILE ORDERS ORDERDT/YYMD=HDATE(ORDERDATE,'YYMD'); END TABLE FILE ORDERS PRINT ORDERID EMPLOYEEID CUSTOMERID ORDERDATE WHERE ORDERDT EQ '1996/07/08' END FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : ORDERDT FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."OrderID",T1."CustomerID",T1."EmployeeID", T1."OrderDate" FROM Northwind.dbo.Orders T1; ORDERID EMPLOYEEID CUSTOMERID ORDERDATE ------- ---------- ---------- --------- 10250 4 HANAR 1996/07/08 00:00:00.000 10251 3 VICTE 1996/07/08 00:00:00.000

  32. FOCUS To SQL DeMystified – Report Statistics ? STAT STATISTICS OF LAST COMMAND RECORDS = 2 SEGS DELTD = 0 LINES = 2 NOMATCH = 0 BASEIO = 0 DUPLICATES = 0 SORTIO = 0 FORMAT ERRORS = 0 SORT PAGES = 0 INVALID CONDTS = 0 READS = 830 OTHER REJECTS = 0 TRANSACTIONS = 0 CACHE READS = 0 ACCEPTED = 0 MERGES = 0 SEGS INPUT = 0 SORT STRINGS = 0 SEGS CHNGD = 0 INDEXIO = 0 INTERNAL MATRIX CREATED: YES AUTOINDEX USED: NO SORT USED: SQL AUTOPATH USED: NO AGGREGATION BY EXT.SORT: NO HOLD FROM EXTERNAL SORT: NO

  33. FOCUS To SQL DeMystified – DATE Conditions Northwind Orders table Master File: FILENAME=ORDERS, SUFFIX=SQLMSS , $ SEGMENT=ORDERS, SEGTYPE=S0, $ FIELDNAME=ORDERID, ALIAS=OrderID, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $ FIELDNAME=CUSTOMERID, ALIAS=CustomerID, USAGE=A5, ACTUAL=A5, MISSING=ON, $ FIELDNAME=EMPLOYEEID, ALIAS=EmployeeID, USAGE=I11, ACTUAL=I4, MISSING=ON, $ FIELDNAME=ORDERDATE, ALIAS=OrderDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ FIELDNAME=ORDERDT, ALIAS=OrderDate, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ Access File: SEGNAME=ORDERS, TABLENAME=Northwind.dbo.Orders, CONNECTION=CON01, KEYS=1, $

  34. FOCUS To SQL DeMystified – DATE Conditions TABLE FILE ORDERS PRINT ORDERID EMPLOYEEID CUSTOMERID ORDERDATE WHERE ORDERDT EQ '1996/07/08' END FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED SELECT T1."OrderID",T1."CustomerID",T1."EmployeeID", T1."OrderDate",T1."OrderDate" FROM Northwind.dbo.Orders T1 WHERE (T1."OrderDate" = '19960708'); ORDERID EMPLOYEEID CUSTOMERID ORDERDATE ------- ---------- ---------- --------- 10250 4 HANAR 1996/07/08 00:00:00.000 10251 3 VICTE 1996/07/08 00:00:00.000

  35. FOCUS To SQL DeMystified – Report Statistics ? STAT STATISTICS OF LAST COMMAND RECORDS = 2 SEGS DELTD = 0 LINES = 2 NOMATCH = 0 BASEIO = 0 DUPLICATES = 0 SORTIO = 0 FORMAT ERRORS = 0 SORT PAGES = 0 INVALID CONDTS = 0 READS = 2 OTHER REJECTS = 0 TRANSACTIONS = 0 CACHE READS = 0 ACCEPTED = 0 MERGES = 0 SEGS INPUT = 0 SORT STRINGS = 0 SEGS CHNGD = 0 INDEXIO = 0 INTERNAL MATRIX CREATED: YES AUTOINDEX USED: NO SORT USED: SQL AUTOPATH USED: NO AGGREGATION BY EXT.SORT: NO HOLD FROM EXTERNAL SORT: NO

  36. FOCUS To SQL DeMystified - Review • SQL Overview • SQL Traces • Verb Objects • Sorting • Record Selection • Defining Fields • Combining Files

  37. FOCUS To SQL DeMystified – Questions?? Thanks for Coming

More Related