iif to DECODE

[Chance1234]

i am having a nightmare with the following

i have this iif statement in a Access database

IIf([NOOFPROPERTIES]<=1,IIf(IsNull([C]),IIf([A]=, & &quot; &quot; & [D],), & &quot; &quot; & [C]),IIf([A]=, & &quot; &quot; & [D],)) AS [Desc]

which im trying to convert over onto a oracle database and cant seem to get the syntax right using DECODE,

any help appreciated.



Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM

 

[SantaMufasa]

Chance,

I'm certain we can &quot;Oracle&quot; your logic for you, but first, I need your help: I'm not an Access aficionado, so could you provide a non-syntactical functional narrative of what you want Oracle to evaluate?

Example: Look at NoOfProperties...if it is less than or equal to 1, then (et cetera).

Dave
Sandy, Utah, USA @ 17:02 GMT, 10:02 Mountain Time

 

[Chance1234]

just realised its used the B value as a bold tag ok, ive taken out the brackets in the below

IIf(NOOFPROPERTIES<=1,IIf(IsNull(C),IIf(A=B,B & &quot; &quot; & D,B),B & &quot; &quot; & C),IIf(A=B,B & &quot; &quot; & D,B)) AS Desc


If Number of Propertys less than or equal to 1

and C is nothing and A = B Then the result = B space and D

IF A doesnt = B then the result is B

If C is not null then the result = B space and C

If Number of propertys is greather than 1 then the result = B space D


ive been kind of going down this road with the decode.

decode([NOOFPROPERTIES]<=1,decode(null([C]),decode([A]=, || ' ' || [D],), || ' ' || [C]),decode([A]=, || ' ' || [D],)) AS [Desc]


Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM

 

[SantaMufasa]

Chance,

I believe this is the equivalent code (broken up onto multiple lines for readability):

... decode	(sign(NoOfProperties-1)
		,1,decode(A,B,B||' '||D,B)	-- Result WHEN NoOfProperties > 1
		,decode	(C			-- WHEN NoOfProperties <= 1
			,Null,decode(A,B,B||' '||D,B) -- WHEN C is NULL, check A=B
			,B||' '||C  )		-- WHEN C is NOT NULL
			)
		) as Desc
...

To compare &quot;NoOfProperties&quot; to 1, I subtracted &quot;1&quot; from NoOfProperties and checked the SIGN of the result (-1 if result is negative, 0 if result is zero, 1 if result is positive).

Keep in mind that since Oracle 8i, you can also use the CASE...WHEN construct in Oracle SQL. But in this situation, DECODE works just fine.

Let me know if this is what you wanted.

Dave
Sandy, Utah, USA @ 18:08 GMT, 11:08 Mountain Time

 

[SantaMufasa]

Also, Chance, change the &quot;AS Desc&quot; to &quot;AS Descr&quot; since &quot;Desc&quot; is a reserved word in Oracle SQL, used in &quot;ORDER BY expr DESC&quot; for &quot;descending&quot;.

Dave
Sandy, Utah, USA @ 18:12 GMT, 11:12 Mountain Time

 

[Chance1234]

cant seem to get the sql to work, have posted the whole query below,


CURRENT SQL trying to get to work
********************

SELECT DISTINCT
Q32SERV.FILESOPENED.FILENO, Q32SERV.REF_FOPN_JOBTYPE.NAME AS A, Q32SERV.FILESOPENED.FILECONTENTS AS B,
Q32SERV.FILESOPENED_PROP.FULLADDRESS AS C, Q32SERV.ORGANISATION.NAME AS D, Q32SERV.FILESOPENED.NOOFPROPERTIES,
Q32SERV.FILESOPENED.DEPARTMENT, Q32SERV.FILESOPENED.PARTNER, DECODE(sign(NOOFPROPERTIES - 1), 1, DECODE(A, B, B || ' ' || D, B)
NOOFPROPERTIESs > 1, DECODE(NOOFPROPERTIES <= 1, NULL, DECODE(A, B, B || ' ' || D, B), A = B, B || ' ' || C)) AS Descr
FROM Q32SERV.FILESOPENED, Q32SERV.ORGANISATION, Q32SERV.REF_FOPN_JOBTYPE, Q32SERV.FILESOPENED_PROP
WHERE Q32SERV.FILESOPENED.ORGID = Q32SERV.ORGANISATION.ORGID AND
Q32SERV.FILESOPENED.JOBTYPE = Q32SERV.REF_FOPN_JOBTYPE.CODE (+) AND
Q32SERV.FILESOPENED.UNIQUEID = Q32SERV.FILESOPENED_PROP.UNIQUEID (+)



OLD ACCESS SQL
***************

SELECT Q32SERV_FILESOPENED.FILENO, Q32SERV_REF_FOPN_JOBTYPE.NAME AS A, Q32SERV_FILESOPENED.FILECONTENTS AS Bx, Q32SERV_FILESOPENED_PROP.FULLADDRESS AS C, Q32SERV_ORGANISATION.NAME AS D, IIf([NOOFPROPERTIES]<=1,IIf(IsNull([C]),IIf([A]=[Bix],[Bx] & &quot; &quot; & [D],[Bx]),[Bx] & &quot; &quot; & [C]),IIf([A]=[Bx],[Bx] & &quot; &quot; & [D],[Bx])) AS [Desc]
FROM ((Q32SERV_REF_FOPN_JOBTYPE RIGHT JOIN Q32SERV_FILESOPENED ON Q32SERV_REF_FOPN_JOBTYPE.CODE = Q32SERV_FILESOPENED.JOBTYPE) LEFT JOIN Q32SERV_FILESOPENED_PROP ON Q32SERV_FILESOPENED.UNIQUEID = Q32SERV_FILESOPENED_PROP.UNIQUEID) LEFT JOIN Q32SERV_ORGANISATION ON Q32SERV_FILESOPENED.ORGID = Q32SERV_ORGANISATION.ORGID
ORDER BY Q32SERV_FILESOPENED.FILENO;


Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM

 

[SantaMufasa]

Chance,

Posting the entire query is more helpful, but the most helpful addition would be a more full understanding of what you mean by &quot;cant seem to get the sql to work...&quot;. We need some evidence of why it's not working for you. Please include the error message(s) if you are receiving a syntax or run-time error, or a description of why the results do not meet your expectations if you are encountering a logic error.

Cheers,

Dave
Sandy, Utah, USA @ 17:29 GMT, 10:29 Mountain Time

 

[Chance1234]

HI i got it to work in the end, thanks for all your help,

Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM