"if then else" in Interbase

[jonshandle]

Hi - stupid/spimple question but whats the equivalent of an oracle decode function in interbase? Is there a case statement? Or perhaps, if then else? I can find absolutely sod all resouces on interbase syntax on the net or the IB help. Can you also point me to any useful synatax links that I dont know about? Thanks in advance...Jon

 

[unclejimbob]

I have no idea what the oracle decode function does but an example of the syntax for an if-else (using local vars) is as follows:

BEGIN
IF ((a + 1 - b) < 30) THEN
c = c + d;
ELSE IF ((a + 1 - b) < 60) THEN
e = e + d;
ELSE IF ((a + 1 - b) < 90) THEN
f = f+ d;
ELSE
g= g+ d;
END

IB 7 has a CASE statement so it depends on which version of IB you are using as to which way you go. The answer to the question about resources is covered in the recent thread 'Reference Book?'; basically you have the IB language reference book and some web sites such as tamaracka and dunstan thomas.

good luck

 

[jonshandle]

Thanks unclejimbob...but I was referring to an 'if then else' within a select statement - i.e. a case equivalent but using IB6. It has to be within a select statement, not a stored procedure. This is certainly embarassing stuff to be discussing publicly but the truth is...I can't find a way to do this utterly fundamental operation! It wouldn't surprise me if you can't do it - and IB6 forces you to write a stored procedure. But that would be ridiculous. FYI - Oracle decode is like an if then else, i.e.

SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;

is equivalent to:

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;

Ill check out the references (appreciated for those) - but if there's a simple resolution to this, i'd be grateful for a reply. Many thanks...Jon

 

[svanels]

Would a look-up table not be handier? If you got 3000 suppliers, that would be a lot of coding.

Steven

 

[jonshandle]

Actually - that was a daft cut n' paste example from the web. The oracle query to be translated into IB6 is:

select c.carname, c.carid,
decode(
carid,
(select carid from customercar where carid = c.carid and custid = 1),
'Y',
'N'
) as preference
from car c

So - without using lookup tables, i'm guessing there isn't an equivalent function...?

Thanks...J

 

[unclejimbob]

I have an idea of how this could be done, if you would like to post schema/structure and example data for the two tables I can test it out here before bothering you with it

 

[bmarks]

I am interested in hearing your thoughts unclejimbob. Can you post them even if you don't get jonshandle's schema?

jonshandle - I understand your confusion. Having come from a different environment (and using case statements in the SQL commands extensively), it takes some getting used to.
I was able to overcome my needs by using a stored procedure, but I can see (and have some potential clients) where I would need to do this in the SQL statement (since I would not have the access/ability to change the client's database to add either a sproc or a lookup table, and the statement would be done from something like Crystal reports).

 

[jonshandle]

BMarks - doesnt seem like theres an obvious solution in IB6. I needed to create a boolean column as part of the query and make it true if a select brings back 'x' data and false otherwise. I did this indirectly by using a union instead of an if statement. I.e. bring back all the true results union bring back all the false results. Not the best way of solving the problem - but it works....

 

[unclejimbob]

bmarks - I was thinking about using either the standard UDFs available with InterBase or more usefully the rfunc library - specifically starting with something like the LongConvertSymbols function.

If the language doesn't support a feature then one option would be to use a DLL that does - unless of course your OS doesn't support it - and this may very well be the case.

jonshandle - Feel free to post the schema/data. If this is not possible/desirable then I wish you good luck with your search for a solution; unfortunately my analysis skills are not sufficient to continue without it.

 

[jonshandle]

Unclejimjob - thanks very much but I've sorted it using the solution in the older thread.....