[Solved] CASE WHEN nesting syntax

Creating tables and queries

[Solved] CASE WHEN nesting syntax

Postby Matt123 » Sun Apr 10, 2011 11:41 am

Aaargh... I am getting a "Data content could not be loaded", "Not a condition in statement" error message.

I should be able to nest WHEN statements. With the following code it runs ok without line 2. With adding line 2 back I get the error messages. I'm picking I haven't got the nesting syntax right. Can I get an experienced eye on this?


Code: Select all   Expand viewCollapse view
(CASE WHEN ( SUM(CASE WHEN "Streamlining"='s' THEN "Fuel Capacity" END) / SUM("Fuel Capacity") ) >= .5 THEN 'Streamlined'
   WHEN ( SUM(CASE WHEN "Streamlining"='p' THEN "Fuel Capacity" END) / SUM("Fuel Capacity") ) >= .5 THEN 'PartialStreamlined'
   ELSE 'Unstreamlined'
END)


Cheers
Matt
Last edited by Matt123 on Wed Apr 13, 2011 1:01 am, edited 1 time in total.
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

Re: CASE WHEN nesting syntax

Postby Sliderule » Sun Apr 10, 2011 5:53 pm

Matt:

What you have written above . . . is a mess.

What might be helpful is you could describe, in English ( not code ) what you want the Query to do.

For example, at the START of the code above, you said . . .

