Select statements IIF / calculated fields 1

[mostevil]

I'm trying to create an SQL statement to pull in some values from an accounts package. The problem is that values are all stored as positive values but need to be negative for certain transaction types.

eg.
when [TRANTYPE] = "CRN", value = [GROSS] * -1
when [TRANTYPE] = "INV", value = [GROSS]

But my attempts at using IIF's have failed..

I'm feeling a little dumb on this one, can anyone help me?
Thanks in advance.

 

[TorF]

You need something like :

iif( trantype ='CRN', GROSS * (-1), GROSS ) ???

 

[mostevil]

Thats the sort of thing I'm after only I get:

Line 1: Incorrect syntax near '='.

from:

SELECT IIF(DET_TYPE ='INV', DET_GROSS, DET_GROSS) AS VALUE FROM SL_PL_NL_DETAIL

I've only seen examples where ISNULL(..) is used in the iif.
I feel like I'm being really stupid here. My SQL knowledge is a bit weak.

 

[Discord]

would
SELECT CASE
WHEN [TRANTYPE] = 'CRN' THEN [GROSS] * -1
ELSE [GROSS]
END AS VALUE
FROM SL_PL_NL_DETAIL
work?

 

[mostevil]

You've just become my personal hero. Thanks, thats just what I was looking for.

 

[kushi]

I am also having the problem like that.
Del Rank: IIf([Rank Del] Is Null,5,[Rank Del])

[Rank Del] is number(10,2) in oracle and double in access.
I am using oracle as linked tables for access appln.

Please let me know if you have any idea.

kushi

 

[kushi]

SELECT
[qry-Rank Supplier Name].[Supplier Name],
IIf([Rank Del] Is Null,5,[Rank Del]) AS [Del Rank],
FROM [qry-Rank Supplier Name].[Supplier Name] = [qry-Rank Delivery].Supplier)

 

[kushi]

SELECT [qry-Rank Supplier Name].[Supplier Name], IIf([Rank Del] Is Null,5,[Rank Del]) AS [Del Rank], IIf([Rank PPAP] Is Null,5,[Rank PPAP]) AS [PPAP Rank], IIf([Rank PPM] Is Null,5,[Rank PPM]) AS [PPM Rank], IIf([Rank QFN] Is Null,5,[Rank QFN]) AS [QFN Rank], ((Val([Del Rank])*0.25)+(Val([PPAP Rank])*0.2)+(Val([PPM Rank])*0.35)+(Val([QFN Rank])*0.2))/5 AS Rank
FROM ((([qry-Rank Supplier Name] LEFT JOIN [qry-Rank QFN] ON [qry-Rank Supplier Name].[Supplier Name] = [qry-Rank QFN].[Supplier Name]) LEFT JOIN [qry-Rank Delivery] ON [qry-Rank Supplier Name].[Supplier Name] = [qry-Rank Delivery].Supplier) LEFT JOIN [qry-Rank PPAP] ON [qry-Rank Supplier Name].[Supplier Name] = [qry-Rank PPAP].[Supplier Name]) LEFT JOIN [qry-Rank PPM2] ON [qry-Rank Supplier Name].[Supplier Name] = [qry-Rank PPM2].[Supplier Name];

 

[kushi]

I am also having the problem like that.
Del Rank: IIf([Rank Del] Is Null,5,[Rank Del])

[Rank Del] is number(10,2) in oracle and double in access.
I am using oracle as linked tables for access appln.

I am getting "Invalid Operation" Error mesg.

Help appreciated

kushi

 

[angiole]

IIF is not supported in Oracle, but v.8.1+ now includes the CASE statement, as follws:

SELECT (CASE WHEN F1 = 'X' THEN Y+1 ELSE Y-1 END)
FROM ...