In database files, you sometimes find that a date has been stored in three separate fields: one for the year, one for the month, and one for the day. If you need to compare two such dates using SQL, you can do it by calculating a single value for each date. For example, suppose the first date occupies fields YY1, MM1, and DD1, and the second date occupies fields YY2, MM2, and DD2. In this case, you could use the following SQL statement:

 SELECT * FROM EMPMST WHERE (YY1*10000+MM1*100+DD1) > (YY2*10000+MM2*100+DD2) 

In many situations, part of a field is used as the key for the sorting purposes. For example, suppose you have a ten-byte character field called CODE and you want to sequence the records by the first two bytes followed by positions 5 and 6. Here's what the SQL statement might look like:

 SELECT SUBSTR(CODE,1,2) CONCAT SUBSTR(CODE,5,2), NAME, REGN FROM SUPMST WHERE REGN = 'NORTH' ORDER BY 1 

The first field in the SELECT statement is derived by extracting two portions of the CODE field and concatenating them. In the ORDER BY clause, 1 means use the first field in the SELECT list.

As another example, sequencing that says ORDER BY 4, 2 means use the fourth and second fields from the select list for sequencing. Specifying fields by relative position number in the ORDER BY clause is required whenever you are referencing derived fields.

If you have a database file in which a date has been defined as a six-digit numeric field in YYMMDD format, you may need to sort on just the YY portion of the date followed by another field in the file. For example, suppose you want to sort on the year portion of a field called ORDDAT followed by a field called ITEM#. In this case, the SQL statement might look like this:

 SELECT INTEGER(ORDDAT/10000), ITEM#, DEPT FROM ITMTRN WHERE DEPT = '02' ORDER BY 1, 2 

In this example, the first field in the SELECT clause is derived by dividing ORDDAT by 10,000. This process moves the decimal point four places to the left, immediately following the year. The INTEGER function eliminates the fractional portion of the field containing the month and day, thereby leaving only the year. The ORDER BY clause sequences the records by the derived year and the ITEM# field.

- Vijay Yadav