RECNO() and SELECT - SQL 2

[torturedmind]

i know your having your vacations but i have this question. how can i include the record number of records that has been extracted from a particular table using SELECT - SQL which involves multiple tables? (is the question clear?) i tried using

SELECT DISTINCT RECNO("lot_curr&quot AS refno,...

but this produces a single number for different records. can anyone help me at this time of year.

anyways, thanks for the replies in advance. have a peaceful new year to all!

kilroy
philippines

 

[ramani]

Hi Kilroy,

1. I wish the new year brings in comfort and the 'torture' in mind should go now that you are member of this great forum. And by the way, you can change it to your nice name 'Kilroy' instead of 'torturedmind', if you like to... by visiting your profile. (And by the way, I am not commenting on the handle name, but drawing attention to every one that they can change their handle name if they want to).

2. I am not sure if this enough..
After the SQL select (no need to create RECNO() AS myRecNo)..
if you need only total records.. _tally will give the number of records.

3. If you need the recno() of the table you are extracting the records from.. you have to be little careful. When you say FROM myTable.. you cannot assume that it is using the already open alias. And that may be the reason you always endup with 1, since the alias of recno() you pickup could be the alias opened by you.
So the way to go is...

SELECT RECNO("A&quot AS myRec1, RECNO("B&quot AS myRec2, ;
a.field1, b.field1, ... ;
FROM myTable1 A, myTable2 B ;
WHERE .......

This makes the "A", "B" etc of the specific FROM tables used in the SQL.








____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.
Merry Christmas & Happy New Year

 

[torturedmind]

hi sir ramani,

1. I wish the new year brings in comfort and the 'torture' in mind should go now that you are member of this great forum.

Thanks. Being a member of this forum has helped me a lot in most of my projects and that the 'torture' has really gone away. Don't worry, am gonna change my handle a.s.a.p.

Anyways, here is the code am using. What i wanted is to get the record number from the table lot_curr. But this keeps giving me the error Alias not found.

SELECT DISTINCT RECNO("lotcurr&quot AS refno, pnbas.cust_id ;
FROM ("\\thepi003\ascim\wip\db\basic\pn_bas.dbf&quot AS "pnbas" INNER JOIN ;
("\\thepi003\ascim\wip\db\cur\lot_curr.dbf&quot AS "lotcurr" ;
ON ALLTRIM(lotcurr.th_pn) = ALLTRIM(pnbas.th_pn)

I think my brain is also on vacation.

kilroy
philippines

 

[ramani]

HI

Use this code..

SELECT DISTINCT RECNO("B&quot AS refno, A.cust_id ;
FROM ("\\thepi003\ascim\wip\db\basic\pn_bas.dbf&quot A ;
INNER JOIN ("\\thepi003\ascim\wip\db\cur\lot_curr.dbf&quot B;
ON ALLTRIM(B.th_pn) = ALLTRIM(A.th_pn)

...
...




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.
Merry Christmas & Happy New Year

 

[torturedmind]

thanks sir ramani,

no more error generated. but refno field contains the same number for all SELECTed records. any ideas why?

kilroy
philippines

 

[ramani]

Hi

If you are doing it in prg or in a form, this will be unreliable. This is because of the environment available at the time of the execution of the command.

I would not recommend using RECNO() in an SQL statement.

Whatever I stated will work, if there are no other tables open at that time. SO if this is in the load of a form with no tables open, it will give correct answer.

For a try, if you want..

CLOSE TABLES ALL
use the above code..

You will see the result correct. But that is not possible to do in the midst of a form. If this is a unique key, then you dont need the recno(). You can always SEEK that unique key to reach the tables record.

SQL will decide on its own, to use a open table or to use a table on the disk. RECNO("A&quot or whatever keeps tracking the table which is otherwise available by an earlier open time. SO that makes the statis number appearing on the result cursor.

An easier approach is to take the Unique of table B as a field in the SQL SELECT and set relation to that table B based on that unique key taken.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.
Merry Christmas & Happy New Year

 

[torturedmind]

thanks agains sir ramani,

i did try CLOSE DATABASE before the code. unfortunately, it still won't work. anyways, am gonna try devising an alternative on how to achieve this. this may take long since i can't seem to remember where my brain has gone vacationing.

thanks again for your help sir.


kilroy
philippines
"and that's what we call creativity..."

 

[ramani]

Hi

If you need it that badly.. try this one..


SELECT RECNO() AS myRec, th_pn FROM ;
("\\thepi003\ascim\wip\db\cur\lot_curr.dbf&quot ;
INTO CURSOR temp NOFILTER

SELECT DISTINCT b.myRec AS refno, A.cust_id ;
FROM ("\\thepi003\ascim\wip\db\basic\pn_bas.dbf&quot A ;
INNER JOIN temp B;
ON ALLTRIM(B.th_pn) = ALLTRIM(A.th_pn)



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.
Merry Christmas & Happy New Year

 

[Mike Lewis]

Hi Kilroy,

May I jump in here.

The reason that your RECNO()s are not showing the correct values is that you cannot make any assumptions about the internal workings of the SELECT command, including the order in which it "sees" its input records. In general, it is advisable not to use functions in SELECT that depend on the sequence of the records in the input tables. You can get away with it when there is a single table, but not when there is a join.

One solution in this case would be to create temporary input tables, each containing an addition field to hold the record number. You can do that as follows:

SELECT *, RECNO() AS REC_NO FROM InputTable_1 INTO CURSOR Cursor_1

You then use the temporary tables in the join to produce the required result.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[torturedmind]

thank you sir mike and sir ramani for the replies,

actually that was my initial thinking - to store all fields plus the record numbers of that table. i did put initially

CLOSE DATABASE
SELECT DISTINCT lot_curr.*, RECNO() AS refno ;
FROM ("\\thepi003\ascim\wip\db\cur\lot_curr.dbf&quot ;
INTO TABLE ("c:\invtemp\xlotcurr.dbf&quot

but still the content of all refno is actually the record number of the last record. btw, am using vfp6 sp5 in win98se.

darn, it's getting late here on this side of the globe and i can't seem to find my way out of this puzzle.


kilroy
philippines
"and that's what we call creativity..."

 

[torturedmind]

i found what's wrong with my SELECT statement. it seems that if i put a GROUP BY clause, RECNO() will always give me either the first or the last record number for all the queried records. problem solved. (see, i told you my brain's gone vacationing somewhere.)

anyways, thanks for all the help. have a very peaceful new year to all!!!


kilroy
philippines
"and that's what we call creativity..."

 

[Mike Lewis]

Glad you solved it. I hope your mind won't be so tortured from now on.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk