SOLVED Using CASE WHEN ?

[pjwalkerpj]

I have a Base 3 database that I created to provide an invoice for my wife’s Reading Clinic.
It is based on billing per session. Sessions are either 60 minutes, 45 minutes or 30 minutes long and attract fees of $50, $40 and $30 dollars respectively. At present these amounts are entered manually through a form program (chosen from a list), but it is a constant source of error (my own finger problem) and I would like to automate this part of the process.
As part of the invoice I generate the time of each session from Start time and Finish time using DATEDIFF which gives me the time in minutes.
I would now like to use CASE WHEN to take the generated time and return the appropriate amount and put it in the Cost field for each newly entered session.

According to what I read from Hsqldb User Guide, Chapter 9, SQL Syntax something like this should work when included in the SQL statement for the Invoice Query.

CASE WHEN DATEDIFF( ‘minute’, “StartTime”, “FinishTime”) = 60 THEN "Cost" = 50 WHEN DATEDIFF( ‘minute’, “StartTime”, “FinishTime”) = 45 THEN "Cost" = 40 WHEN DATEDIFF( ‘minute’, “StartTime”, “FinishTime”) = 30 THEN "Cost" = 30 END

Everything I have tried gives Syntax errors of some kind so obviously I have a large gap in my knowledge of SQL.

The SQL query statement is

SELECT "ParentDetails"."Salutation", "ParentDetails"."First Initial", "ParentDetails"."FirstName", "ParentDetails"."Name", "ParentDetails"."Address", "ParentDetails"."Suburb", "ParentDetails"."State", "ParentDetails"."PostCode", "SessionDetails"."Date", "SessionDetails"."Session ID", "SessionDetails"."Client", "SessionDetails"."StartTime", "SessionDetails"."FinishTime", DATEDIFF( 'MINUTE', "StartTime", "FinishTime" ) AS "Length", "SessionDetails"."Cost", "SessionDetails"."PaidDate" AS "Date Paid", "SessionDetails"."AmountPaid" AS "Amount", "SessionDetails"."Receipt" AS "ReceiptNumber", "Cost" - "AmountPaid", "SessionDetails"."Description" FROM "SessionDetails" AS "SessionDetails", "ParentDetails" AS "ParentDetails" WHERE "SessionDetails"."Name" = "ParentDetails"."Name" AND "SessionDetails"."Date" BETWEEN {D '2009-07-01' } AND {D '2009-11-30' } AND "SessionDetails"."Session ID" <> 28 AND "ParentDetails"."CurrentStatus" = 'Active' AND "SessionDetails"."Session ID" <> 123 AND "SessionDetails"."Session ID" <> 52 ORDER BY "SessionDetails"."Date" ASC, "SessionDetails"."StartTime" ASC

It operates on two tables, ParentDetails and SessionDetails.
I would GREATLY appreciate any advice that could put these two statements together for a successful outcome.

Regards
Pete

[RPG]

Hello


Maybe there is nothing wrong with your code but it is not implement.
I think your code can be runned in native mode.
When you use only the casewhen that is without a space then it can be runned in OOo-base mode.

http://www.oooforum.org/forum/viewtopic.phtml?t=88094&highlight=casewhen

http://www.oooforum.org/forum/viewtopic.phtml?t=85574&highlight=casewhen


Native or direct mode is when the query go direct to the database engine.

Romke

[pjwalkerpj]

Thanks Romke,
Your pointers to sliderule's explanations are most helpful and I will now spend some time implementing the various permutations and combinations that result from his suggestions.
I have tried running the code in Native and OoBase modes. They both produced Syntax errors, Native returned less errors.
Your suggestion that CASE WHEN may not be implemented when it is defined in the HSQLDB User Manual is somewhat strange. Oops, I don't mean your suggestion is strange, I mean the possibility that an HSQLDB defined facility may not have been implemented is strange. Maybe the new owners of Sun Microsystems can introduce a much needed improvement in Office's system development procedures. It would certainly make my (and your) task much easier.
Anyhow I will let you know how I go with this new found information.
Again thanks for your help
Peter J Walker FIEAust, CPEng
Practising Professional Engineer

