Need help to format a calculated field in a query

[batboy]

I'm working on a simple database for a little league baseball team. In the query I think is where the statistical calculations would be made, not certain, this could be root of the problem.

The outcome for Batting Average calculation and other stats needs to be formatted to show three decimal places. I'm stuck on how to get this accomplished. I did make an honest effort to read documentation and Google search but haven't found a clear explanation.

Hopefully, this is not too trivial. If I can get over this hump, then maybe the questions will get more sophisticated.

Thank you

Using OpenOffice 4.01 on Windows XP computer.

[batboy]

I should mention that I tried to format the column in the Query results for the calculated result and then saved the query.

This definitely added three decimals places to the outcome, but, after re-running the query, the calculated results returns to a number with no decimal places.

[DACM]

See: How to format an SQL table result

[batboy]

I see in the example how to format the number but no sure if I add that function into my existing query or what. This is all very new to me.

This is my query, I'm pulling the batter's number, the game and some batting numbers. At the end of the query is the calculation for At Bats which is a whole number and then Batting Avg will typically be a decimal number.

SELECT "Player_ID", "Game_ID", "TPA", "Runs", "Hits", "BB", "SO", "HBP", "SF", "SH", "IBB", "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) AS "AB", "Hits" / ( "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) ) AS "AVG" FROM "Batting"

The example shows:
SELECT CAST( CAST( "PRICE" * 2 AS DECIMAL ( 18 , 2 ) ) AS VARCHAR ( 20 ) ) AS "TOTAL" FROM "ITEMS"

Would the formatted query look like this:
SELECT "Player_ID", "Game_ID", "TPA", "Runs", "Hits", "BB", "SO", "HBP", "SF", "SH", "IBB", "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) AS "AB", "Hits" / ( "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) ) AS DECIMAL (1,3) AS VARCHAR (4) AS "AVG" FROM "Batting"

[DACM]

Maybe more like...

• Code: Select all

  SELECT "Player_ID", "Game_ID", "TPA", "Runs", "Hits", "BB", "SO", "HBP", "SF", "SH", "IBB", 
  "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) AS "AB", 
  CAST ( CAST ( "Hits" / ( "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) ) AS DECIMAL (4,3) ) AS VARCHAR (5) ) AS "AVG" FROM "Batting"

And as I recall, the VARCHAR (#sum total) includes the decimal separator (dot or comma).

[batboy]

Yes, I now see your logic in how that would be written.

Pasting the suggestion from above into the Query generates an error when the Query is executed, see below. I'm not sure what it means. Can a query be too long? There's a few more calculations that need to be made.


SQL Status: 22001
Error code: -124

Value too long in statement [SELECT "Player_ID", "Game_ID", "TPA", "Runs", "Hits", "BB", "SO", "HBP", "SF", "SH", "IBB", "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) AS "AB", CAST( CAST( "Hits" / ( "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) ) AS DECIMAL ( 1 , 3 ) ) AS VARCHAR ( 5 ) ) AS "AVG" FROM "Batting"]

[Sliderule]

Forgive me for responding here, but, I could not help myself.

Batboy, you are real ( database field type pun intended ) close to the solution.

Let me re-write the Query, as you did above, but, with line-breaks for readability:

Code: Select all

SELECT 
   "Player_ID",
   "Game_ID",
   "TPA",
   "Runs",
   "Hits",
   "BB",
   "SO",
   "HBP",
   "SF",
   "SH",
   "IBB",
   "TPA" - ("BB" + "IBB" + "HBP" + "SF" + "SH") AS "AB",
   CAST(CAST("Hits" / ("TPA" - ("BB" + "IBB" + "HBP" + "SF" + "SH")) AS DECIMAL(1, 3)) AS VARCHAR(5)) AS "AVG"
FROM "Batting"

What is important, and, the reason for the error, is you wrote: AS DECIMAL(1,3) . . . however, when defining the two parameter as a part of the DECIMAL . . . the first number is the TOTAL length of the result, and, and second number is the number of decimal places.

For example, DECIMAL(10,2) means maximum total number of digits is 10 and there are always 2 digits after the decimal point, while DECIMAL(10) means 10 digits without a decimal point.

Therefore, in your case, change the Query to AS DECIMAL(5, 3):

Code: Select all

SELECT 
   "Player_ID",
   "Game_ID",
   "TPA",
   "Runs",
   "Hits",
   "BB",
   "SO",
   "HBP",
   "SF",
   "SH",
   "IBB",
   "TPA" - ("BB" + "IBB" + "HBP" + "SF" + "SH") AS "AB",
   CAST(CAST("Hits" / ("TPA" - ("BB" + "IBB" + "HBP" + "SF" + "SH")) AS DECIMAL(5, 3)) AS VARCHAR(5)) AS "AVG"
FROM "Batting"

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.

