0

I need to run a T-SQL query via the Microsoft Query interface, using an IIf statement.

When I run the query, I get the error Missing FROM clause. This is odd, because the FROM clause is there. It fails because of the IIf but I have no idea why.

The code:

SELECT 
    TABLE1.NUMBER, 
    TABLE1.TEXT, 
    TABLE1.INT, 
    IIf(
        (
            (
                ((TABLE2.INT1-TABLE2.INT2) -TABLE2.INT3) -TABLE2.INT4
            ) > 0
        ), 
        (
            1
        ),  0) 
        as NEWCOL
FROM 
    DB.TABLEX TABLE1, 
    DB.TABLEY TABLE2
WHERE 
    TABLE2.NUMBER = TABLE1.NUMBER 
    AND TABLE1.NUMBER='991208000192'

The database is not mine, so I have to work with what I can. Does anyone have an idea what may cause the problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Deep Frozen
  • 2,053
  • 2
  • 25
  • 42
  • what rdbms are you using? I believe `IIf` is only available in SQL Server 2012, and it is also an MS-Access function. – Taryn Dec 03 '12 at 10:06
  • @bluefeet I am not sure what rdbms it is using since I have really, really limited access. I have tried `CASE`, but then I found out that the server does not use that statement. – Deep Frozen Dec 03 '12 at 10:09
  • You have so many redundant parenthesis that this `IIf` statement is not very clear. Maybe it generates this syntax error? – psur Dec 03 '12 at 10:10
  • You are going to need to determine what database you have. That will then allow you to use the correct functions. – Taryn Dec 03 '12 at 10:10
  • @bluefeet I have made a call and they told me it is SQLBase, but they didn't know the version.. Is that of any help? If the `IIf` and `Case` functions can't be used, how can I do this query then? – Deep Frozen Dec 03 '12 at 10:17
  • Try using an IF .. ELSE statement instead of IIF, (According to this): http://enos.itcollege.ee/~priit/(I216)%20Ab-de%20administreerimine/SQLBase%20admin%20juhendid/LANG.PDF – twoleggedhorse Dec 03 '12 at 15:03
  • @twoleggedhorse I've tried this, but Microsoft Query returns `Invalid Column Expression` – Deep Frozen Dec 03 '12 at 15:22

1 Answers1

0

the SQLBase reference in http://enos.itcollege.ee/~priit/(I216)%20Ab-de%20administreerimine/SQLBase%20admin%20juhendid/LANG.PDF (Courtesy @twoleggedhorse) does not have function IIF in SQLBase. It has a function IF with similar syntax as your IIF.

Try using @IF(CONDITION, TRUE_VAL, FALSE_VAL) in your SQL.

Aishu
  • 413
  • 1
  • 5
  • 20