[RPG]

Hello

About not implementing Case when
All the instruction you see in the HSQLDB manunal are correct implemented in HSQLDB (maybe some errors but I don't know)
Though they are correct implemented in HSQLDB that means not you can excute them with OOo-base in the OOo-base mode. There are some things not implemented.
When you are in direct mode then OOo-base is not looking to the code and give the code to the engine in your cas HSQLDB.


Which commands OOo base can execute differ from version to version and most of the time it is going better.
OOo base does translate there own instruction to the engine which is installed on your computer.

You are a professional and maybe HSQLDB is not the good engine for you, but that is outside my knowledge.

I hope this is clear. It is difficult for me to write it up. English is not my own language and I'm a home user.
For better explanation read post of Sliderule, Villeroy
and see the links.

http://documentation.openoffice.org/
http://documentation.openoffice.org/servlets/ProjectDocumentList?folderID=778
Romke

[pjwalkerpj]

Hi Romke,
Thanks again for your comments. I should have told you that I knew about using direct mode and in fact I have entered the code directly in the SQL page and pressed the green ticked SQL button.
The fact that the CASE WHEN instruction, and its variations, are defined by HSQLDB leads me to believe that it should be possible to do what I want to do. Sliderule's answers also suggest this, but he is addressing slightly different requirements than the ones I have.
I can actually replicate his solutions and get them to run so I am afraid that it is my knowledge of SQL that is lacking and I do not know all of the correct parsing rules to make the appropriate changes.
I think your suggestion of getting sliderule to have a look at my problem is a good one and to this end I have created a cut down version of my data base that may make it easier to follow what I want to do. So my next question is how can I communicate directly with Sliderule? Just posing a question and hoping he will see and answer is like entering a lottery I think.
Anyhow I apprieciate the time you have taken to help me.
Best regards
Peter J Walker FIEAust CPEng (retd)
Practising Professional Engineer

[RPG]

Hello

You can send Sliderule a PM. But he read always this forum and he like good questions.
And for your information more
see
http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures#Date_and_Time_Functions

and maybe special for tochar
Sliderule did explain that even too on this forum

http://www.oooforum.org/forum/viewtopic.phtml?t=88012&highlight=tochar

I did not look to the complete instruction, there I saw case when and think that was the problem but Sliderule is better with SQL. On this moment I do not have time but when you not have an answer I will better look to the SQL.

Romke



Romke

[sliderule]

Pete:

Just so you understand, I tried not to comment, but, could not help myself to attempt to help you ( and perhaps others in the forum ) in my response to your original question.

I suspect, you are 'almost' at the desired result, BUT, I do want to 'point out' a few important concerns.

1. You could use a CASEWHEN HSQL function ( one word with NO space between the letters ) . . . if is the same as an IF statement in a spreadsheet, such as Calc or Excel. This function IS allowed by the Parser . . . that means in short . . . you could write it with the GUI ( Graphical User Interface ) and when Base reads it, it will accept it. Also, very important, in the event you are using a Parameter Query ( user is prompted for input, such as a :From_Date and :To_Date ) . . . you will have to use CASEWHEN.
   
   

   http://www.hsqldb.org/doc/guide/ch09.html#N1251E wrote:

   CASEWHEN(exp,v1,v2) if exp is true, v1 is returned, else v2

   
   
   
2. You could use CASE WHEN ... THEN . . . END BUT by running it 'direct'. This means, the Base Parser will not check for syntax errors, AND, the Parameter portion will not be available.
   
   

   http://www.hsqldb.org/doc/guide/ch09.html#N1251E wrote:

   CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END when expr1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE]

   
   
   
