sql include rec number or in natural order 3

[rommeltm00]

good day

how can i include a record number in sql query

example

sele a.cname,b.caddress from personal a,address b,;
where a.cname='A' into curs x

i want to include the recno() of personal dbf into the query
how to do it.

my primary goal here is to preserve the natural order of the personal dbf.

please help

 

[jimstarr]

sele a.cname,b.caddress from personal a,address b, RECNO() AS rec;
where a.cname='A' into curs x ORDER BY rec


Jim

 

[rommeltm00]

sir jim

example of data

personal record

recno name
1 AB
2 C
3 A
4 E
5 Z
6 AY

i want to get the data from this command

sele name from personal where name='A' into curs x

i want to include in this command the recno()

output must be

recno name
1 AB
3 A
6 AY

this is the output i needed

 

[Mike Lewis]

rommeltm00,

Jim has given you the answer. All you need to do is to add RECNO() to the list of output fields. At its simplest, it would look like this:

SELECT RECNO(), CName FROM MyTable ;
  WHERE CName = 'A' ;
  ORDER BY 1 ;
  INTO CURSOR MyCursor

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[rommeltm00]

sir mike

when i used that command the record number it represent is the cursor recno()

all i want to do is extract the recno() from mytable

as you can see on my example

personal record

recno name
1 AB
2 C
3 A
4 E
5 Z
6 AY

i want to get the data from this command

sele name from personal where name='A' into curs x

i want to include in this command the recno()

output must be

recno name
1 AB
3 A
6 AY

this is the output sir mike

 

[bborissov]

Why?
DO NOT EVER RELATE on RecNo() for anything.
Always have Primary key for each table and use it not RECNO().
Also Functions like RECNO(), RECCOUNT() gives very strange results when you use them in SQL Query. Especially when more than one table is involved in that query. You can't use ALIAS in these functions because SQL engine open tables involved in query in their own NEW aliases.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[jimstarr]

When I run the following I get the desired results:

CREATE CURSOR cc (NAME c(2))
INSERT INTO cc VALUE('AB')
INSERT INTO cc VALUE('C')
INSERT INTO cc VALUE('A')
INSERT INTO cc VALUE('E')
INSERT INTO cc VALUE('Z')
INSERT INTO cc VALUE('AY')

SELECT RECNO() AS recno, NAME FROM cc WHERE NAME='A'


Jim

 

[Olaf Doschke]

Yes Jimstarr,

but only because your select does only work on a single table. As soon, as you join two tables, functions working on an alias like Recno(), Reccount(), Eof() etc. will give you unexpected results, even when using them with an alias like Recno("personal"). SQL Select will most probably not scan through personal but reopen the table with an alias like A,B,C,Q1 or whatever, so if personal is a used table you could get the active record number in each record of an sql result set.

You should have a key field in each table that you can select to identify the source record from a result set. That's the way it works and recno() is not meant for that.

Bye, Olaf.

 

[TamarGranor]

As others have said, if you're using only one table, you can include RECNO() in the field list. However, with multiple tables, you have to grab the record number first.

In VFP 9, a derived table makes this pretty easy:

SELECT WithRec.cname, Address.caddress, WithRec.nRec ;
  from (SELECT cName, RECNO() AS nRec FROM personal) WITHREC ;
    JOIN address ;
      ON <substitute the proper join condition here> ;
    where WithRec.cname='A' ;
    into CURSOROR x

In older versions, you have to do the query to add the record number first, and then use that result in a second query.

Having said all that, as several have pointed out, the utility of this technique is limited since you should have a better way than record number for identifying a record. I would do this only when working with older tables that were constructed without primary keys.

Tamar

 

[Olaf Doschke]

that's a very good idea and advice, to use a subquery/derived table for savely querying the correct recno(). Take that advice, rommel, if you have no other choice as the recno() as personal identifier.

Bye, Olaf.

 

[rommeltm00]

i will try! thank you guys for your time. God Bless and more power