Force SQL result fields to integer?

Force SQL result fields to integer?

Post by Karl Perr » Fri, 04 Jul 2003 23:23:27



Using P9.

In a SQL statement:

SELECT ClientID, count(access) as NumAccesses
FROM Patient
GROUP BY ClientID
ORDER BY ClientID

Is there any way to force the answer table's NumAccesses field to be a
certain data type - I'd prefer Integer?

TIA,

Karl Perry

 
 
 

Force SQL result fields to integer?

Post by Larry DiGiovann » Fri, 04 Jul 2003 23:27:46



> Is there any way to force the answer table's NumAccesses field to be a
> certain data type - I'd prefer Integer?

The COUNT() function should already be returning an Integer.  If you want
to force a result to another data type, you can do this by using the
CAST() function.  Check it out in LocalSQL.hlp.

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

 
 
 

Force SQL result fields to integer?

Post by Karl Perr » Sat, 05 Jul 2003 02:29:15




> > Is there any way to force the answer table's NumAccesses field to be a
> > certain data type - I'd prefer Integer?

> The COUNT() function should already be returning an Integer.  If you want
> to force a result to another data type, you can do this by using the
> CAST() function.  Check it out in LocalSQL.hlp.

Looks like I'll have to use CAST().  You're right, it should return an Int,
but it's returning an integer.

Karl

 
 
 

Force SQL result fields to integer?

Post by Larry DiGiovann » Sat, 05 Jul 2003 02:49:16



> Looks like I'll have to use CAST().  You're right, it should
> return an Int, but it's returning an integer.

What are you looking at?  An Int and an integer are the same thing, aren't
they?

When I do

SELECT state, count(*) as NumStates
  FROM customer
GROUP BY state

The Numstates column of the answer table is type I - Long integer.  Isn't
that what you want?

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

 
 
 

Force SQL result fields to integer?

Post by Karl Perr » Sun, 06 Jul 2003 03:29:33




> > Looks like I'll have to use CAST().  You're right, it should
> > return an Int, but it's returning an integer.

> What are you looking at?  An Int and an integer are the same thing, aren't
> they?

Sorry - I mistyped.  I meant to type "numeric" above but typed "integer"
instead.

I queried the same table several times using different date criteria:

SELECT ClientID, count(access) AS JuneTots (MayTots, etc.) FROM MyTable
WHERE EntryDate >= "06/01/2003" AND EntryDate <= "06/30/2003"

Then I queried for May, then for April, etc.

Sometimes the resulting column in the answer table was an integer, other
times it was a numeric.

When I later tried to combine things back together, I had problems and ended
up having to manually restructure all of the columns to the same type.  It
was quite a hassle.

Karl

 
 
 

1. Integer VB Vrs Integer SQL Arrrgggggggg

Why ?

An sql counter field is Int by default normaly this would be used but in vb
Integer is limited so when retriving data in vb based on the counter id in
sql we blow-up. With a nice

"Runtime error 6 Overflow"

SQL Data Type Interger "integer or int Has a length of 4 bytes, and stores
numbers from -2,147,483,648 through 2,147,483,647"

VB Data Type Integer "Integer 2 bytes -32,768 to 32,767 "

Does MS have A standard or what ???

Am i now forced to chage all integer variables in my code to long?

Or is there a solution here?

Any input all

Jason

2. How do I change *.mdb files into *.exe ?

3. How to make a "decimal"-field to an "integer"-field

4. Very Interesting Indeed...

5. How to Join char field to integer field ?

6. Clustrting and SQL Mail

7. link master autoInc field to Detail Integer field

8. Oracle Conversion Tool

9. Automatic random (unique) integer field in SQL Server?

10. Format integer data type field in SQL 6.5

11. SELECT - SQL with Integer fields

12. type mismatch when writing null to sql server integer field

13. Calculating time in minutes (integer result)