3. This, I suspect, is the genesis of your initial error. A SELECT statement is JUST a read of the database, and, to bring back a set of records that meet the criteria. It does NOT update ( change ) the contents of any of your records. Therefore, you canNOT say, as in your first post above . . . THEN "Cost" = 50 ELSE . . .
   
   
4. I have put below, the syntax, for use of both a CASEWHEN and CASE WHEN using your original example.
   
   
5. A database, should, only contain atomic data. That is, data at the 'lowest' level. You can then 'maniplulate' (combine, calculate ) the data to return (format) the data as you need.
   
   For example, I would NOT recommend putting in the 'calculated' value ( "Cost") as a 'field' in your table, since, it is a 'calculated' value. Instead, I would recommend you create a VIEW that performs the calculation below, AND, this will contain the Primary field from your original table, and, the calculation. You can 'link' the two tables ( your original and the VIEW ) for any query, so, you only 'perform' the action once.
   
   
6. CASEWHEN function ( returning a NEW field/column to the result set, NOT changing the data in your table ):
   
   

   Code:

   CASEHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 60, 50, CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 45, 40, CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 30, 30 ))) AS "Calculated Cost"

   
   
   
7. CASE WHEN . . . THEN . . . END function ( returning a NEW field/column to the result set, NOT changing the data in your table ):
   
   

   Code:

   CASE WHEN DATEDIFF( 'minute', "StartTime", "FinishTime") = 60 THEN 50 WHEN DATEDIFF( 'minute', "StartTime", "FinishTime") = 45 THEN 40 WHEN DATEDIFF( 'minute', "StartTime", "FinishTime") = 30 THEN "Cost" = 30 END AS "Calculated Cost"

   
   
   
8. In the two above statements . . . what do you want them to return, in the event, DATEDIFF is not, 60, 45 or 30 ? ? ?
   

Forgive me for the verbose answer, but, I could not help myself, in giving what I hope is a 'clear' ( or at least semi-clear ) explanation.

I hope this helps and is as clear as mud, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.

[pjwalkerpj]

Hi Sliderule,
Thanks very much for the very detailed examination of my problem.

I think the code you suggested IE

CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 60, 50, CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 45, 40, CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 30, 30 ))) AS "Calculated Cost"

is exactly what I need and in fact was a version that I tried in one of the many iterations I have tested over the last few weeks. Unfortunately whatever I did with it returned a syntax error.

The 'calculated cost' field generated by CASEWHEN is ok because it can take the place of the existing "Cost' field that I manually fill in each week from a list in a form field.
I guess the problem I have is how to integrate the above CASEWHEN statement with the existing SQL statement generated by OoBase.

I hadn't thought about using a VIEW to generate a calculated table and I will have a look at this approach, but surely there is a way of adding CASEWHEN to the following SQL statement that I use to generate an invoice each week?

SELECT "Session ID" AS "Session ID", "Date" AS "Date", "Client" AS "Client", "StartTime" AS "StartTime", "FinishTime" AS "FinishTime", "Name" AS "Name", "Cost" AS "Cost", DATEDIFF( 'MINUTE', "StartTime", "FinishTime" ) AS "Length" FROM "SessionDetails" AS "SessionDetails" ORDER BY "Date" ASC, "Name" ASC

This is a cut down version because I can't use the real names etc for privacy reasons, but it includes all the elements of the real table.
The "cost" field in the table is empty until I put a value in it each week and depends on how long the professional session is.

To answer your question No8 if any of the three time values defined are not returned by DATEDIFF then null is the expected result and the different value can be entered entirely manually from the form field which would no longer need the list attached.

I hope this is clear to you and I haven't muddied the waters unnecessarily. I do appreciate your attention to this matter

Best regards
Pete

[sliderule]

Pete:

You said,

[pjwalkerpj]

Hi sliderule,
Thanks again for your efforts. I must apologise for using an Australian vernacular with the term 'surely'. I was "wishing" rather than saying it was so!
I also was not asserting that the CASEWHEN statement I arrived at that was almost the same as yours worked. In fact it didn't.

As you can see I am not an expert in database design, but the little program I use works for my wife except for for entering the Cost value which sometimes is entered in error.
I look forward to trying the suggestions you have made as they seem to me to be absolutely sensible. However I am leaving town for a few day so won't be able to test until next Wednesday or so.

Again thanks
Best regards
Pete

[pjwalkerpj]

Hi Sliderule,
Thanks for your rapid reply, I just thought that the diagrams may have helped!
I have rehashed my response and beg your indulgence.

