"Select *"
Is the enemy. It is the appearance of Satan on earth. That's your first problem.
Your next problem is that you are comparing a numeric column to a character column:
Select (mathematical result) between 'character1' and 'character2'
We have to make sure that the result field is character. We also have to ensure that it is eight characters. To do so we will explicitly set it's size in DECimal and convert it to character with DIGITS.
To see bite size chunks try this in Run SQL Scripts:
VALUES (19 * 1000000) + (79 * 10000) + (12 * 100) + 31; -- numeric 19791231
VALUES DIGITS((19 * 1000000) + (79 * 10000) + (12 * 100) + 31); -- character '0019791231'
To get rid of the leading zeros:
VALUES DIGITS(DEC((19 * 1000000) + (79 * 10000) + (12 * 100) + 31, 8, 0)); -- character '19791231'
values case WHEN
(DIGITS(DEC((19 * 1000000) + (79 * 10000) + (12 * 100) + 31, 8, 0)) between '19791230' and '19800102')
then 'TRUE'
else 'FALSE'
END; -- returns TRUE
So now that we understand this we can create a view with a calculated column.
create view LIB1/V1 AS ( select
DIGITS(DEC((field1 * 1000000) + (field2 * 10000) + (field3 * 100) + field4, 8, 0)) as CharDate,
-- add other columns here. They can be calculated like above or existing columns
-- you can rename them to something your Query users can understand like
-- IADJ as ADJUSTMNTS
-- or to just add the rest of the columns you can do
-- B.*
-- looking for the B below
From lib1/file1 B;
Then you can do your where either in your view, as you showed in your example. Or do your record selection in your WRKQRY based off the calculated column CharDate.
The big thing here is to start using calculated columns in your VIEW instead of having every Query/400 user have to do the calculations.
create view LIB1/V1 AS ( select
DIGITS(DEC((field1 * 1000000) + (field2 * 10000) + (field3 * 100) + field4, 8, 0)) as CharDate,
-- add other columns here. They can be calculated like above or existing columns
-- you can rename them to something your Query users can understand like
-- IADJ as ADJUSTMNTS
-- or to just add the rest of the columns you can do
-- B.*
-- looking for the B below
From lib1/file1 B;
You could even have that CASE statement as a calculated column:
create view LIB1/V1 AS ( select
DIGITS(DEC((field1 * 1000000) + (field2 * 10000) + (field3 * 100) + field4, 8, 0)) as CharDate,
Char(
case WHEN
(DIGITS(DEC((19 * 1000000) + (79 * 10000) + (12 * 100) + 31, 8, 0)) between '19791230' and '19800102')
then 'TRUE'
else 'FALSE'
END
, 5) as InRange,
B.*
From lib1/file1 B;
We have a software package. They have no on hand balance column. You either have to calculate it in every dang query and program with IOPB + IADJ + IRCT - IISS or create a view which calculates it into a calculated column.
Rob Berendt
midrange.com
