SQL query question

SQL query question

Post by Robert Lafo » Thu, 22 Feb 2001 12:05:35



object Panel4: TPanel
  Left = -24
  Top = 0
  Width = 434
  Height = 269
  TabOrder = 1
  object Label5: TLabel
    Left = 328
    Top = 16
    Width = 29
    Height = 16
    Caption = 'Rel.'
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clNavy
    Font.Height = -13
    Font.Name = 'MS Sans Serif'
    Font.Style = [fsBold]
    ParentFont = False
  end
  object Label2: TLabel
    Left = 176
    Top = 16
    Width = 32
    Height = 16
    Caption = 'HPC'
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clNavy
    Font.Height = -13

Hi,

Is there a way to reference a number obtained from a subquery in a
following column such as :

   SELECT column1 AS name1, (SELECT count(*) FROM table1) AS name2,
<<value obtained in the preceding column>> / 10 AS name2 FROM table2.

Since referencing "name2" is illegal, how can i do it ?

Regards
Robert  

 
 
 

SQL query question

Post by BP Margoli » Fri, 23 Feb 2001 06:02:59


Robert,

One option is to define the subquery as a derived table, and join to it ...
for example ...

 SELECT column1 AS name1,
                  name2,
                  name2 / 10 AS name3
FROM table2
CROSS JOIN (SELECT name2 = count(*) FROM table1) AS t1

----------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> object Panel4: TPanel
>   Left = -24
>   Top = 0
>   Width = 434
>   Height = 269
>   TabOrder = 1
>   object Label5: TLabel
>     Left = 328
>     Top = 16
>     Width = 29
>     Height = 16
>     Caption = 'Rel.'
>     Font.Charset = DEFAULT_CHARSET
>     Font.Color = clNavy
>     Font.Height = -13
>     Font.Name = 'MS Sans Serif'
>     Font.Style = [fsBold]
>     ParentFont = False
>   end
>   object Label2: TLabel
>     Left = 176
>     Top = 16
>     Width = 32
>     Height = 16
>     Caption = 'HPC'
>     Font.Charset = DEFAULT_CHARSET
>     Font.Color = clNavy
>     Font.Height = -13

> Hi,

> Is there a way to reference a number obtained from a subquery in a
> following column such as :

>    SELECT column1 AS name1, (SELECT count(*) FROM table1) AS name2,
> <<value obtained in the preceding column>> / 10 AS name2 FROM table2.

> Since referencing "name2" is illegal, how can i do it ?

> Regards
> Robert


 
 
 

SQL query question

Post by Robert Lafo » Sat, 24 Feb 2001 12:12:25


On Wed, 21 Feb 2001 16:02:59 -0500, "BP Margolin"


>Robert,

>One option is to define the subquery as a derived table, and join to it ...
>for example ...

> SELECT column1 AS name1,
>                  name2,
>                  name2 / 10 AS name3
>FROM table2
>CROSS JOIN (SELECT name2 = count(*) FROM table1) AS t1

>----------------------------------------------------------
>BP Margolin

Hi BP,

 Thanks for your insight. But my problem is that i need the subquery
to use fields of the outer query in its where clause like in the
following example:

SELECT column1 AS name1,
             (SELECT count(*) FROM table2 t2
             WHERE  t1.column1=t2.column1) AS name2,
             >>> name2<<< / 100
FROM table1 t1
GROUP BY ...
ORDER BY ...

I'd pretty much like to know how to go about it.

Regards
Robert

 
 
 

SQL query question

Post by BP Margoli » Sat, 24 Feb 2001 22:07:31


Robert,

Perhaps something like ...

SELECT column1 AS name1,
                 name2,
                 name2 / 100
FROM table1 t1
JOIN (SELECT column1, name2 = count(*) from table2
           GROUP BY column1) as t2
  ON (t1.column1 = t2.column1)
ORDER BY ...

----------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> On Wed, 21 Feb 2001 16:02:59 -0500, "BP Margolin"

> >Robert,

> >One option is to define the subquery as a derived table, and join to it
...
> >for example ...

> > SELECT column1 AS name1,
> >                  name2,
> >                  name2 / 10 AS name3
> >FROM table2
> >CROSS JOIN (SELECT name2 = count(*) FROM table1) AS t1

> >----------------------------------------------------------
> >BP Margolin

> Hi BP,

>  Thanks for your insight. But my problem is that i need the subquery
> to use fields of the outer query in its where clause like in the
> following example:

> SELECT column1 AS name1,
>              (SELECT count(*) FROM table2 t2
>              WHERE  t1.column1=t2.column1) AS name2,
>              >>> name2<<< / 100
> FROM table1 t1
> GROUP BY ...
> ORDER BY ...

> I'd pretty much like to know how to go about it.

> Regards
> Robert

 
 
 

1. SQL Query question and unload db question

Hello,

I've already accomplished part of what I need to do by going three
sides around the barn, but wondered if there was an easier way.

What I wanted was just a list of the table names in a db.

To do that, I ran help tables *.t*, which, of course, got me _all_ the
information about each table, then did a search, cut and paste routine
on all that to get just the table names into a concise list.

Is there some syntax I missed someplace, or a query routine that would
produce just the table names in a db? What would have really been
peachy, is a routine that would have pulled out only those table names
ending in 000, 110, or 700. The table name format is something like
ttccom010110.

Second part is a question about unloaddb. We're moving a 2.0/8125
install to 2.5/whatever install. The tools that come with the front end
app are apparently Baan tools (the app was originally written in that,
several generations ago).

The typical instructions call for running a bdbpre command line routine
against the aforementioned table list, which creates a delimited file
suitable for doing a bdbpost back into the new install tables.

Are there any problems in moving directly from 2.0 to 2.5? And, is
there anything special or or more preferable about using the
unloaddb/loaddb command line arguments?

Many thanks and for those celebrating Memorial Day, hope it was good
for you.

Regards,

Mike Ossing
Rodem, Inc.
Cincinnati, OH
513-922-6140

2. Grid Refreshes at bottom

3. SQL Query Question...can I do this?

4. SQL 6.5 - Thin on documentation.

5. SQL Query Question

6. Changing Oracle based data into an outlook format?

7. A SQL query question...

8. Type decimal

9. SQL-Query-question

10. SQL Query Question

11. Transact-SQL Query Question

12. A SQL Query Question

13. SQL Query Question