SQL error message 
Author Message
 SQL error message

I'm trying to get two different count totals displayed in a dbgrid and when
I run the following I get 'Type mismatch in expression'.  Is there anyway to
add a blank integer column to the select statement so the union will be
printed on the same line and not below it.
This is a training tutorial and I need a count of all questions in each
lesson for each person and a count of how many each person answered
correctly - shouldn't be hard right  - but I've spent hours.  I'm new at D3
and the SQL is giving me a headache.

Select
  a."password",
  a."Number" as Lesson,
  count(DISTINCT a."PageNo") as Questions,     { get count of distinct
records, students have multiply tries at each question}
  ' '              { add a blank column for count of questions answered
correctly}
from log
 as a
 group by a."Password", a."Number"
Union all                       { not let's get count of records answered
correctly}
  Select
  loga."password",
  Loga."Number",
  ' ',                                { blank column for total count of
questions}
 count(loga."Correct" )
 from log
 as loga
Where loga."correct" = loga."Response"            (see if their response is
correct}
group by Loga."Password",Loga."Number"

All help is appreciated.


remove the "nospam" for correct e-mail address



Sat, 24 Mar 2001 03:00:00 GMT  
 SQL error message

Quote:

>I'm trying to get two different count totals displayed in a dbgrid and when
>I run the following I get 'Type mismatch in expression'.  Is there anyway to
>add a blank integer column to the select statement so the union will be
>printed on the same line and not below it.
>This is a training tutorial and I need a count of all questions in each
>lesson for each person and a count of how many each person answered
>correctly - shouldn't be hard right  - but I've spent hours.  I'm new at D3
>and the SQL is giving me a headache.

>Select
>  a."password",
>  a."Number" as Lesson,
>  count(DISTINCT a."PageNo") as Questions,     { get count of distinct
>records, students have multiply tries at each question}
>  ' '              { add a blank column for count of questions answered
>correctly}
>from log
> as a
> group by a."Password", a."Number"
>Union all                       { not let's get count of records answered
>correctly}
>  Select
>  loga."password",
>  Loga."Number",
>  ' ',                                { blank column for total count of
>questions}
> count(loga."Correct" )
> from log
> as loga
>Where loga."correct" = loga."Response"            (see if their response is
>correct}
>group by Loga."Password",Loga."Number"

>All help is appreciated.

In your UNION join query, you are trying to match up a string literal in
one of the SELECT statements with a numeric value (the COUNT function
result) in another. This is most likely the cause of the error you cite.

You would need to either convert the numeric to string to make it
compatible with the string literal, or use other than a string literal as
the placeholder column. You can do such conversions using the SQL function
CAST. For example:

  SELECT a."password", a."Number" as Lesson,
    COUNT(DISTINCT a."PageNo") AS Questions, CAST(0 AS INTEGER)
  FROM log AS a
  GROUP BY a."Password", a."Number"
  UNION ALL
  SELECT loga."password", Loga."Number", CAST(0 AS INTEGER),
    COUNT(loga."Correct" )
  FROM log AS loga
  WHERE (loga."correct" = loga."Response")
  GROUP BY Loga."Password",Loga."Number"

//////////////////////////////////////////////////////////////////////////
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)



Sat, 24 Mar 2001 03:00:00 GMT  
 SQL error message
Thanks It works, Now how do I combine the two rows returned?

Many years ago I did a lot of mainframe SQL but have forgotten a lot of the
details and there are some differences.  Can you refer me to a good book or
internet site that has all these neat tricks.

Thanks again.
Dianne

Quote:


>In your UNION join query, you are trying to match up a string literal in
>one of the SELECT statements with a numeric value (the COUNT function
>result) in another. This is most likely the cause of the error you cite.

>You would need to either convert the numeric to string to make it
>compatible with the string literal, or use other than a string literal as
>the placeholder column. You can do such conversions using the SQL function
>CAST. For example:

>  SELECT a."password", a."Number" as Lesson,
>    COUNT(DISTINCT a."PageNo") AS Questions, CAST(0 AS INTEGER)
>  FROM log AS a
>  GROUP BY a."Password", a."Number"
>  UNION ALL
>  SELECT loga."password", Loga."Number", CAST(0 AS INTEGER),
>    COUNT(loga."Correct" )
>  FROM log AS loga
>  WHERE (loga."correct" = loga."Response")
>  GROUP BY Loga."Password",Loga."Number"

>//////////////////////////////////////////////////////////////////////////
>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)



Sun, 25 Mar 2001 03:00:00 GMT  
 SQL error message

Quote:

>Thanks It works, Now how do I combine the two rows returned?

You cannot -- at least not using SQL alone. One way to aggegate this UNION
join result set would be to:

1. Save the result set to a temporary table (such as with a TBatchmove).
2. Run a summarizing SQL statement against the temporary table.

Given that for any distinct combination of the columns Password and Number,
there will be two rows in the temporary file (one from each SELECT query in
the UNION join), use the MAX function in this second query.

  SELECT a."Password", a."Number", MAX(a."Questions"), MAX(a."Correct")
  FROM TempFile a
  GROUP BY a."Password", a."Number"
  ORDER BY a."Password", a."Number"

This also requires altering the original UNION join statment. The second
calculated column was never actually given a name.

  SELECT a."password", a."Number" as Lesson,
    COUNT(DISTINCT a."PageNo") AS Questions, CAST(0 AS INTEGER) AS Correct
  FROM log AS a
  GROUP BY a."Password", a."Number"
  UNION ALL
  SELECT loga."password", Loga."Number", CAST(0 AS INTEGER),
    COUNT(loga."Correct" )
  FROM log AS loga
  WHERE (loga."correct" = loga."Response")
  GROUP BY Loga."Password",Loga."Number"

Quote:
>Many years ago I did a lot of mainframe SQL but have forgotten a lot of the
>details and there are some differences.  Can you refer me to a good book or
>internet site that has all these neat tricks.

Here are a few SQL books I like and use:

  Understanding the New SQL, a Complete Guide
  Jim Melton & Alan R. Smith
  Morgan Kaufmann, 1993
  ISBN: 1-55860-245-3

  SQL Instant Reference
  Martin Gruber
  Sybex, 1993
  ISBN: 0-7821-1148-3

  A Guide To the SQL Standard, Third Edition
  C.J. Date & Hugh Darwen
  Addison-Wesley, 1993
  ISBN: 0-201-55822-X

  The Practical SQL Handbook, Second Edition
  Judith S. Bowman, Sandra L. Emerson, & Marcy Darnovsky
  Addison-Wesley, 1993
  ISBN: 0-201-62623-3

//////////////////////////////////////////////////////////////////////////
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)



Sun, 25 Mar 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Q: SQL Error message "Capability not supported"

2. Conflicting error message formats from SQL Server 2000

3. Trapping error messages (Tquery - SQL -odbc - Oracle)

4. Error messages for SQL-calls through a TQuery

5. BDE Missing ) error message on SQL statement

6. Help:Record/Key Deleted message and Error message Numbers

7. Date error message and focusing in TDBgrid

8. "File is Locked" error message

9. Message Error Older Version

10. DBE Error Messages

11. Mysterious error message

12. BDE error message 15880

 

 
Powered by phpBB® Forum Software