I have tried to rethink this problem through (with your help) from first principles. As a result I have drawn a flow-chart of my design (not included) which helped me understand the point you were making regarding base data (I think you described it as “atomic”, nice description) and calculated data.
Use of a View query/table to do the calculations is logical. I have been able to take the DATEDIFF statement out of the Invoice Query and put it into View 1, adjust the Invoice Query to include View 1 and it works fine as far as calculating the time for each session based on the StartTime and FinishTime

SELECT "Session ID" AS "Session ID",
"Date" AS "Date",
"StartTime" AS "StartTime",
"FinishTime" AS "FinishTime",
"Name" AS "Name",
DATEDIFF( 'MINUTE', "StartTime", "FinishTime" ) AS "Length"
FROM
"SessionDetails" AS "SessionDetails"
ORDER BY
"Date" ASC,
"Name" ASC

However every attempt to include a CASEWHEN statement has brought up an error statement
SELECT
"Session ID" AS "Session ID",
"Date" AS "Date",
"StartTime" AS "StartTime",
"FinishTime" AS "FinishTime",
"Name" AS "Name",
CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 60, 50, CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 45, 40, CASEWHEN(DATEDIFF( 'minute', "StartTime", "FinishTime") = 30, 30 ))) AS "Calculated Cost",
DATEDIFF( 'MINUTE', "StartTime", "FinishTime" ) AS "Length"

FROM "SessionDetails" AS "SessionDetails"

ORDER BY "Date" ASC,
"Name" ASC
This code produces this error message

"The data content could not be loaded.
Unexpected token in statement [SELECT “Session ID” AS “Session ID”, etc (copy of the complete code above)]"

I am using the View Query/Table I generated to test the SQL code. I think this approach must be OK because the DATEDIFF code by itself works.

In another test I left out the Length DATEDIFF code and just tried to use the CASEWHEN in the SELECT statement, same error resulted.

I hope this is a clearer description of the problem I am having and look forward to your erudite response.
Regards
Pete

[sliderule]

The SQL you describe above, IF copied EXACTLY as entered into OpenOffice Base does look OK -- assuming -- all the table / field names are correct and exactly as defined in your table. This includes the CASE ( UPPER, lower, Mixed ).

However, what I was suggesting about a VIEW . . . was to have a view ONLY of the Primary Key of your table ( "SessionDetails" ) AND the calculated field(s) . . . perhaps . . . Length and Cost. This way, you can use this same VIEW, with OTHER tables on as as needed basis.

I will assume, your field . . . "Session ID" is the Primary Key ( that is, only ONE occurrence per table ).

So, your VIEW ( I will use the name "CostView" . . . you can decide on a name of your liking ) will be made up of the following fields:

1. "Session ID"
   
2. "Length"
   
3. "Cost"

The following SQL ( or Query ) could be used:

[pjwalkerpj]

Hi Sliderule,
I will certainly say Gracias for all your efforts.
There is only one thing wrong, neither of your suggested ways of proceeding work in my database.
They both return the error
"Unexpected token in statement [SELECT etc]
The good thing about this is that your suggested way forward was the way I tried in the beginning of the whole exercise. The actual CASEWHEN statement may have had some bugs, but I have compared the only version I saved with yours and they are identical.
The not so good thing is I am at a complete loss to know how to move forward.
Regards
Pete

[sliderule]

Pete:

You said:

[pjwalkerpj]

Hi Sliderule,
I had already done that exercise, but I have just realised that the CASEWHEN statements you are now suggesting are slightly different.
The first two examples include a , 99999 at the end.
The last example does not
The first two examples work, the last does not until I include the last comma and a value.

As you said it is "simple"
I would have thought from the Manual that if nothing was entered for the last variable in the CASEWHEN statement then a NULL would have resulted. Anyhow this will do nicely for my purposes. If a new cost is needed I will just alter the SQL code.

Again many Gracias for your perseverence.
I will metaphorically go and dust off my sliderule in the vain hope of stirring up lost brain cells

Best regards
Pete