johndanter
Legendary Poster
Hi folks,
I have an issues that been bugging me.
I'm meant to be fetching values from a table where a certain field is NULL (SS57STYPE ). So I tried using a variable on alias DCTO and set to '' nothing in ER and then used the following select by using less than or equal the variable (which defaults to blank anyway).
SS57STYPE can sometimes be 'WO' or 'WR' or NULL
SELECT * FROM CRPDTA.F57MESSS WHERE ( SS57SKEY1 = 'SAMPLEPLAN' AND SS57SADD = '158527' AND SS57SBRAN = '728' AND SS57STYPE <= ' ' AND SS57SITEM = '4826096' ) ORDER BY SS57SKEY1 ASC,SS57SADD ASC,SS57SBRAN ASC
No records come back!!!
In the end I solved it with a descending index on SS57STYPE and just got the first record, which happens to be the one I'm after
SELECT * FROM CRPDTA.F57MESSS WHERE ( SS57SKEY1 = 'SAMPLEPLAN' AND SS57SADD = '158527' AND SS57SBRAN = '728' AND SS57STYPE <= ' ' AND SS57SITEM = '4826096' ) ORDER BY SS57SKEY1 ASC,SS57SADD ASC,SS57SBRAN ASC
How do you guys get around this as normally what I've done works ok. Not sure why it isn't here.
Could it be the nature of DCTO maybe? As ABLK is N which means it'll always add a blank
Thanks
John
I have an issues that been bugging me.
I'm meant to be fetching values from a table where a certain field is NULL (SS57STYPE ). So I tried using a variable on alias DCTO and set to '' nothing in ER and then used the following select by using less than or equal the variable (which defaults to blank anyway).
SS57STYPE can sometimes be 'WO' or 'WR' or NULL
SELECT * FROM CRPDTA.F57MESSS WHERE ( SS57SKEY1 = 'SAMPLEPLAN' AND SS57SADD = '158527' AND SS57SBRAN = '728' AND SS57STYPE <= ' ' AND SS57SITEM = '4826096' ) ORDER BY SS57SKEY1 ASC,SS57SADD ASC,SS57SBRAN ASC
No records come back!!!
In the end I solved it with a descending index on SS57STYPE and just got the first record, which happens to be the one I'm after
SELECT * FROM CRPDTA.F57MESSS WHERE ( SS57SKEY1 = 'SAMPLEPLAN' AND SS57SADD = '158527' AND SS57SBRAN = '728' AND SS57STYPE <= ' ' AND SS57SITEM = '4826096' ) ORDER BY SS57SKEY1 ASC,SS57SADD ASC,SS57SBRAN ASC
How do you guys get around this as normally what I've done works ok. Not sure why it isn't here.
Could it be the nature of DCTO maybe? As ABLK is N which means it'll always add a blank
Thanks
John
Last edited: