Select statement help - CASE? 1

[bmarks]

I have done this in the past with SQL Server and it works on IB7, but does not work on IB6. Can someone help me with the correct method to do this in IB6?

I have:
select field1,
(case field2 when 1 then 'Text1'
when 2 then 'Text2'
when 3 then 'Text3'
else 'Text4'),
field3
from .......

The case statement does not seem to be supported in IB6. I tried replacing it with an "if" statement, but that does not seem to be supported within the select statement.

Hoping someone can help.
Thanks.

 

[unclejimbob]

It may be beneficial if you post the actual SQL statement.

I can think of three ways to handle this - in no particular order:

1) If its a simple transformation then you can use the concatenation operator or
any of the functions available through the UDF libraries, i.e. LONGSUBSTR() etc
for example if the field contains a value you can then use it as a pointer into
a string and use substr() to retrieve the actual value

i.e. '1stCase.2ndCase.3rdCase'

select
longsubstr('1stCase.2ndCase.3rdCase',1,Field2) from ...

2) Lookup - create a lookup table and add it into your list of joined tables;
if the case statement contains something that you would term significant to the
business then you will be doing this anyway
3) cursor - create a cursor in a for..select loop and handle the transform using IF
statements, this is a bit of overkill and tedious but it will give you the most
control

 

[bmarks]

unclejimbob, thanks for the input. I am posting the actual SQL stmt below. 1 doesn't apply. 2 Might work, I'll have to check with my client if there game for me to add tables into their applications database. 3 Don't know about Interbase, but in the SQL Server world, cursors were considered a bad thing to be avoided if at all possible, but I'll look into it. Case statements used in this way is something that I used extensively in SQL Server and can see many places where I would want to use them now in Interbase, but in v6, I can't so trying to see all the ways to do this without case.

In case it helps, here's the SQL stmt lifted out of the stored procedure (I've changed/masked a couple of items for client confidentiality, but the gist of the question and case stmt remain):

You will notice that there are two types of case stmts here. The first takes a code and turns it into a descriptive text string, the second returns either a 0 or the value into the field depending on the date of the transation - in order to put the value into "aging buckets" for accounting aged receivable report.

begin
for select
'Invoice',
p.policy_no,
p.accounting_date,
case p.status when 'O' then 'Original'
when 'X' then 'Cancel'
when 'RX' then 'Cancel'
when 'C' then 'Mid Term Change'
when 'RC' then 'Mid Term Change'
when 'R' then 'Renew'
when 'W' then 'Rewrite'
when 'Q' then 'Quote'
when 'I' then 'Cancel'
when 'RI' then 'Cancel'
else 'Adjustment' end as Status_Text,

case when b.invoice_date <= :AsOfDate and b.invoice_date >= :AsOfDate-30 then b.amount end as Current_Total,

case when b.invoice_date < :AsOfDate-30 and b.invoice_date >= :AsOfDate-60 then b.amount end as A31to60_Total,

case when b.invoice_date < :AsOfDate-60 and b.invoice_date >= :AsOfDate-90 then b.amount end as A61to90_Total,

case when b.invoice_date < :AsOfDate-90 then b.amount end as A91plus_Total

from billing b
left outer join policy p on b.id = p.id

where .....

into :ttype, olicy_No, :Acctg_Date, :Status_Text,
:Current_Total, :A31to60_Total, :A60to90Total, :A91Plus_Total

do
suspend;

end

 

[unclejimbob]

Don't know about Interbase, but in the SQL Server world, cursors were considered a bad thing to be avoided if at all possible, but I'll look into it."
I think you will find that the set up and usage of cursors in InterBase is simpler and less costly than you are used to with SQL Server.

"Case statements used in this way is something that I used extensively in SQL Server and can see many places
where I would want to use them now in Interbase, but in v6, I can't so trying to see all the ways to do this without case."
FWIW I have used IB since 1996 and have never needed to use case statements...instead I make it a rule
that anything of business interest requires that is be recorded as a lookup mainly due to the fact that it is likely to be used in more than one place anyway and cursors and if statements take care of the rest. This means that I have 100 lookup tables - but it also means that later on when creating things like data marts is a heck of a lot easier as each lookup table is fully documented.

Swings and roundabouts really, the major things I found a pain when moving to SQL Server were:
1) the drama you have to go through in order to do something as simple as updating a related table when one of the column values changes - instead of just using the 'old' and 'new' variables in IB to pick up the changes in the table you carry out a select using 'inserted' and 'deleted' tables
2) having to explicitly define indexes for FK constraints

"the second returns either a 0 or the value into the field depending on the date of the transation - in order to put the value into "aging buckets" for accounting aged receivable report."
I have exactly the same scenario - except that I have a specific aged debt table which I populate via a stored proc - mainly because I have to handle the whole gamut of financial transactions in order to determine the true financial aged debt position of each debtor, i.e. pro forma invoicing, future payments, credit notes, payments, refunds and where applicable reversals of one or more of the above. All good stuff.

I see that you are putting the results of the select statement into variables anyway so its no great hardship
to put p.status directly into :Status_Text and then add if statements after...

Untested pseudo-code follows...

DECLARE VARIABLE sopStatusText VARCHAR(20);
DECLARE VARIABLE dopInvoiceDate DATETIME;
DECLARE VARIABLE dopInvoiceAmount NUMERIC(9,2);

begin
for
select
'Invoice',
p.policy_no,
...
p.status,
b.invoice_date,
b.amount
into
:ttype, olicy_No,
...
:Status_Text,
:dopInvoiceDate,
:dopInvoiceAmount

do
BEGIN
IF Status_Text = 'O') then
sopStatusText = 'Original';
ELSE IF Status_Text = 'X') THEN
sopStatusText = 'Cancel';
...
ELSE
sopStatusText = 'Adjustment'

IF (dopInvoiceDate <= :AsOfDate) and (dopInvoiceDate >= :AsOfDate-30)) then
Current_Total = dopInvoiceAmount;

suspend;

end

Note that I haven't added any fall through code for any of the output parameters that you would normally do
such as testing to see if the value of Current_Total is null and perhaps setting it to zero, the above is
just an indication of how you would go about it using a cursor.

Good luck.

 

[unclejimbob]

Sorry, just realised that when I say using a 'cursor' I don't really mean DECLARE CURSOR which is available under DSQL, I mean the use of the following IB language extension to the SELECT statement:

FOR select_statement
DO compound_statement

which is what you already have; so you only need to modify your existng code slightly to include the conditions in the compound_statement block rather than have them in the select_statement block.

 

[bmarks]

unclejimbob,

Thanks for all the comments, they are very valuable. My major block was that after learning how to do things the MS SQL way was to get my head around a new way of doing it. I now see how the "compound_statement" block works and have managed to get things working. I still find that I would rater use case statements, but can now get around it. It just seems simpler to me to put it all in the select_statement block with a case and take care of everything at once. But I'll get over that as I find other benefits to doing it this way.

You are right to make the distinction between DECLARE CURSOR and the IB language extension. I think that was one of the areas that was confusing me.

I agree with you about lookup tables, but there are times when you just can't add tables (someone else's design or applicaiton) or things like aging bucket determination.

I guess part of it is I'm not used to putting the results of a select into variables. Before, I would have just done a select (not a for select) with case stmts and that would be the end of it - all done in one statement. In some ways, I find that to be more elegant, but there is definitely some advantages this way as well.

I think I've got it now and your help has made it possible. Thanks for all your help!!