sql statement error

What error are you getting?

[Sybase][ODBC Driver][SQL Anywhere]Syntax error near '=' on line 1

I appreciate your attempt. No luck even with a different field name.  Points for effort (and you were the only reply :).
Mark

I know several months have passed so this may no longer be an issue.  Out of curiosity did you have any reason for not wanting to use a case statement?  Also, the use of characteristic function may be an option.  Again, this may no longer be an issue at this time.  Either way, let us know.  We may be able to revisit this one for you.  Finally, could you try using single quotes rather than double.  This may be a silly request but I see it cause grief from time to time for the programming languages that I use.  At worst, it shouldn't cost you more than a minute or 2 doing a find/replace on the select.

Note:  I reformatted this for myself.  People may not like the look but just I worked better for me

SELECT
       administrator_vhstock.[NO],
       Val(RIGHT([br], 1))        AS BR2,
       Iif(administrator_vhstock.usage = "d", "d",
          Iif(administrator_vhstock.usage = "l", "l",
              Iif(administrator_vhstock.usage = "g", "s",
                   Iif(administrator_vhstock.usage = "o", "h",
                   Iif(administrator_vhstock.rental_status = "r", "r",
                      Iif(administrator_vhstock.salesdate IS NOT NULL, "v",
                         Iif(administrator_vhstock.stock_status = "p", "o", "i"))))))) AS STAT
FROM   administrator.vhstock
WHERE  no = varstockno

Let me try that. I believe the issue was that it was a Sybase database and there was a syntax difference. I never did get it resolved. I found a sloppy work around so let me give this a try. Thanks - I will reply back my results.

Same error
[Sybase][ODBC Driver][SQL Anywhere]Syntax error near '=' on line 1
I think it is a Sybase syntax issue.

What happens if you remove the WHERE no = varstockno?  It clearly won't return the right data but does it still generate the syntax error?

It looks like you are using SQL Anywhere (not ASE or other Sybase product)?  Are you sure iif is supported by your SQL Anywhere (I know some Sybase products don't support iif but I'd expect a different error if that was the problem).

same error though different line
[Sybase][ODBC Driver][SQL Anywhere]Syntax error near '=' on line 4

What about rewriting it as a case statement?

SELECT 
       administrator_vhstock.[NO],
       Val(RIGHT([br], 1))        AS BR2,
       case when administrator_vhstock.usage = "d" then "d" 
                when administrator_vhstock.usage = "l" then "l"
                when administrator_vhstock.usage = "g" then "s"
                when administrator_vhstock.usage = "o" then "h"
                when administrator_vhstock.rental_status = "r" then "r"
                when administrator_vhstock.salesdate IS NOT NULL then "v"
                when administrator_vhstock.stock_status = "p" then "o"
                else "i" end AS STAT
FROM   administrator.vhstock
WHERE  no = varstockno

Select allOpen in new window

made a slight change (swapped . for -) as needed.
I did get a different error this time.
[Sybase][ODBC Driver][SQL Anywhere]Column 'd' not found

SELECT
case when administrator.vhstock.usage = "d" then "d"
                when administrator.vhstock.usage = "l" then "l"
                when administrator.vhstock.usage = "g" then "s"
                when administrator.vhstock.usage = "o" then "h"
                when administrator.vhstock.usage = "r" then "r"
                when administrator.vhstock.usage IS NOT NULL then "v"
                when administrator.vhstock.usage = "p" then "o"
                else "i" end AS STAT
FROM   administrator.vhstock
WHERE NO = varStockNo
error:

for simplicity I'm working with this statement

SELECT
case when administrator.vhstock.usage = "d" then "d"
                when administrator.vhstock.usage = "l" then "l"
                else "i" end AS STAT
FROM   administrator.vhstock
WHERE NO = varStockNo

I think we are getting somewhere - another character change and I got good results. Swapped the quotes for single quotes.  Now I will work to reintroduce the full statement.

SELECT
case when administrator.vhstock.usage = 'd' then 'd'
                when administrator.vhstock.usage = 'l' then 'l'
                else 'i' end AS STAT
FROM   administrator.vhstock
WHERE NO = varStockNo

Odd.  It's treating one of the "d"s as a column name rather than a string.  For testing, I'd suggest changing it to:

SELECT 
case when administrator.vhstock.usage = "d" then "e" 
                when administrator.vhstock.usage = "l" then "m"
                else "i" end AS STAT
FROM   administrator.vhstock
WHERE NO = varStockNo

Select allOpen in new window

Another minor tweak would be to leave the administrator.vhstock off the select columns (it shouldn't matter but this seems to be behaving oddly).

so far, this is working - little more tweaking - thank you for the direction and push !!
I'm thinking there is different syntax based on version of Sybase.

SELECT
case when administrator.vhstock.usage = 'd' then 'd'
                when administrator.vhstock.usage = 'l' then 'l'
               when administrator.vhstock.usage = 'g' then 's'
                when administrator.vhstock.usage = 'o' then 'h'
                when administrator.vhstock.usage = 'r' then 'r'
                when administrator.vhstock.usage = 'p' then 'o'  
              else 'i' end AS STAT
FROM   administrator.vhstock
WHERE NO = varStockNo

I think we've got it - just doing extended testing:

SELECT Administrator.VhStock.[NO] ,(Right([BR],1)) as Branch,  
case when administrator.vhstock.usage = 'D' then 'd'                    
when administrator.vhstock.usage = 'L' then 'L'                    
when administrator.vhstock.usage = 'G' then 'S'                    
when administrator.vhstock.usage = 'O' then 'H'                    
when administrator.vhstock.usage = 'R' then 'R'                    
when administrator.vhstock.usage = 'P' then 'O'                    
when administrator.vhstock.SALESDATE IS NOT NULL then 'v'  else 'I' end AS STAT
FROM administrator.vhstock
WHERE NO = varStockNo

Glad you got it working.

I opened a separate question to document the final answer. Do you want to post your answer for credit ?  question title is sql statement error sybase