Results 1 to 8 of 8

Thread: Easy query help

  1. #1
    Join Date
    Sep 2006
    Posts
    8

    Unanswered: Easy query help

    I want to perform the following query but i dont know how :

    select count(num) from X

    and I want the X to be a table from the following query :

    select table from bla bla bla . . .

    This cannot be done by :select count(num) from (select table from bla bla bla) !

    How can i build it ??? I have stuck !

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database is this? because the method you described should work

    what was the actual error message you got?

    perhaps you could also show real table and column names so we could see if there is an obvious syntax error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    8
    I get a incorrect synbtax :

    The query is
    select count(distinct timestamp) from (select cf.table from general_cfg cf, can c where cf.x1 = c.y1);

    This should have worked ???

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, it shouldn't've

    the subquery produces an intermediate table result which is used as the data source for the outer query's FROM clause, yes?

    well, that intermediate table result has a single column, called "table"

    thus, the outer table cannot count the distinct values of a column called "timestamp" because that column doesn't exist
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are we trying to sneak up on dynamic SQL here?

    -PatP

  6. #6
    Join Date
    Sep 2006
    Posts
    8
    And is there a solution to this problem at last ...?????

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes there is!!!!!!!!!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by archMEL
    select count(num) from (select table from bla bla bla)
    Add an alias name for the table:
    Code:
    select count(num) from (select num from table from bla bla bla) AS tmp
    Last edited by Peter.Vanroose; 09-08-06 at 16:14.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •