SQL Server statement error

building semantic layer
1

Hello
I have a field called @Select(Dbo Dyresults\Extjan) its type is number, I am trying to put in the where the following statement

CASE WHEN @Select(Dbo Dyresults\Extdesc) = 'Straight Line Adjustment'  
   THEN @Select(Dbo Dyresults\Extjan) * -1 
   ELSE @Select(Dbo Dyresults\Extdesc) = 'Straight Line Adjustment'  
END

but when I parse I keep receiving the follwoing error :
Parse failed: exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server] Statement could not be prepared.State: 42000

Any ideas, Please Help

thanks,

chmatn1 :canada: (BOB member since 2007-08-02)

2

When you hide the actual SQL using @select() it is very difficult to try to debug via a discussion forum… :wink:

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Sorry I did not understand, All I did is I edited the objects and I inserted this statement in the Where clause !! but I did not hide anything ?

Thanks

chmatn1 :canada: (BOB member since 2007-08-02)

4

You can’t ask someone to debug code when you’re not showing the code. If you open the object, click the edit button, and click the checkbox for showing the full SQL then paste that.

For example, if the value includes an aggregate function (which is impossible to see with what you have posted) you cannot put an aggregate inside of a case statement. But it’s pointless to speculate that might be the case without seeing the SQL that is being prepared after the @select() functions have been processed.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Hello

I went to the object and then edit and a Query panel appeared I then clicked on SQL and here ie the code I see

SELECT DISTINCT
  dbo.DyResults.ExtJan
FROM
  dbo.DyResults
WHERE
( CASE  WHEN ( dbo.DyResults.ExtDesc ) = 'Straight Line Adjustment'  THEN ( dbo.DyResults.ExtJan ) * -1 ELSE ( dbo.DyResults.ExtJan )  END  )

Thanks

[Edited, when posting code samples please use the code option for formatting. It will preserve any indenting or formatting that you may have done. Thank you, Andreas.]

chmatn1 :canada: (BOB member since 2007-08-02)

6

The where clause is not valid. It doesn’t compare anything. You have an expression, but it’s not being compared to anything else. It’s as if you have this:

select x from table where y

and what you need is

select x from table where y=1

… or something along those lines

Dave Rathbun :us: (BOB member since 2002-06-06)