Code: Select all   Expand viewCollapse view
SUM(CASE WHEN "Streamlining" = 's' THEN "Fuel Capacity" END


However, you did NOT qualify it it ( beginning of the CASE WHEN that terminates with END ) an ELSE statement. Put another way, what should happen WHEN "Streamlining" is NOT equal to 's' ? ? ?

Also, an output MUST be of the same data type. For example, if you are doing an arithmetic calculation on a field, a RESULT can NOT combine results of numeric types with text types.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: CASE WHEN nesting syntax

Postby Sliderule » Sun Apr 10, 2011 7:37 pm

You might want to try the following code, and, see if it works as you want ( notice, it is much easier to read / comprehend :bravo: ):

Code: Select all   Expand viewCollapse view
CASE WHEN SUM(CASE WHEN "Streamlining" = 's' AND "Fuel Capacity" / SUM("Fuel Capacity")) >= .5 THEN 'Streamlined'
     WHEN SUM(CASE WHEN "Streamlining" = 'p' AND "Fuel Capacity" / SUM("Fuel Capacity")) >= .5 THEN 'PartialStreamlined'
     ELSE 'Unstreamlined'
END


I hope this helps, please be sure to let me / us know. :super:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: CASE WHEN nesting syntax

Postby Matt123 » Mon Apr 11, 2011 1:06 am

Workin into the wee hours doesn't help...

Your code produced syntax errors unfortunately, but I played with it & rationalised my logic better. Here is the latest version and a test table to try it on.

Code: Select all   Expand viewCollapse view
SELECT "FleetName",

CASE
   WHEN SUM( CASE "Streamlining" WHEN 's' THEN "FuelCapacity" ELSE 1 END ) / SUM("FuelCapacity") >= .5 THEN 'Streamlined'
   WHEN SUM( CASE "Streamlining" WHEN 'p' THEN "FuelCapacity" ELSE 1 END ) / SUM("FuelCapacity") >= .5 THEN 'P-Streamlined'
   ELSE 'Unstreamlined'
END AS "Streamlining"

FROM "FleetTest"
GROUP BY "FleetName"


I am looking to define a relationship between the total "FuelCapacity" in a "FleetName" and the "FuelCapacity" carried by Ships in the Fleet where those Ships are 's', 'p' or 'u'. The table I am using (not "FleetTest") is a Query providing details from a "Ships" table, summing together the differant types of fuel tankage for each ship (& doing other calculations).

The code above works if you drop the second "WHEN SUM( CASE..." line.

The test table given here, is refered to in the code above and is a much shorter version than my Query and it generates the same error code.
"Not a condition in statement [SELECT..."

Code: Select all   Expand viewCollapse view
Table:  "FleetTest"
Fields: 
"FleetName" (PK), "Streamlining", "FuelCapacity"
Alpha, s, 20000
Bravo, p, 500
Charlie, u, 900
Delta, s, 200
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

Re: CASE WHEN nesting syntax

Postby Sliderule » Mon Apr 11, 2011 1:31 am

Not sure, but, try the following:

Code: Select all   Expand viewCollapse view
SELECT
   "FleetName",
   CASE WHEN SUM(CASE WHEN "Streamlining" = 's' THEN "Fuel Capacity" ELSE 1 END ) / SUM("Fuel Capacity") >= .5 THEN 'Streamlined'
        WHEN SUM(CASE WHEN "Streamlining" = 'p' THEN "Fuel Capacity" ELSE 1 END ) / SUM("Fuel Capacity") >= .5 THEN 'PartialStreamlined'
        ELSE 'Unstreamlined'
   END as "Streamlining ?"
FROM "FleetTest"
GROUP BY "FleetName"


You code above included the clause ELSE 1 . . . I do NOT understand why you have it that way, rather than either a 0 or NULL . . . but . . . that is YOUR calculation, as YOU wrote it for reasons only YOU know. :crazy:

Additionally, you said:

Matt123 wrote:and a test table to try it on.

I see no "test table" to try it on.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: CASE WHEN nesting syntax

Postby Matt123 » Mon Apr 11, 2011 2:08 pm

Sliderule wrote:Not sure, but, try the following:

Code: Select all   Expand viewCollapse view
SELECT
   "FleetName",
   CASE WHEN SUM(CASE WHEN "Streamlining" = 's' THEN "Fuel Capacity" ELSE 1 END ) / SUM("Fuel Capacity") >= .5 THEN 'Streamlined'
        WHEN SUM(CASE WHEN "Streamlining" = 'p' THEN "Fuel Capacity" ELSE 1 END ) / SUM("Fuel Capacity") >= .5 THEN 'PartialStreamlined'
        ELSE 'Unstreamlined'
   END as "Streamlining ?"
FROM "FleetTest"
GROUP BY "FleetName"


You code above included the clause ELSE 1 . . . I do NOT understand why you have it that way, rather than either a 0 or NULL . . . but . . . that is YOUR calculation, as YOU wrote it for reasons only YOU know. :crazy:


Two reasons. The first one is that you told me off in the second post for not having an ELSE clause. So I thought about that.
The second reason reason occured as I was thinking, 0 or NULL / xxx usually generates an error. While 1/xxx will result in a fraction far less than .5.

Additionally, you said:

Matt123 wrote:and a test table to try it on.

I see no "test table" to try it on.


Hmmm, I gave the format above for a pretty basic table that can easily be put straight into ooBase Tables. ooBase doesn't show me the SQL for setting up a table, otherwise I would have provided that.

The alternative code solution you give appears to change two aspects, both of which I have already tried and neither of which would lead to the error message I am getting.

You have given me some great help in the last few days, but here we have reached the limits of your "off the cuff" knowledge. Unless of course, you are willing to try the code and replicate the error. Its ok tho' if you are not keen, I appreciate your past efforts & error checking this use of CASE WHEN may be more involved than you wish to get.

We are definately NOT SOLVED tho, if anyone else wants to throw in thier 2 cents.
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

Re: CASE WHEN nesting syntax

Postby Sliderule » Mon Apr 11, 2011 5:13 pm

Matt123:

You have elected NOT to attach a 'sample' database file to this forum . . . with REAL data YOU created . . . and . . . showing the results you expect from the Query.

Therefore, I cannot be of any other assistance, based ( pun intended ) on your vague description.

Perhaps, someone else on the forum can help.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: CASE WHEN nesting syntax

Postby rudolfo » Mon Apr 11, 2011 8:21 pm

Okay, I throw my 5 cents in by trying to analyze the query with the nested case when operators:
Code: Select all   Expand viewCollapse view
SELECT "FleetName",
CASE
   WHEN SUM( CASE "Streamlining" WHEN 's' THEN "FuelCapacity" ELSE 1 END ) / SUM("FuelCapacity") >= .5 THEN 'Streamlined'
   WHEN SUM( CASE "Streamlining" WHEN 'p' THEN "FuelCapacity" ELSE 1 END ) / SUM("FuelCapacity") >= .5 THEN 'P-Streamlined'
   ELSE 'Unstreamlined'
END AS "Streamlining"
FROM "FleetTest"
GROUP BY "FleetName"

With the test data Alpha, Bravo, etc in mind the result would be:
Code: Select all   Expand viewCollapse view
| FleetName | Streamlining  |
+-----------+---------------+
| Alpha     | Streamlined   |
| Bravo     | P-Streamlined |
| Charlie   | ....

The expression after the WHEN may be as complex as it wants to be, the crux of the CASE is that the resulting/returned value will be the thing after the THEN (and ELSE). Only 3 possibilities: 'Streamlined', 'P-Streamlined', 'Unstreamlined'.

In other words the second field in the select list (the CASE-WHEN monster) is not aggregrating and it is not a constant, and hence it must be added to the group by after "FleetName",
Otherwise you will get an SQL error.
The reason why you didn't get the error when removing the second inner case-when .. might be that the outer case did return the same string for all records, i.e. the special constant aggregation case.
A simplified version of the CASE-WHEN logic should make this clearer:
Code: Select all   Expand viewCollapse view
SELECT "FleetName",
CASE
   WHEN "Streamlining" = 's' THEN 'Streamlined'
   WHEN 1 = 1 THEN 'P-Streamlined'
   ELSE 'Unstreamlined'
END AS "Streamlining"
FROM "FleetTest"
GROUP BY "FleetName"
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: CASE WHEN nesting syntax

Postby Matt123 » Tue Apr 12, 2011 12:12 am

Hi, ta for taking a look. You are spot on with what it should be producing.

rudolfo wrote:The expression after the WHEN may be as complex as it wants to be, the crux of the CASE is that the resulting/returned value will be the thing after the THEN (and ELSE). Only 3 possibilities: 'Streamlined', 'P-Streamlined', 'Unstreamlined'.

In other words the second field in the select list (the CASE-WHEN monster) is not aggregrating and it is not a constant, and hence it must be added to the group by after "FleetName",
Otherwise you will get an SQL error.


I'm missing some basic knowledge that sits between these two statements. ooBase is requiring the use of the GOUP BY command and the code works fine with just two possibilities 'Streamlined' and 'Unstreamlined' (a THEN and an ELSE). I'm not sure why it needs the GROUP BY command, but it relates to the SUM() used in the condition. (ie: its not needed if the SUM() is not there).

Soo, one line with a nested CASE WHEN SUM() works fine, but requires GROUP BY even though the results of the field are not intended to agregate.
Two lines equal a... problem. Error message, "Not a condition in [STATEMENT..."

The reason why you didn't get the error when removing the second inner case-when .. might be that the outer case did return the same string for all records, i.e. the special constant aggregation case.


It works perfectly with just one inner-case.

Hmm, apologies Sliderule, I didn't realise I could post attachments. Attachment attached.

Test Database.odb
(3.75 KiB) Downloaded 110 times
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

Re: CASE WHEN nesting syntax

Postby Sliderule » Tue Apr 12, 2011 12:22 am

You said / asked about me:

Matt123 wrote:but here we have reached the limits of your "off the cuff" knowledge.


Since, I have limited knowledge ( according to your arrogant attitude ), and, you are all knowing . . . I can best reflect to never reply with you again, since, according to you . . . "we have reached the limits of my "off the cuff" knowledge".

Yes, I agree, you are all knowing, and, I can only learn from an expert with your extensive knowledge.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: CASE WHEN nesting syntax

Postby Matt123 » Tue Apr 12, 2011 5:28 am

lol, any way I reply to that will merely start a flame war.

But again, thank you for your previous efforts, much appreciated. On this occasion my disappointment was merely that your 'answers' were not accurate, were not based on your own previous experience and not tested against the problem I have. Here in NZ we call that type of answer 'off the cuff', sometimes it works, sometimes it doesn't. Here it didn't. No problem, hopefully someone else with experience of this will be able to help.

Matt
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

Re: CASE WHEN nesting syntax

Postby rudolfo » Tue Apr 12, 2011 10:45 am

Think we need someone with OOo 3.3. As Sliderule pointed out (in some other post?) CASE WHEN works only since 3.3, before CASEWHEN or some other derivate was needed. But I'd rather use CASE WHEN and follow SQL standards.
In other words. You have attached the sample database file, that's good. But I can't test it, because my version is too old for this.

But I think the more relevant part here is the logic. You say "ooBase is requiring the use of the GROUP BY command". No, that's wrong. It is always the decision of the user to use GROUP BY or not. Seems a bit that you came to the last state by trial-and-error ... occasionally adding a group by and suddenly the nasty error from before disappears.

Sometimes it is easier to understand what GROUP BY is doing if you start with a DISTINCT query:
SELECT DISTINCT "Streamlining" FROM "FleetTest"
DISTINCT refers to all listed fields (here only one) and says if there is another "Streamlining" value 's' or 'p' that has already been seen in the records before, ignore it. For your sample table only 3 records. In short it eliminates duplicates. Now you may ask: How many duplicates do I have? This extra bit is given by GROUP BY:

SELECT "Streamlining", count(*) AS "how_many" FROM "FleetTest" GROUP BY "Streamlining"

Now you get:
Code: Select all   Expand viewCollapse view
| Streamlining | how_many |
+--------------+==========+
| p            |        1 |
| s            |        2 |
| u            |        1 |

The fields of a group-by select do always have fields that should be distinct (these fields must be repeated after the final GROUP BY) and all other fields have to be aggregation fields count(*), sum(...), avg(...)
A more elaborate example (you don't need to change the part after the group by if you add other aggregate fields):

SELECT "Streamlining", count(*) AS "how_many", SUM("FuelCapacity") AS "Capacity" FROM "FleetTest" GROUP BY "Streamlining"

Code: Select all   Expand viewCollapse view
| Streamlining | how_many | Capacity |
+--------------+==========+==========+   NOTE: Aggregation fields have double lines =====
| p            |        1 |      100 |
| s            |        2 |    21500 |
| u            |        1 |      900 |

In this case you ask "How many ships of each streamlining category do I have? How much capacity do I have per category?" Which is easy because you have the category or characteristics directly as column in the table. Often you have a date column "the_day" in your table and want to see how much records or how many tons of goods you transport on Mondays. The answer is you have to massage your date column to only show the day of the week. Most database engines have functions for this: weekday("the_date") and you do:
SELECT count(*) AS how_many, sum(goods) AS total FROM the_table WHERE weekday("the_date") = 'Mon'
(Note: thats somehow pseudo code, some databases might require a numerical 1 or 2 depending on the locale.)

But you don't want to run a separate query for each weekday, so group-by will help you:

SELECT weekday("the_date") AS wday, count(*) AS how_many, sum(goods) AS total FROM the_table
GROUP BY weekday("the_date")

Code: Select all   Expand viewCollapse view
| wday | how_many | total |
+------+==========+=======+
| Mon  |       24 |  3400 |
| Tue  |        0 |     0 |
| Wed  |       45 |  6700 |
| Thu  |       13 |   980 |
   :            :       :
| Sun  |        0 |     0 |


The crucial point here is that "weekday" filters the variety of 365 different days of a year into only 7 different output values. Your CASE-WHEN works in the same way: A variety of different input constellations are filtered to a set of 3 different output values/strings.

So you should really reconsider what you want to achieve. I am pretty sure you want to see the SUM or PERCENTAGE or something similar as an additional third column. But at the moment that's just swallowed.
Last edited by rudolfo on Tue Apr 12, 2011 11:12 am, edited 2 times in total.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: CASE WHEN nesting syntax

Postby rudolfo » Tue Apr 12, 2011 11:05 am

Seems a bit lengthly, so here's the step-by-step guide: First try to formulate your query with DISTINCT to figure out what categories you want to see. If you can't achieve anything with distinct, don't even think about using GROUP BY. It would be the wrong thing for your purpose.

 Edit: Note: If you have the primary key column in the list of (distinct) columns for GROUP BY it is usually a sign that the logic is wrong. The primary key is unique already, it can't become "more distinct" and nothing would be aggregated. Well, not nothing, but exactly one record -- I won't call this aggregating. 


One more thing: Some gut feeling tells me that you want to have something like:

P-Streamlined: 15% of the overall capacity
Streamlined : 75% of the overall capacity

That's not working with plain SQL in a single query. You can either calculate/aggregate the per-category capacity or the overall capacity, but not both in the same run.
Reporting Tools like Crystal Reports (or the SUN/Oracle Report Designer extension ... anyone?) have some solutions for this.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: CASE WHEN nesting syntax

Postby Matt123 » Tue Apr 12, 2011 3:55 pm

rudolfo wrote:Think we need someone with OOo 3.3. As Sliderule pointed out (in some other post?) CASE WHEN works only since 3.3, before CASEWHEN or some other derivate was needed. But I'd rather use CASE WHEN and follow SQL standards.
In other words. You have attached the sample database file, that's good. But I can't test it, because my version is too old for this.


bugger. I think only with someone else trying the database will we get an answer to why this CASE WHEN isn't doing what it should.

Seperate to that though, your following was quite interesting.

But I think the more relevant part here is the logic. You say "ooBase is requiring the use of the GROUP BY command". No, that's wrong. It is always the decision of the user to use GROUP BY or not. Seems a bit that you came to the last state by trial-and-error ... occasionally adding a group by and suddenly the nasty error from before disappears.


Yep, it was trial & error to get rid of the error message :) . I noted earlier the requirement for the GROUP BY seemed to disappear when I removed the SUM() commands and you note further on in your reply the SUM() command is an aggregation. I'm picking this applies even though the SUM() isn't tied to a field, pretty much meaning if I want a SUM(), COUNT(), etc and a category, I have to have the GROUP BY.

Just tested this & without the category field, I don't need the GROUP BY :) . I'm still getting the error message though that started this thread, but I've picked up a better understanding of GROUP BY and why this code needs it.

So you should really reconsider what you want to achieve. I am pretty sure you want to see the SUM or PERCENTAGE or something similar as an additional third column. But at the moment that's just swallowed.


The end result should merely be one of those three text strings for each Fleet. Based on the percentage fuel capacity of 's', 'p' & 'u' ships in that fleet.

I am reconsidering how I achieve this, splitting the task across two queries, as I'm starting to think the end result of this thread will be someone telling me "you can't get CASE WHEN to work like that!". I'm just not keen on splitting the task, I would like the query to be self contained rather than have Query A call on Query B.

That's not working with plain SQL in a single query. You can either calculate/aggregate the per-category capacity or the overall capacity, but not both in the same run.


Maybe this is the problem? In my nested CASE WHEN, I am getting the aggregate of 's' ships and the aggregate of all ships then dividing one by the other to get a % result.
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

Re: CASE WHEN nesting syntax

Postby rudolfo » Tue Apr 12, 2011 5:49 pm

Matt123 wrote:I am looking to define a relationship between the total "FuelCapacity" in a "FleetName" and the "FuelCapacity" carried by Ships in the Fleet where those Ships are 's', 'p' or 'u'. The table I am using (not "FleetTest") is a Query providing details from a "Ships" table, summing together the differant types of fuel tankage for each ship (& doing other calculations).
[...]
Code: Select all   Expand viewCollapse view
Table:  "FleetTest"
Fields: 
"FleetName" (PK), "Streamlining", "FuelCapacity"


Looking again on this I see that "FleetName" is the Primary Key for this table. If you GROUP-BY the unique fleetname, every sum("FuelCapacity") will only have one value to accumulate. In other words it makes no difference if you write simply "FuelCapacity" or sum("FuelCapacity").

I really recommend that you write your query with a WHERE clause instead of using the CASE-WHEN. Okay, you would have to break this into 3 or 4 queries, but it would reveal the logic or what you want to achieve. Because frankly so far, I haven't caught that!
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: CASE WHEN nesting syntax

Postby Matt123 » Wed Apr 13, 2011 1:00 am

OMG! That did it!

That was a ureka moment, of course SUM isn't needed if group by is being used. & getting rid of all the SUM statements removed the error message.

And the nested CASE WHEN still works. I now suspect the problem was perhaps in using SUM in the same field four times, linked back to your earlier observation.

Cut & paste into the query (its one of several details needed on Fleets) & I get a syntax error, I'm pretty sure I can sort that but I've gotta head out right now. Meantime I'm gonna mark this one as SOLVED :)

I really recommend that you write your query with a WHERE clause instead of using the CASE-WHEN. Okay, you would have to break this into 3 or 4 queries, but it would reveal the logic or what you want to achieve. Because frankly so far, I haven't caught that!


I'm pretty happy with using CASE WHEN THEN ELSE, it is very similar to IF THEN ELSE which I grew up on. Both allow finer control over your data than the global WHERE and at this stage I can read it easier, no doubt that will change :) I also get frustrated at having to look at multiple codes to figure out what the sum total result is. As it is, this DB will have maybe 30 or 40 queries working on it, maybe more. One job = one query max, preferably one set of jobs for an object = 1 query. Fortunately most queries are pretty straight forward.

I've got some editing though to do, to get rid of excess SUM commands in other queries :)

Here's the working code if you are interested.

Code: Select all   Expand viewCollapse view
SELECT "FleetName",

CASE WHEN (CASE "Streamlining" WHEN 's' THEN "FuelCapacity" ELSE 1 END) / "FuelCapacity" >= .5 THEN 'Streamlined'
    WHEN (CASE "Streamlining" WHEN 'p' THEN "FuelCapacity" ELSE 1 END) / "FuelCapacity" >= .5 THEN 'P-Streamlined'
    ELSE 'Unstreamlined'
END AS "Streamlining"

FROM "FleetTest"
GROUP BY "FleetName"


Cheers!
Matt
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

Re: [SOLVED] CASE WHEN nesting syntax

Postby rudolfo » Wed Apr 13, 2011 1:50 am

Guess you should have given an example output of the results:
That's what I get for your query:
Code: Select all   Expand viewCollapse view
| FleetName | Streamlining  |
+-----------+---------------+
| Alpha     | Streamlined   |
| Bravo     | P-Streamlined |
| Charlie   | Streamlined   |
| Delta     | Unstreamlined |

And I get exactly the same if I leave out the GROUP BY "FleetName". It must be like that, because FleetName is the Primary Key and grouping directly by a primary key has no effect.

Please understand what happens when your query hits the record
Bravo, p, 500
The first inner CASE "Streamlining" WHEN 's' THEN "FuelCapacity" ELSE 1 END evaluates to 1 (it is not 's' it is 'p') 1 divided by FuelCapacty 500 is surely less than 0.5 and not true and the next WHEN is evaluated: CASE "Streamlining" WHEN 'p' THEN "FuelCapacity" ELSE 1 END returns FuelCapacity 500. This is divided by FuelCapacity 500, so that's 1 and surely greater than 0.5: This WHEN branch evaluates to True and the outer case when will be 'P-Streamlined'.

If you go through this in your mind for another record you will realize, that the size of the capacity doesn't matter at all. No matter how large or small a ship is, if it is of catetory 's' the CASE-WHEN will evaluate to 'Streamlined'.

This kind of relation between 's' and 'Streamlined', or 'p' and 'P-Streamlined' does not depend in any way on the size of the ship. So why don't you do it the relational way? Build a second helper or lookup table for this relation:
TABLE streamline_types
Code: Select all   Expand viewCollapse view
category | type_label
---------+--------------
s       | Streamlined
p       | P-Streamlined
u       | Unstreamlined

and use a simple join:
Code: Select all   Expand viewCollapse view
SELECT "FleetTest"."FleetName", "streamline_types"."type_label" FROM "FleetName"
    INNER JOIN "streamline_label" ON "FleetName"."streamlining" = "streamline_types"."category"
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: [SOLVED] CASE WHEN nesting syntax

Postby Matt123 » Wed Apr 13, 2011 3:38 pm

The data actually being used is a little more complex than the test table. The test table was merely to show the error message & help figure out why the CASE WHEN statements weren't working.

I will have a play with Inner Joins tho', I've not used them yet and I like the use you show in your example. I'll google it tho, I've read something about inner & outer joins but I'm not really sure what the difference is. I'm already using joins in the Where command, whichever ones those are!

Thanks for your help :)

Cheers!
Matt
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests