Board index » delphi » Adding a calculated field to a query How Pleeease

Adding a calculated field to a query How Pleeease

I need to add three calculated fields to a query. I've set all the code
up for the onCalcFields event but everything I've tried to add a
calculated field has failed miserably.
Please assist on this major learning curve or I'll just have to sell my
body to make a living.
Nick Gabb
 

Re:Adding a calculated field to a query How Pleeease


Quote
>I need to add three calculated fields to a query. I've set all the code
>up for the onCalcFields event but everything I've tried to add a
>calculated field has failed miserably.
>Please assist on this major learning curve or I'll just have to sell my
>body to make a living.

Please don't do that!  :-)  

Double click on your query object, up will pop the fields editor.  In there
right click and select New Field.  A New Field Dialog will then pop up and you
can define your calculated field there.  Once you have added it there, make
sure that you set whatever component you want to display it with
Datasource/Datafield properties.

********************************
Michael Glatz
glatzfa...@aol.com
mgl...@briefcase.com

Re:Adding a calculated field to a query How Pleeease


Un fortunately I need to do this on the fly with a query created at run
time. Anybody know how????
Quote

> >I need to add three calculated fields to a query. I've set all the code
> >up for the onCalcFields event but everything I've tried to add a
> >calculated field has failed miserably.
> >Please assist on this major learning curve or I'll just have to sell my
> >body to make a living.

> Please don't do that!  :-)

> Double click on your query object, up will pop the fields editor.  In there
> right click and select New Field.  A New Field Dialog will then pop up and you
> can define your calculated field there.  Once you have added it there, make
> sure that you set whatever component you want to display it with
> Datasource/Datafield properties.

> ********************************
> Michael Glatz
> glatzfa...@aol.com
> mgl...@briefcase.com

Re:Adding a calculated field to a query How Pleeease


Quote
Nick Gabb wrote:

> Un fortunately I need to do this on the fly with a query created at run
> time. Anybody know how????

Nick, if you want to do a calculation, let the query itself do it for
you.  

For example, I can say: SELECT SUM(DEBITS) - SUM(CREDITS) AS BALANCE
FROM ACCOUNTS...

The query result will include the single calculated answer as one of its
fields, named BALANCE.  I think this is what you actually want to do.

You -can- examine the Fields[] property of any TQuery object and obtain
the name of, and some information about, all of the fields that it
returns.  You do not have to know them in advance.

Re:Adding a calculated field to a query How Pleeease


Thanks! but I know how to do this already in fact its in my query.
The problem is no truncate in SQL
My problem is I recall Total stock (& other totals) in bottles
to convert them into cases I have to do this Sum
Cases=Trunc(Bottles/CaseSize);
CasesAndBottles:=Cases+(Bottles-((Cases*CaseSize))/100);
That means 14 bottles in a 12 bottle case becomes 1.02
This works perfectly with a calculated field & Oncalc... event
However I need to add the calculated field in code since I create the
Query on the fly at run time (it changes drastically).
I seem to have a mental block on how to add the calculated fields to a
query in code.
Nick Gabb
Quote
Sundial Services wrote:

> Nick Gabb wrote:

> > Unfortunately I need to do this on the fly with a query created at run
> > time. Anybody know how????

> Nick, if you want to do a calculation, let the query itself do it for
> you.

> For example, I can say: SELECT SUM(DEBITS) - SUM(CREDITS) AS BALANCE
> FROM ACCOUNTS...

> The query result will include the single calculated answer as one of its
> fields, named BALANCE.  I think this is what you actually want to do.

> You -can- examine the Fields[] property of any TQuery object and obtain
> the name of, and some information about, all of the fields that it
> returns.  You do not have to know them in advance.

Re:Adding a calculated field to a query How Pleeease


Quote
On Tue, 15 Dec 1998 18:14:28 +0000, Nick Gabb <h...@dial.pipex.com> wrote:
>Thanks! but I know how to do this already in fact its in my query.
>The problem is no truncate in SQL
>My problem is I recall Total stock (& other totals) in bottles
>to convert them into cases I have to do this Sum
>Cases=Trunc(Bottles/CaseSize);
>CasesAndBottles:=Cases+(Bottles-((Cases*CaseSize))/100);
>That means 14 bottles in a 12 bottle case becomes 1.02
>This works perfectly with a calculated field & Oncalc... event
>However I need to add the calculated field in code since I create the
>Query on the fly at run time (it changes drastically).
>I seem to have a mental block on how to add the calculated fields to a
>query in code.

