Hi all.

Are there any way to create a field calculated with a SELECT instruction?

Thanks and regards

Francisco


I can see two interpretations of your question:

1. Can you calculate the data returned by a SELECT
2. Can you create a blank field that can then be calculated by an OnCalcFields event.

The answer to 1. is Yes eg field1+field2

The answer to 2. is No but you can achieve the same result but its more difficult (or at least the only way I know how to do it is). You need to create a table with the query (real, temporary or memory) using CREATE TABLE AS ... WITH DATA. Add the FieldDefs in Delphi for the real fields and then create the calculated field.

If you need No. 2 I would recommend looking to see if it can be switched to No. 1

Of course there may be a third interpretation that I couldn't spot.

Roy Lambert [Team Elevate]

Roy.

Thanks for your response.

Can I do a SELECT instruction with fields of two tables? something like this:

SELECT CODE,NAME,Table2.AMOUT WHERE CODE=TABLE2.CODE

Thanks.

Francisco


Yup, You always could even in DBISAM its just that the syntax has changed. Now you use sub-selects.

Something like

SELECT CODE,NAME,(SELECT AMOUNT FROM Table2 WHERE Table1.CODE=TABLE2.CODE) FROM Table1

The sub-select statement must only return one field

Roy Lambert [Team Elevate]

Francisco

> Can I do a SELECT instruction with fields of two tables? something like
this:
>
> SELECT CODE,NAME,Table2.AMOUT WHERE CODE=TABLE2.CODE

This looks like a simple join

SELECT Code,Name,Table2.Amount FROM Table1
JOIN Table2 ON Table1.Code=Table2.Code

John

John

>This looks like a simple join

It certainly could be, and I feel bashful at contradicting such a master of SQL, but there can be  differences between using JOINS and sub-selects. In this case I think the results would be identical, but, with some joins there can be multiple rows for each hit of the join condition which a sub-select will not generate.

Its an area I'm still only just starting to get familiar with This is a quote from Tim in response to a question of mine about JOINs vs subselects

"The sub-selects are intended to be used in situations where the joins are not used for selection, but for *lookups*, ala LEFT OUTER JOINs"

Roy Lambert [Team Elevate]

John, Roy.

Thanks for your quick response.

This is was I wanted. My SQL knowledge is poor by now and I thank all help you give to me.

Thanks and regards

Roy

> >This looks like a simple join
>
> It certainly could be, and I feel bashful at contradicting such a master
of SQL

Yeah right <bg>

> but there can be differences between using JOINS and sub-selects. In this
case I think the results would be identical, but, with some joins there >
can be multiple rows for each hit of the join condition which a sub-select
will not generate.

With multiple hits I think the subquery returns null.

> Its an area I'm still only just starting to get familiar with This is a
quote from Tim in response to a question of mine about JOINs vs subselects
>
> "The sub-selects are intended to be used in situations where the joins are
not used for selection, but for *lookups*, ala LEFT OUTER JOINs"

That makes sense and you can get a live result set.

John

John,

<< With multiple hits I think the subquery returns null. >>

Only if the result set is sensitive.  If it isn't, then it will generate an
error.   The only reason that we don't issue an error with sensitive result
sets is because the "lookup" is done on-demand, and issuing an exception
during row retrieval is a no-no since it will cause VCL grids to go into an
infinite exception loop during painting.

--
Tim Young
Elevate Software
www.elevatesoft.com