ERROR in Assigment 1

[JRCharlie]

HI
I have a Pervasive database with a table that one of the columns is a masked textbox

.
I'm trying to retreave the information on the mask cell using a SUBSTING Function.
It seams to work fine when just using the substring to return the values (column a,b,c,d) but I get the error in assigment when applying an IF Function with the substring in Pervasive And ERROR In Row In VB.NET.
The table you see in the attachment is data retrived using the substring alone (the first two substrings in my query.
Can you help me with this Query.

SELECT
  limsk.MASK AS p22msk,
 SUBSTRING(limsk.MASK, 41, 2)as a,SUBSTRING(limsk.MASK, 41, 4) AS b,
  SUBSTRING(limsk.MASK, 58, 2) as c,SUBSTRING(limsk.MASK, 58, 4) AS  D,
if (SUBSTRING(limsk.MASK, 41, 2)='__' , 0, SUBSTRING(limsk.MASK, 41, 4) )AS P22xtra,
if (SUBSTRING(limsk.MASK, 58, 2)='__' ,0, SUBSTRING(limsk.MASK, 58, 4) ) As P21Xtra
FROM
  CWLIVEWB.OELI_WB_MASK AS limsk
WHERE
  limsk.FIELD_NAME = 'P22Extra' AND
  limsk.OELI_ORDER_NO LIKE '%0000707956'

Thanks in Advance.

 

[mirtheil]

THe problem is in your IF statement. You can't have the True part be an integer and the False part be a string. You should be able to change the query to:

SELECT
  limsk.MASK AS p22msk,
 SUBSTRING(limsk.MASK, 41, 2)as a,SUBSTRING(limsk.MASK, 41, 4) AS b,
  SUBSTRING(limsk.MASK, 58, 2) as c,SUBSTRING(limsk.MASK, 58, 4) AS  D,
if (SUBSTRING(limsk.MASK, 41, 2)='__' , '0', SUBSTRING(limsk.MASK, 41, 4) )AS P22xtra,
if (SUBSTRING(limsk.MASK, 58, 2)='__' ,'0', SUBSTRING(limsk.MASK, 58, 4) ) As P21Xtra
FROM
  CWLIVEWB.OELI_WB_MASK AS limsk
WHERE
  limsk.FIELD_NAME = 'P22Extra' AND
  limsk.OELI_ORDER_NO LIKE '%0000707956'

Which makes the "True" part being a string with 0 in it.

Mirtheil

http://www.mirtheil.com

 

[JRCharlie]

Thanks Mirtheil for the help