True. There is no direct equivalent of the Delphi function Trunc in SQL.
But there is a function that will have the same effect: CAST. If you use
the CAST function to convert a floating point number (SQL data type NUMERIC
or DECIMAL) to INTEGER, the decimal portion of the original number is
simply dropped (i.e., no rounding).

  SELECT (15/8) AS Num1, CAST((15/8) AS INTEGER) AS Num2
  FROM AnyTable

The first column yields 1.875 and the second simply 1.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Re:Adding a calculated field to a query How Pleeease


Thanks Steve. I've already tried this but I tried it again to make sure.
I'm using local Interbase 4.? and it rounds Not truncates
I tried it using the query section
CAST(SUM(c.ROT_BOTTLES)/MAX(a.CASESIZE) AS INTEGER)
CASESIZE is always correct (only  1 per code)
The calculated field does work but I just can't get it right in a
dynamically created query.
I suppose eventually it could be a stored procedure creating a view but
it's most annoying. I hate admitting defeat with any program code.
Nick
Quote
Steve Koterski wrote:

> On Tue, 15 Dec 1998 18:14:28 +0000, Nick Gabb <h...@dial.pipex.com> wrote:

> >Thanks! but I know how to do this already in fact its in my query.
> >The problem is no truncate in SQL
> >My problem is I recall Total stock (& other totals) in bottles
> >to convert them into cases I have to do this Sum
> >Cases=Trunc(Bottles/CaseSize);
> >CasesAndBottles:=Cases+(Bottles-((Cases*CaseSize))/100);
> >That means 14 bottles in a 12 bottle case becomes 1.02
> >This works perfectly with a calculated field & Oncalc... event
> >However I need to add the calculated field in code since I create the
> >Query on the fly at run time (it changes drastically).
> >I seem to have a mental block on how to add the calculated fields to a
> >query in code.

> True. There is no direct equivalent of the Delphi function Trunc in SQL.
> But there is a function that will have the same effect: CAST. If you use
> the CAST function to convert a floating point number (SQL data type NUMERIC
> or DECIMAL) to INTEGER, the decimal portion of the original number is
> simply dropped (i.e., no rounding).

>   SELECT (15/8) AS Num1, CAST((15/8) AS INTEGER) AS Num2
>   FROM AnyTable

> The first column yields 1.875 and the second simply 1.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                      "The knowledge of the world is only to
> Technical Publications              be acquired in the world, and not in a
> INPRISE Corporation                 closet."
> http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Re:Adding a calculated field to a query How Pleeease


Quote
On Thu, 17 Dec 1998 00:21:42 +0000, Nick Gabb <h...@dial.pipex.com> wrote:
>Thanks Steve. I've already tried this but I tried it again to make sure.
>I'm using local Interbase 4.? and it rounds Not truncates
>I tried it using the query section
>CAST(SUM(c.ROT_BOTTLES)/MAX(a.CASESIZE) AS INTEGER)
>CASESIZE is always correct (only  1 per code)
>The calculated field does work but I just can't get it right in a
>dynamically created query.
>I suppose eventually it could be a stored procedure creating a view but
>it's most annoying. I hate admitting defeat with any program code.

Hmm. You're right, this is the behavior in InterBase. Must be some
implementation-specific difference. I tested under local SQL against a
Paradox table. You did not, after all, indicate in your original post what
specific database type you were using <grin>.

Since you are using InterBase, you might still be able to come up with a
workable solution. InterBase allows for user-defined functions that can
later be used directly in SQL statements. So you could conceivably concoct
a Trunc function that truly truncates a floating point number to an
integer.

