Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
Calculated field? |
Mon, Aug 23 2010 4:06 AM | Permanent Link |
Francisco Fernandez NewTRON Informatica | Hi all.
Are there any way to create a field calculated with a SELECT instruction? Thanks and regards |
Mon, Aug 23 2010 4:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Aug 24 2010 3:07 AM | Permanent Link |
Francisco Fernandez NewTRON Informatica | 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. |
Tue, Aug 24 2010 3:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Aug 24 2010 4:42 AM | Permanent Link |
John Hay | 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 |
Tue, Aug 24 2010 5:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Aug 24 2010 7:01 AM | Permanent Link |
Francisco Fernandez NewTRON Informatica | 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 |
Tue, Aug 24 2010 7:49 AM | Permanent Link |
John Hay | 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 |
Tue, Aug 24 2010 10:17 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |