Query for iSeries was designed with multiple member files in mind and will allow you to specify the member name. However, SQL will not allow you to access a specific database member directly. When you run SQL on an iSeries file, it will always use the first member in the file. Let’s assume we have […]

Number of rows EXCLUDING deleted – three methods 1. The SQL COUNT function > STRSQL > SELECT COUNT(*) FROM cities ….+….1…. COUNT ( * ) 6 ******** End of data ******** 2. The display file description command > DSPFD cities Go to the bottom of the information and look for the line “Total records” Total number of members […]

Query/400 provides many options for converting data, particularly dates, but lacks one important feature – converting character to numeric. However, there is a workaround even if it is a little “awkward” by using the MICROSECONDS function. Firstly, append your character field to the end of an arbitrary date. Next, use the MICROSECONDS function to extract it as […]

Many legacy systems stores dates in 6 digit numeric fields. This makes comparison difficult during a Query’s select statements. However, there is a way to handel these dates in Query/400.  Firstly, convert the numeric date field to a character field and formatting it with separators. Then it can be converted to a date field using the DATE function in query. […]