[batboy]

That's progress, the column is formatted correctly as 0.000

At first, it appears the calculation is not working correctly in the query since the results for two of the test batters is wrong.

When I right click on the calculated results column to format the column, there are no options for numbers, just a single option, text. It appears the column is formatted for text?

[Sliderule]

Yes, since, the result ( calculation result ) is TEXT ( VARCHAR ) . . . that is why the 'format' capabilities as assigned by OpenOffice Base . . . is as you describe. What is being 'returned' by the database back-end ( HSQL ) . . . is also . . . text.

Keep in mind, you are CASTING the calculated value, to a DECIMAL value with three numbers to the right of the decimal place, AND, CASTING that to a display string.

I hope that is clear . . . well . . . at least as clear as mud.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this isue has been resolved.

[batboy]

How should the query be written to produce a number as previously described?

[Sliderule]

batboy:

How should the query be written to produce a number as previously described?

Sorry, I do not understand your question . . . I mean . . . is not the display, in the newly calculated column "AVG" what you want . . . with . . . one number to the left of the decimal point, a decimal point, and, three numbers to the right?

And, even if the answer ends with one or two zeros to at the end, for example: 0.300 it will still display that way. I thought, that was what you wanted. If not, please give some examples of what is being displayed, and, what you think should be displayed.

Just as an FYI ( For Your Information ) . . . keep in mind, that the Query I wrote, includes some additional text, that, on this forum, you have to press the EXPAND VIEW button, to see.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[batboy]

Yes, the physical format is exactly what I wanted, i.e., 0.000

However, the results of the AVG calculation is incorrect if AVG < 1.000.

I have three test batters created, two of which will have an AVG < 1.000. In their results, the outcome of the AVG calculation is 0.000

The database is attached, its very basic at this point since I'm just getting started. There is only one query. Run it and look to the far right, you will see the Hits column and the AB column. AVG = Hits / AB. Since the data in these columns is correct, I think the calculation must be correct in the background but possibly the display of the result has a problem.

Notice in the calculation for AVG how the AB calculation is duplicated. Is it possible to reuse the previously defined alias AB in the AVG calculation. Other statistical calculations will get downright ugly unless there is some way to use a predefined alias.

Thank you for your generous patience and sharing your expertise.

[Sliderule]

batboy:

Please forgive me today, I can only use one hand while responding here fo the next 15 hours.

Easy solution . . . and I have attached a file.

In the calculation

Code: Select all

SELECT 
   "Player_ID", 
   "Game_ID", 
   "TPA", 
   "Runs", 
   "BB", 
   "SO", 
   "HBP", 
   "SF", 
   "SH", 
   "IBB", 
   "Hits", 
   "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) AS "AB", 
   CAST( CAST( "Hits" * 1.000 / ( "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) ) AS DECIMAL ( 5 , 3 ) ) AS VARCHAR ( 5 ) ) AS "AVG" 

FROM "Batting"

The difference is, I am multiplying the "Hits" by 1.000 so it will calculate it to an accuracy of 3 decimal places.

I included a new, re-written query in the file.

Baseball_v2.odb

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[batboy]

Great job, I would have never thought of that, I would have remained barking up the wrong tree. <insert a big high five here>

Just one last question, can we use an alias in a calculation?

In this query, we first get the At Bats, AB = TPA-(BB+IBB+HBP+SF+SH). Then use AB in the AVG, AVG=Hits/AB

In the query, without using an alias, the calculation becomes, AVG = Hits / (TPA-(BB+IBB+HBP+SF+SH))

Considering the spirit of efficiency in a relational database, there should be a method to use alias or a previously calculated variable in a calculation.

[Sliderule]

I would recommend not using an alias within the calculation . . . BUT . . . maybe you can create a VIEW, comprised of the primary key, AND the calculation.

That way, you always have the calculation stored in the database back-end ( HSQL ) rather than in the query, which is stored in the *.odb file.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[DACM]

And as I recall, the VARCHAR (#sum total) includes the decimal separator (dot or comma).

I just want to mention a very small point...which is admittedly unclear in my "recollection" above. Technically, we should consider using:

• ... CAST( CAST( "Hits" ... AS DECIMAL (precision, scale) ) AS VARCHAR (precision + 1) ) AS "AVG" ...

in order to accommodate the decimal separator (dot or comma) in the final VARCHAR data type.

So if we're allowing up to 99.999 AVG "Hits" using DECIMAL ( 5, 3 ) then we should accommodate the display with VARCHAR ( 6 ) as a minimum.

• Code: Select all

  CAST( CAST( "Hits" * 1.000 / ( "TPA" - ( "BB" + "IBB" + "HBP" + "SF" + "SH" ) ) AS DECIMAL ( 5 , 3 ) ) AS VARCHAR ( 6 ) ) AS "AVG"