Another way, a very roundabout way, would be a variation on the SQL
statement below. As InterBase does not have a native SUBSTRING function,
this would require one of the publicly available user-defined function
libraries available (see the InterBase Web site for a start).

  SELECT CAST(SUBSTRING(CAST((15/8) + 1000000 AS CHAR(14)) FROM 2 FOR 6) AS

    INTEGER)
  FROM AnyTable

The key to the above expression is adding an outrageously high number to
your calculation, converting that to a string, and then using the SUBSTRING
function to extract characters starting from the position just after that
initial one and up to but not including the decimal place. The final touch
is converting the result of all that to INTEGER.

The result of the calculation 15 divided by 8 is 1.875. Adding 1,000,000 to
that produces the number (still a floating point value) 1000001.875.
Converted to a CHAR(14) value this becomes "1000001.875   ". If the number
added to the original calculation is sufficiently high, you will always
know that there will be a unvarying number of zeros between the initial 1
of the 1,000,000 add to the calculation. Further, you will always know that
the number of character positions between that first zero (counting from
the left) and the decimal place. So the second and third parameters of the
SUBSTRING would always be the same. The SUBSTRING function thus extracts
the value "000001". The final conversion to INTEGER causes the leading
zeros to be simply discarded, yielding a value of 1.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Re:Adding a calculated field to a query How Pleeease


Eureka!!! (Leaps from bath)
Like this calculation. Inspired is the word. I feel most humble not
thinking of it.
I had thought of stored procedures but that always ties you to loads of
work if you wish to shift platform.
I can easily create this mammoth string in a procedure to save hours of
typing thus achieving and maintaining the dynamic creation I require.
Thanks again (and sorry for posting thanks)
Nick
Quote
Steve Koterski wrote:

> On Thu, 17 Dec 1998 00:21:42 +0000, Nick Gabb <h...@dial.pipex.com> wrote:

> >Thanks Steve. I've already tried this but I tried it again to make sure.
> >I'm using local Interbase 4.? and it rounds Not truncates
> >I tried it using the query section
> >CAST(SUM(c.ROT_BOTTLES)/MAX(a.CASESIZE) AS INTEGER)
> >CASESIZE is always correct (only  1 per code)
> >The calculated field does work but I just can't get it right in a
> >dynamically created query.
> >I suppose eventually it could be a stored procedure creating a view but
> >it's most annoying. I hate admitting defeat with any program code.

> Hmm. You're right, this is the behavior in InterBase. Must be some
> implementation-specific difference. I tested under local SQL against a
> Paradox table. You did not, after all, indicate in your original post what
> specific database type you were using <grin>.

> Since you are using InterBase, you might still be able to come up with a
> workable solution. InterBase allows for user-defined functions that can
> later be used directly in SQL statements. So you could conceivably concoct
> a Trunc function that truly truncates a floating point number to an
> integer.

> Another way, a very roundabout way, would be a variation on the SQL
> statement below. As InterBase does not have a native SUBSTRING function,
> this would require one of the publicly available user-defined function
> libraries available (see the InterBase Web site for a start).

>   SELECT CAST(SUBSTRING(CAST((15/8) + 1000000 AS CHAR(14)) FROM 2 FOR 6) AS

>     INTEGER)
>   FROM AnyTable

> The key to the above expression is adding an outrageously high number to
> your calculation, converting that to a string, and then using the SUBSTRING
> function to extract characters starting from the position just after that
> initial one and up to but not including the decimal place. The final touch
> is converting the result of all that to INTEGER.

> The result of the calculation 15 divided by 8 is 1.875. Adding 1,000,000 to
> that produces the number (still a floating point value) 1000001.875.
> Converted to a CHAR(14) value this becomes "1000001.875   ". If the number
> added to the original calculation is sufficiently high, you will always
> know that there will be a unvarying number of zeros between the initial 1
> of the 1,000,000 add to the calculation. Further, you will always know that
> the number of character positions between that first zero (counting from
> the left) and the decimal place. So the second and third parameters of the
> SUBSTRING would always be the same. The SUBSTRING function thus extracts
> the value "000001". The final conversion to INTEGER causes the leading
> zeros to be simply discarded, yielding a value of 1.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                      "The knowledge of the world is only to
> Technical Publications              be acquired in the world, and not in a
> INPRISE Corporation                 closet."
> http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Re:Adding a calculated field to a query How Pleeease


