OpenEuphoria

2. Re: EUSQL get count of select if command

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Nov 03, 2004
• 431 views

sixs wrote:
> 
> Hello,
> I am selecting records if they equal to the master file.
> I want to print out the records in a for loop
> 
> sql = ( "SELECT * FROM SERVICE WHERE VAL(SERVICE.MEID) = 1;")
> norecs = length(sql)
> I have one record that qualifys. I get a norecs = 3
> I tried using the count:
> sql = ("SELECT COUNT(ID) FROM SERVICE WHERE VAL(SERVICE.MEID) = 1;")
> sqll =parse_sql (sql )
> I cant read it , but ind edb I run the  count statement and look at the 
> results and it shows me a one field record containing a value of 1 that 
> is left justified.

The result of an executed query will always be a 3-element sequence.  The
first element is a sequence containing the field names.  The second element
contains the data, where each row is a sequence whose elements are the
fields in the query.  The third element is a sequence of datatypes for
the fields.  So the query:

SELECT * FROM SERVICE WHERE VAL(SERVICE.MEID) = 1;

Should give you something like:

{
  { "MEID", "OTHER_FIELDS..." },
  {  "1", "OTHER FIELDS..." },
  {  EUSQL_EU_TEXT, EUSQL_EU_OBJECT }
}

And 

SELECT COUNT(ID) FROM SERVICE WHERE VAL(SERVICE.MEID) = 1;

should give:

{
  { "COUNT OF EXPR1" },
  { 1 },
  { EUSQL_EU_INTEGER }
}

You can get rid of the 'EXPR1' by giving the field an alias:

SELECT COUNT(ID) AS TOTAL_IDS FROM SERVICE WHERE VAL(SERVICE.MEID) = 1;

The sequence returned from parse_sql() shouldn't be messed with, and it's 
not easy to read.  It's the 'compiled' result that EuSQL uses when it 
runs a query.  If you're really interested, take a look at the block
of comments in parse_main() in EuSQL.  It's probably the best piece of
documentation on the format of a query.

Matt Lewis

4. Re: EUSQL get count of select if command

• Posted by "Elliott S. de Andrade" <quantum_analyst at hotmail.com> Nov 04, 2004
• 428 views

>From: sixs <sixs at ida.net>
>Reply-To: EUforum at topica.com
>To: EUforum at topica.com
>Subject: Re: EUSQL get count of select if command
>Date: Wed, 03 Nov 2004 17:05:44 -0700
>
>Thanks for your reply. I'm using the value and this code to get the value
>    v = thiscount
>    v = value(thiscount)
>   v = v[1]
>I thought there might be an eusql command to get the value.
>

   v[1] is the error status returned from the value() function. Shouldn't 
you be using v[2], the actual value?

~[ WingZone ]~
http://wingzone.tripod.com/