|
|
|
|
|
|
|
When doing comparisons within a WHERE clause (the Criteria Column) it is important to know whether you are comparing strings or numbers. Numbers can be used in the SQL statement without any quotations: WHERE CustomerNumber = 1003 Whereas strings need to be delineated with single quotations marks: WHERE CustomerName = ‘Company A’ There are instances where a field appears to be a number like CustomerNumber, however it is stored as a string in the Database. Most of these cases involve leading zeros: WHERE CustomerNumber = ‘0001’ In the Query Designer’s Expression Builder, you have the option to use a parameter as a string or a number. Selecting a parameter as a string inserts the &PARMXX with single quotation marks on either side of the actual value passed to the SQL string, whereas selecting as a number will simply insert whatever value passed directly into the SQL |
|
close |
|
|
|
|
|
Lets say you have a Query that uses a parameter to filter the data down in the Criteria section. However, you also need the same query to run without the filter in place. The way to do that is to use a CASE statement in the Criteria portion If I put the following in the Criteria of my ICLAS field : = CASE WHEN '&PARM01' = '' THEN &lib.IIM.ICLAS ELSE '&PARM01' END I get the following SQL: SELECT &lib.IIM.IPROD, &lib.IIM.IDESC, &lib.IIM.ICLAS FROM &lib.IIM WHERE (((&lib.IIM.ICLAS)= CASE WHEN '&PARM01' = '' THEN &lib.IIM.ICLAS ELSE '&PARM01' END) ) |
|
close |
|
|
|
|
|
When building criteria is often the case where you want to find all the records
where a field starts with a certain letter, or contains a certain substring. To
accomplish this you can use the “%” or the “_” wildcards.
The “%” is a true wildcard ANYTHING or NOTHING can be in that spot:
WHERE myField LIKE “A%”
“A” or “Apple or A123”
WHERE myField LIKE “AB%”
“AB” or “ABcdef”, “ABCEDF”, “AB123”
WHERE myField LIKE “%A%”
“This is A value”, “CAT”, “A”, “Apple”
The “_” is a single position wildcard Only one character can be in that spot:
WHERE myField LIKE “A_”
“A” or “AB or A1” NOT “ABC” or “A123”
WHERE myField LIKE “AB_”
“ABc”, “ABC”, “AB1” NOT “ABCD”
WHERE myField LIKE “_A_”
“RAT”, “CAT”, “BAT” NOT “CATS”, “BRAT”, “A” |
|
close |
|
|
|
|
|
Use IN to find records where the field value is represented in a specified list: WHERE myField IN (‘RALEIGH’,’CHARLOTTE’) WHERE myNum IN (123,124,125) You cannot use a wildcard in the items within the list! |
|
close |
|
|
|
|
|
Use BETWEEN to find records where the field value is INCLUSIVELY within the two specified values” WHERE myDate BETWEEN 19990801 and 19990831 Using BETWEEN is more efficient and more readable than doing: WHERE myDate >= 19990801 AND myDate <= 19990831 |
|
close |
|
|
|
|
|
Lets say you have a Query that uses a parameter to filter the data down in the Criteria section. However, you also need the same query to run without the filter in place. The way to do that is to use a CASE statement in the Criteria portion If I put the following in the Criteria of my ICLAS field : = CASE WHEN '&PARM01' = '' THEN &lib.IIM.ICLAS ELSE '&PARM01' END I get the following SQL: SELECT &lib.IIM.IPROD, &lib.IIM.IDESC, &lib.IIM.ICLAS FROM &lib.IIM WHERE (((&lib.IIM.ICLAS)= CASE WHEN '&PARM01' = '' THEN &lib.IIM.ICLAS ELSE '&PARM01' END) ) |
|
close |
|
|
|
|
|
First you need to understand that using Parameters in the Query Designer provides a way to replace a portion of the SQL statement on the fly, or just prior to executing the SQL statement against the database. Knowing that, we usually use parameters in the Where clause as the value in a comparison for example: CustomerNum= &Parm01. There may be times when you want to use a parameter to specify the left side of a comparison or in the Select xxxx portion itself. In order to accomplish this you need to add a calculated field to your Query, and in the builder simply use the parameter as a number. This will in effect substitute the value you send to the query into that spot within the SQL. For example look at the following Queries SQL text: SELECT &PARM01 AS "Balance" FROM &Lib.CMF The query above was created by adding a calculated field using the first parameter as a number in the builder. Then the Alias was set to ‘Balance’. Now you can send an actual field name to the query for parameter 1 and the SQL will return a different column of data dependent on your passed value! |
|
close |
|
|
|
|
|
SELECT table1.column1, table1.column2,table2.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.keyfield=table2.keyfield When you execute a statement like this, SQL creates a new table—the results recordset—based on the contents of table1 and table2. In this sample statement, the condition (table1.keyfield=table2.keyfield) tells SQL to find records in both tables that contain matching values in the column named by keyfield. If one of the tables contains records that are orphaned—for which there is no corresponding record with a matching value in the other table—those records are ignored. |
|
close |
|
|
|
|
|
The LEFT JOIN: Matching records plus orphans from the left So what makes a LEFT JOIN different from an INNER JOIN? When you execute a query using the LEFT JOIN syntax, SQL does two things: • It returns all of the records from both tables that contain matching values, as defined by the ON clause. • It also returns all of the records from the table on the left side of the FROM…LEFT JOIN key words, even if there aren’t any matching values in the table on the right. |
|
close |
|
|
|
|
|
The RIGHT JOIN: Matching records plus orphans from the right When you execute a query using the RIGHT JOIN syntax, SQL does two things: • It returns all of the records from both tables that contain matching values, as defined by the ON clause. • It also returns all of the records from the table on the right side of the FROM…RIGHT JOIN key words, even if there aren’t any matching values in the table on the left. |
|
close |
|
|
|
|
|
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example: GROUP BY clause syntax: SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list" Let's say you would like to retrieve a list of the highest paid salaries in each dept: SELECT max(salary), dept FROM employee GROUP BY dept This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned. Some points to remember about Group By clauses: • Use a WHERE clause in a query containing a GROUP BY clause to eliminate rows before grouping occurs. • The GROUP BY clause comes after the WHERE clause and before the ORDER BY clause. • Use the HAVING clause to filter rows after they have been grouped. • Grouping columns can be column names or derived columns. • Every non-aggregate column in the SELECT clause must appear in the GROUP BY clause. • Specify multiple grouping columns in the GROUP BY clause to nest groups. Data is summarized at the last specified group • Without an ORDER BY clause, groups returned by GROUP BY aren’t in any particular order. |
|
close |
|
|
|
|
|
If you have a numeric value on the iSeries you can convert that to a string by using the function CHAR() . |
|
close |
|
|
|
|
|
If you have values in a database that are NULL or a join that has no matching records a NULL value is returned. This value can be converted to any value you like using the COALESCE function. This works for SQL Server, iSeries, MDB files and Oracle as well. COALESCE (exp1, exp2 [,expN]...) This will return the first non-null value it encounters in the list. COALESCE( , 0) if is null then the result is Zero, otherwise the value of the fieldname is returned. |
|
close |
|
|
|
|
|
If you need to convert a number to a string with leading 0’s you should use the function DIGITS() |
|
close |
|
|
|
|
|
If you have string data in say a format ‘00001203’, you can remove the leading zeros to get ‘1203’ with the following function. First, use the REPLACE() function to substitute all existing zeros with spaces. Next, use the LTRIM() function to eliminate the leading spaces. Then, use REPLACE () again to turn the spaces back to zeros. The following statement shows this process: REPLACE(LTRIM(REPLACE(myField, '0', ' ')), ' ', '0') |
|
close |
|
|
|
|
|
What if I have a DATE stored like YYYYMMDD (with no dashes or slashes) and I want to compare it to a DB2 date? First use the CHAR function to convert it to a character string. CHAR(Fieldname) Then, you can use the SUBSTR function to break the character column apart into the separate components, for example SUBSTR(column,1,4) returns the year component, SUBSTR(column,5,2) returns the month, and SUBSTR(column,7,2) returns the day. SUBSTR(CHAR(Fieldname),5,2) returns Month Then you can concatenate all of these together into a format that DB2 recognizes, for example, the USA format which is mm/DD/yyyy. This can be done as follows: SUBSTR( CHAR(Fieldname),5,2) || '/' || SUBSTR(CHAR(Fieldname),7,2) || '/' || SUBSTR(CHAR(Fieldname),1,4) Then you can use the DATE function to convert this character string into a DATE that DB2 will recognize. This is done as follows: DATE( SUBSTR( CHAR(Fieldname),5,2) || '/' || SUBSTR(CHAR (Fieldname),7,2) || '/' || SUBSTR(CHAR (Fieldname),1,4)) AS MyDate |
|
close |
|
|
|
|
|
The dates are stored as Century, Year, Month, and Day, where Century is 0 for 19 and 1 for 20, years are stored as two digits, and months and days always have leading zeros. Here is a table of sample data: Log Number Decimal Date Date Represented 1096 821231 December 31, 1982 1461 831231 December 31, 1983 2192 851231 December 31, 1985 7305 991231 December 31, 1999 7306 1000101 January 1, 2000 10228 1080101 January 1, 2008 By adding 19000000 to the date field, you create a number that can be easily substringed to get the relevant year, month and day. Here is a snippet of SQL that converts the field YMD to a date using ISO format: DATE( SUBSTR(CHAR(19000000 + YMD),1,4) ||’-‘|| SUBSTR(CHAR(19000000 + YMD),5,2) ||’-‘|| SUBSTR(CHAR(19000000 + YMD),7,2) ) If the date separator is a forward slash (/), you could also convert the date this way: DATE( SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘|| SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘|| SUBSTR(CHAR(19000000 + YMD),1,4) ) |
|
close |
|
|
|
|
|
What if I want to convert a J.D. Edwards date, (CYYDDD where C represents the century, YY represents the year, and DDD represents the day of the year) to a more readable format, (like YYYY-MM-DD).
One of the interesting formats of the DATE function is a seven-character string in the form YYYYNNN where YYYY is the year and NNN is the day number. When passed this information, DATE will return a date data type value representative of the string that was passed in.
To take advantage of this feature, we need only to convert the DECIMAL(6,0) representation of the date into the required CHAR(7) representation and pass it to the date function.
The first step is to convert the CYY representation of the year to a four-digit year:
DECIMAL(d + 1900000,7,0))
The next step is to convert the seven-digit decimal number to a character string. The DIGITS function does this nicely:
DIGITS(DECIMAL(d +1900000,7,0)))
Here is an example statement using the transformation and the resulting data:
SELECT DATE(DIGITS( DECIMAL(d +1900000,7,0)) ) AS CONVDATE
FROM ….. |
|
close |
|
|
|
|
|
The DATE(yyyyNNN) function in AS400 SQL will return a date field from Julian date. We must simply figure out the correct century and pass the full value yyyyNNN to the Date function. Assuming when YY is between 30-99 the year is considered to be in the 19th century, and if YY is less than 30 assume the date is in the 20th century. CASE WHEN Fieldname > 0 THEN DATE( TRIM( CHAR( CASE WHEN INT(SUBSTR(CHAR( Fieldname ),1,2)) >30 THEN 1900 + INT(SUBSTR(CHAR( Fieldname),1,2)) ELSE 2000 + INT(SUBSTR(CHAR( Fieldname),1,2)) END ) ) CONCAT SUBSTR(CHAR( Fieldname),3,3) ) ELSE DATE('1900-01-01') END As an alternative we could build a date to the first day of the first month of the correct year. Then, add the number of days NNN to the Date, then subtract one since we started on the first of the year. Fieldname is the field with the Julian Date. CASE WHEN Fieldname > 0 THEN DATE( TRIM( CHAR( CASE WHEN INT(SUBSTR(CHAR( Fieldname ),1,2)) >30 THEN 1900 + INT(SUBSTR(CHAR( Fieldname ),1,2)) ELSE 2000 + INT(SUBSTR(CHAR( Fieldname ),1,2)) END ) ) CONCAT '-1-1' ) + (( INT( SUBSTR(CHAR( Fieldname),3,3) ) ) -1 ) DAYS ELSE DATE('1900-01-01') END |
|
close |
|
|
|
|
|
Use a calculated field or use this in some criteria:
YEAR((CURRENT DATE)) * 1000 + DAYOFYEAR(CURRENT DATE) |
|
close |
|
|
|
|
|
What if I want to convert a number, (CCYYDD where CC represents the century, YY represents the year, and DD represents the day of the month) to an actual Date Type in MS Access. CVDate( mid( ,1,4) & '-' & mid( ,5,2) & '-' & mid( ,7,2) ) This will return a value in machines default date/time format. Most likely: 1/23/2001 12:00:00 AM You can format the result with the following format( CVDate( mid(,1,4) & '-' & mid(,5,2) & '-' & mid(,7,2) ) , 'mm-dd-yyyy') |
|
close |
|
|
|
|
|
When you subtract one date from another, Query gives you an eight-digit date duration. The first four digits represent a number of years, the next two digits are a number of months, and the last four digits represent a number of days. For example a returned value 102 means one month and two days, not 102 days. Here is the correct way to determine the number of days between the two dates. days(reqdate) - days(entdate) |
|
close |
|
|
|
|
|
Use the CONVERT function in SQL Server to convert a string to a SQL Server date: CONVERT( datetime, ‘01/01/2005’) |
|
close |
|
|
|
|
|
You can use the SQL Server Convert function to format the date into a string in a certain format. CONVERT ( data_type [ ( length) ] , expression [ , style ] ) Where data_type [(length)] is the target data type format and length, expression is any valid Microsoft expression that represents the date/time you want to display, and style specifies the output format for the data/time. Using the CONVERT function, with different styles, allows you to display date and time data in many different formats. Let's look at what I am taking about. The easiest way to demonstrate how to use the CONVERT function is to review some TSQL code that displays the current time in a few different display formats. The following uses only the CONVERT function to display the different formats. HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==> CONVERT(CHAR(19),GETDATE()) HERE IS MM-DD-YY FORMAT ==> CONVERT(CHAR(8),GETDATE(),10) HERE IS MM-DD-YYYY FORMAT ==> CONVERT(CHAR(10),GETDATE(),110) HERE IS DD MON YYYY FORMAT ==> CONVERT(CHAR(11),GETDATE(),106) HERE IS DD MON YY FORMAT ==> CONVERT(CHAR(9),GETDATE(),6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==> CONVERT(CHAR(24),GETDATE(),113) You can use whatever 'date' value in place of the GETDATE() function |
|
close |
|
|
|
|
|
|
|
|
|
|
Frequently Asked Questions