Solution is great but the BDE or Interbase 4.? won't except the
'FROM 2 FOR 6'
Anyone know the Interbase code for same pleeeeaase?

SELECT CAST(SUBSTRING(CAST((15/8) + 1000000 AS CHAR(14)) FROM 2 FOR 6)
AS INTEGER)
FROM AnyTable

Quote
Steve Koterski wrote:

> On Thu, 17 Dec 1998 00:21:42 +0000, Nick Gabb <h...@dial.pipex.com> wrote:

> >Thanks Steve. I've already tried this but I tried it again to make sure.
> >I'm using local Interbase 4.? and it rounds Not truncates
> >I tried it using the query section
> >CAST(SUM(c.ROT_BOTTLES)/MAX(a.CASESIZE) AS INTEGER)
> >CASESIZE is always correct (only  1 per code)
> >The calculated field does work but I just can't get it right in a
> >dynamically created query.
> >I suppose eventually it could be a stored procedure creating a view but
> >it's most annoying. I hate admitting defeat with any program code.

> Hmm. You're right, this is the behavior in InterBase. Must be some
> implementation-specific difference. I tested under local SQL against a
> Paradox table. You did not, after all, indicate in your original post what
> specific database type you were using <grin>.

> Since you are using InterBase, you might still be able to come up with a
> workable solution. InterBase allows for user-defined functions that can
> later be used directly in SQL statements. So you could conceivably concoct
> a Trunc function that truly truncates a floating point number to an
> integer.

> Another way, a very roundabout way, would be a variation on the SQL
> statement below. As InterBase does not have a native SUBSTRING function,
> this would require one of the publicly available user-defined function
> libraries available (see the InterBase Web site for a start).

>   SELECT CAST(SUBSTRING(CAST((15/8) + 1000000 AS CHAR(14)) FROM 2 FOR 6) AS

>     INTEGER)
>   FROM AnyTable

> The key to the above expression is adding an outrageously high number to
> your calculation, converting that to a string, and then using the SUBSTRING
> function to extract characters starting from the position just after that
> initial one and up to but not including the decimal place. The final touch
> is converting the result of all that to INTEGER.

> The result of the calculation 15 divided by 8 is 1.875. Adding 1,000,000 to
> that produces the number (still a floating point value) 1000001.875.
> Converted to a CHAR(14) value this becomes "1000001.875   ". If the number
> added to the original calculation is sufficiently high, you will always
> know that there will be a unvarying number of zeros between the initial 1
> of the 1,000,000 add to the calculation. Further, you will always know that
> the number of character positions between that first zero (counting from
> the left) and the decimal place. So the second and third parameters of the
> SUBSTRING would always be the same. The SUBSTRING function thus extracts
> the value "000001". The final conversion to INTEGER causes the leading
> zeros to be simply discarded, yielding a value of 1.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                      "The knowledge of the world is only to
> Technical Publications              be acquired in the world, and not in a
> INPRISE Corporation                 closet."
> http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Re:Adding a calculated field to a query How Pleeease


Quote
On Fri, 18 Dec 1998 22:36:46 +0000, Nick Gabb <h...@dial.pipex.com> wrote:
>Solution is great but the BDE or Interbase 4.? won't except the
>'FROM 2 FOR 6'
>Anyone know the Interbase code for same pleeeeaase?

>Steve Koterski wrote:

>> Another way, a very roundabout way, would be a variation on the SQL
>> statement below. As InterBase does not have a native SUBSTRING function,
>> this would require one of the publicly available user-defined function
>> libraries available (see the InterBase Web site for a start).

[...]

As I indicated, InterBase does not have a native SUBSTRING function. That
is, there is no SUBSTRING function in its implementation of SQL. The error
you are seeing is the result of trying to use one.

You need to use a user-defined function for this. You can write
user-defined functions that are stored in DLLs and set up so that InterBase
can use them in SQL statements. Or you can get one of a number of
third-party user-defined function libraries available (some of which are
absolutely free). Check the InterBase Web site (as a start) for some of
these third-party libraries.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Other Threads