I urgently need to solve this problem. 1

[mary1]

The following is the problem that I am facing to:<br><br>I am tring to retrieve values from the database using the code below. I am using Sybase SQL Server. The problem is some of the fields in the database for AlarmLow, AlarmHigh, VetoLow and VetoHigh are blank (no values). The datatype has been set to varchar in the tables. Is the code below appropriate for what I am tring to do? I have also tried changing the 0 to '',' '<br>I am running this program on PowerBuilder and I always seem to get this error message:<br>Error converting datatype from varchar to numeric.<br><br>SELECT @SQLStatement = @SQLStatement + 'alarmlow, alarmhigh, vetolow, ' +<br>&nbsp;'vetohigh, criteria1value) SELECT ' + RTRIM (CONVERT&nbsp;&nbsp;<br>&nbsp;(CHAR, @Id)) + ', &quot;' +<br>&nbsp;RTRIM (CONVERT (CHAR(30), @DocDateValue)) + '&quot;, &quot;' + <br>&nbsp;RTRIM (CONVERT (CHAR(30), @SampleDateValue)) + '&quot;, ' + <br>&nbsp;RTRIM (CONVERT (CHAR(30), @Reading)) + ', ' + <br>&nbsp;RTRIM (CONVERT (CHAR(30), IsNull (@AlarmLow, 0))) + ', ' + <br>&nbsp;RTRIM (CONVERT (CHAR(30), IsNull (@AlarmHigh, 0))) + ', ' + <br>&nbsp;RTRIM (CONVERT (CHAR(30), IsNull (@VetoLow, 0))) + ', ' + <br>&nbsp;RTRIM (CONVERT (CHAR(30), IsNull (@VetoHigh, 0))) + ', &quot;' + <br>&nbsp;RTRIM (@Criteria1Value) + '&quot;'<br><br>--MyDebug<br>--SELECT SUBSTRING (@SQLStatement, 1, 200)<br>--SELECT SUBSTRING (@SQLStatement, 201, LEN (@SQLStatement))<br><br><br><br><br>

 

[srajesh]

Hi Mary1,<br>This kind of problem occurs when you use isnull() on a column defined as a char and the database contains space for the same.&nbsp;&nbsp;Say we have a column 'b' in table 'test'.&nbsp;&nbsp;If we use the query 'select isnull(b,0) from test' then it will give an output similar to your situation.&nbsp;&nbsp;<br><br>We can handle these type of queries by using the following query. <br>'select convert(char(1),isnull(b,'')) from test'<br><br>Do let me know if this works in your query.<br><br>Thx,<br>S.Rajesh

 

[mary1]

Hi srajesh,<br>Thanks for the reply,<br>I have tried the code that u have written but I still seem to get the error message when executing the program on PowerBuilder.<br>Error: Datatype coversion from varchar to numeric.<br><br>One more thing, in this case b does not always haveto be a null value, there are times when there is a value for b.<br>So, how would you code that?<br><br>Besides, I also tested the codes below:<br>Test1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RTRIM(CONVERT(CHAR(1),ISNULL(b,' ')))<br>Test2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RTRIM(CONVERT(CHAR(1),ISNULL(b,null)))<br><br>For Test1, I get the same error as above, for Test2, a messagebox appears &quot;No data retrieved&quot;. But there is data in the table of the database.<br><br>Any other suggestion??<br>Thanks.

 

[dkvin]

May be it's due to the parameters' datatype.<br>In manual:<br><i>Syntax<br>ISNULL ( expression, expression [ , ... ] )<br>Parameters<br>expression - The numeric expression passed into the function...</i><br><br>So, parameters must be the numeric expression. You need to convert varchar to numeric.