Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
why there is 'select' without 'from' in the code 3
- Thread starter Rock4J
- Start date
- Thread starter
- #2
-
1
- #3
Whenever you do select queries, it makes a "CURSOR" or what it is so called, its a table actually containing the results from the SELECT statement.
SELECT [SCOPE] FROM [TABLENAME/ALIAS] && this makes a cursor with your defined filter like All, WHERE, etc.
SELECT [TABLENAME/ALIAS] && this selects that specific table/cursor in FOCUS
On the top side of the environment you will see menus and locate for Data Session Window(usually located at the top center part). It displays the list of opened tables/created cursor and the highlighted one is the currently on focus.
*Also: you can add at the end part of the SELECT [SCOPE] FROM [TABLENAME/ALIAS] "INTO CURSOR cursorname". This will pass all the result of your query to that cursor and later on use it. REMEMBER it is a READ ONLY cursor. IF YOU WANT IT EDITABLE ADD "READWRITE" at the end. These cursor/s are like virtual tables so it wont appear on your file system.
HTH
Dexter
SELECT [SCOPE] FROM [TABLENAME/ALIAS] && this makes a cursor with your defined filter like All, WHERE, etc.
SELECT [TABLENAME/ALIAS] && this selects that specific table/cursor in FOCUS
On the top side of the environment you will see menus and locate for Data Session Window(usually located at the top center part). It displays the list of opened tables/created cursor and the highlighted one is the currently on focus.
*Also: you can add at the end part of the SELECT [SCOPE] FROM [TABLENAME/ALIAS] "INTO CURSOR cursorname". This will pass all the result of your query to that cursor and later on use it. REMEMBER it is a READ ONLY cursor. IF YOU WANT IT EDITABLE ADD "READWRITE" at the end. These cursor/s are like virtual tables so it wont appear on your file system.
HTH
Dexter
- Thread starter
- #4
Thanks Dexter! For the complete explanation.
Quote:
SELECT [TABLENAME/ALIAS] && this selects that specific table/cursor in FOCUS
For example:
1. If there is '2 OR MORE TABLE' that have 'SAME STRUCTURE'
2. In a project I found for example 'SELECT EMPNO'
3. I notice the structure was similar with the '2 OR MORE TABLE' which have 'SAME STRUCTURE', but not one of the TABLE NAME is 'EMPNO'
Is there a way that I can identify which one of the '2 OR MORE TABLE' is actually related to the 'EMPNO'?
Thanks.
Regards,
Rocky
Quote:
SELECT [TABLENAME/ALIAS] && this selects that specific table/cursor in FOCUS
For example:
1. If there is '2 OR MORE TABLE' that have 'SAME STRUCTURE'
2. In a project I found for example 'SELECT EMPNO'
3. I notice the structure was similar with the '2 OR MORE TABLE' which have 'SAME STRUCTURE', but not one of the TABLE NAME is 'EMPNO'
Is there a way that I can identify which one of the '2 OR MORE TABLE' is actually related to the 'EMPNO'?
Thanks.
Regards,
Rocky
Olaf Doschke
Programmer
The help topic about SQL is called SELECT-SQL and the other SELECT command just shares the name, the only common thing is, it has to do with tables. Instead of querying a table it's there to "focus" a table, as dexter says it. That's the short explanation.
Two Concepts
There are two main concepts in data access and processing in VFP as a successor of dBase and other dBase compatible databases, SQL and xBase commands and functions. As a beginner you seem to have SQL knowledge and experience, but not dBase, although you already mention "alias" as one of the core xBase concepts. You can mix them, too.
In Foxpro you can "OPEN" a table instead or additional to selecting from it via SQL queries. You have a datasession for that matter, the default datasession is there from the start, you don't have to do anything to have a datasesion, you can add further "private" datasessions, but this leads aways from your main question. Each datasession has "slots", in which you open tables, directly or indirectly. Those slots are called workareas and can be addressed by a number or by an alias name (and for historical reasons the first 15 workareas can also be addressed by the lettersA to J - SELECT J for example - just for the sake of completeness) Often enough even users already using VFP for some time still understand an alias name as an alias you give to the dbf file, but indeed it's naming the workarea and by default VFP opens tables in a workarea it names with the table same, so you can easily confuse this, but it also doesn't matter much, it's making this "transparent", you don't have to think about it too much.
The two concepts mix in that datasession/workareas concept, oth concepts use workareas. That means even if you SELECT * FROM sometable.dbf and don't open the dbf beforehand (eg because you don't know that concept), this dbf is opened in a free workarea of the current datasession and stays open. The query result itself also is put into such a workarea, unless you query INTO ARRAY, but again this just leads away.
You can work on a table or a query result in such a workarea, no matter if you use SQL or old dBase commands. As you wrote it yourself already, you can also query FROM an ALIAS. That's true, you can open a table with an alias name identical to or differing from the table name and then SELECT from that alias name.
Addressing data
It's very important to know about the workarea concept, because it's your way to address both table and query result records and fields.
If you know recordsets from eg classic asp as Adodb.recordset. You know you can do stuff like oRS.movefirst, oRS.movenext etc. and then address a field by oRS.Fields("fieldname").value. In VFP you can simply write fieldname, as if it was a variable name and read the field value of the current record. So there is no recordset object, instead there is the concept of the focused or current workarea
And SELECT 1 or SELECT alias dows specify that current workarea.
I always recommend not making too much use of that shortcut notation. As nice and short that code is compared to oRS.Fields("fieldname").value, it's exactly looking as if using a variable. I prefer the more verbose addressing of a field by aliasname.fieldname, which also is a valid way to address the field of a record in a workarea (and here the confusion about table alias or workarea alias applies, you address a workarea, not a table this way, but it looks like table.field, if alias name=table name, of course).
The addressing via alias.field is possible, even you you don't SELECT alias beforehand, that's what makes it not only more verbose to the maintainer of code, but also saves a line of code. And it's not breakable code. If SELECTing a workarea and addressing a field, doesn't happen just a few lines apart of each other, you can easily introduce problems, when you add code changing the current workarea, while code addressing certain fields of course needs a table selected, that has these fields. So alias.fieldname is less breakable, unless you misuse the same alias name for different things.
The SELECT command, which is selecting a workarea and has nothing to do with SQL-Selects, therefore is almost unneeded, unless you make use of some of the few commands, which don't let you specify the alias they should work on. One such command is LOCATE. Almost any other command allows an IN alias clause, eg REPLACE ... IN alias, SET FILTER TO ... IN alias, SKIP 1 IN alias, GO TOP IN alias, etc.
The danger of it
The danger of having the wrong work area selected is most easily seen, if you do a ZAP without using the IN alias clause. If you successfully ZAP the wrong workarea, the data of it is lost. So if by chance or mistake an important table is the current workarea, yo can lose data. SET SAFETY ON adds a safety question prompt before that happens, also you need a table opened EXCLUSIVE instead of SHARED for ZAP to work, so it's not that dangerous, but be aware of this. When ZAP works, it doesn't check for referential integrity rules, so it can remove parent records and lead to orphaned child records.
So indeed, you almost don't need SELECT alias, if you follow my recommendation to make use of IN alias and the nWorkarea/cAlias parameters of functions like TABLEUPDATE(), TABLEREVERT(), CURSORSETPROP(), SEEK() and more xBase functions affecting a workarea.
Bye, Olaf.
Two Concepts
There are two main concepts in data access and processing in VFP as a successor of dBase and other dBase compatible databases, SQL and xBase commands and functions. As a beginner you seem to have SQL knowledge and experience, but not dBase, although you already mention "alias" as one of the core xBase concepts. You can mix them, too.
In Foxpro you can "OPEN" a table instead or additional to selecting from it via SQL queries. You have a datasession for that matter, the default datasession is there from the start, you don't have to do anything to have a datasesion, you can add further "private" datasessions, but this leads aways from your main question. Each datasession has "slots", in which you open tables, directly or indirectly. Those slots are called workareas and can be addressed by a number or by an alias name (and for historical reasons the first 15 workareas can also be addressed by the lettersA to J - SELECT J for example - just for the sake of completeness) Often enough even users already using VFP for some time still understand an alias name as an alias you give to the dbf file, but indeed it's naming the workarea and by default VFP opens tables in a workarea it names with the table same, so you can easily confuse this, but it also doesn't matter much, it's making this "transparent", you don't have to think about it too much.
The two concepts mix in that datasession/workareas concept, oth concepts use workareas. That means even if you SELECT * FROM sometable.dbf and don't open the dbf beforehand (eg because you don't know that concept), this dbf is opened in a free workarea of the current datasession and stays open. The query result itself also is put into such a workarea, unless you query INTO ARRAY, but again this just leads away.
You can work on a table or a query result in such a workarea, no matter if you use SQL or old dBase commands. As you wrote it yourself already, you can also query FROM an ALIAS. That's true, you can open a table with an alias name identical to or differing from the table name and then SELECT from that alias name.
Addressing data
It's very important to know about the workarea concept, because it's your way to address both table and query result records and fields.
If you know recordsets from eg classic asp as Adodb.recordset. You know you can do stuff like oRS.movefirst, oRS.movenext etc. and then address a field by oRS.Fields("fieldname").value. In VFP you can simply write fieldname, as if it was a variable name and read the field value of the current record. So there is no recordset object, instead there is the concept of the focused or current workarea
And SELECT 1 or SELECT alias dows specify that current workarea.
I always recommend not making too much use of that shortcut notation. As nice and short that code is compared to oRS.Fields("fieldname").value, it's exactly looking as if using a variable. I prefer the more verbose addressing of a field by aliasname.fieldname, which also is a valid way to address the field of a record in a workarea (and here the confusion about table alias or workarea alias applies, you address a workarea, not a table this way, but it looks like table.field, if alias name=table name, of course).
The addressing via alias.field is possible, even you you don't SELECT alias beforehand, that's what makes it not only more verbose to the maintainer of code, but also saves a line of code. And it's not breakable code. If SELECTing a workarea and addressing a field, doesn't happen just a few lines apart of each other, you can easily introduce problems, when you add code changing the current workarea, while code addressing certain fields of course needs a table selected, that has these fields. So alias.fieldname is less breakable, unless you misuse the same alias name for different things.
The SELECT command, which is selecting a workarea and has nothing to do with SQL-Selects, therefore is almost unneeded, unless you make use of some of the few commands, which don't let you specify the alias they should work on. One such command is LOCATE. Almost any other command allows an IN alias clause, eg REPLACE ... IN alias, SET FILTER TO ... IN alias, SKIP 1 IN alias, GO TOP IN alias, etc.
The danger of it
The danger of having the wrong work area selected is most easily seen, if you do a ZAP without using the IN alias clause. If you successfully ZAP the wrong workarea, the data of it is lost. So if by chance or mistake an important table is the current workarea, yo can lose data. SET SAFETY ON adds a safety question prompt before that happens, also you need a table opened EXCLUSIVE instead of SHARED for ZAP to work, so it's not that dangerous, but be aware of this. When ZAP works, it doesn't check for referential integrity rules, so it can remove parent records and lead to orphaned child records.
So indeed, you almost don't need SELECT alias, if you follow my recommendation to make use of IN alias and the nWorkarea/cAlias parameters of functions like TABLEUPDATE(), TABLEREVERT(), CURSORSETPROP(), SEEK() and more xBase functions affecting a workarea.
Bye, Olaf.
-
1
- #6
I hope that Olaf's explanation makes it all clear to you.
But if you still have a question or two, here is a super simplistic 5 Cent explanation.
You have to differentiate between SQL Query expressions where the SELECT requires a FROM clause...
and the 'other' use of SELECT which is unique to VFP which merely chooses which is the 'active' data table/cursor on which to operate...
Good Luck,
JRB-Bldr
But if you still have a question or two, here is a super simplistic 5 Cent explanation.
You have to differentiate between SQL Query expressions where the SELECT requires a FROM clause...
Code:
Example:
* --- Run a SQL Query against an 'Open' data table to produce results ---
SELECT * FROM MyDataTable WHERE <whatever> INTO CURSOR Results READWRITEand the 'other' use of SELECT which is unique to VFP which merely chooses which is the 'active' data table/cursor on which to operate...
Code:
Example:
* --- 'Open' (make available) a Data Table into a VFP data 'work area' ---
* --- the [b]IN 0[/b] tells VFP to 'open' it into the [u]next[/u] available data work area ---
USE ThisDataTable IN 0
* --- Since this data table may be one of MANY concurrently 'available' data tables, you have to 'select' one to be currently 'active' ---
SELECT ThisDataTable
<do whatever on ThisTable>Good Luck,
JRB-Bldr
Olaf Doschke
Programmer
If you find "SELECT EMPNO" in code and no table in your database is called EMPNOI.DBF, then that alias name "EMPNO" can be any alias name of any query result or temp table or cursor generated in code. You have to analyse code before SELECT EMPNO to knwo what it means. Not only code physically before that line, but exeecuting before that line is executed at runtime.
The easiest thing to do would be setting a breakpoint to that line. A Breakpoint will start the debugger, and while debugging you can see what the alias EMPNO is addressing, by examining DBF("EMPNO") in the watch window of the debugger. It'll tell you which DBF file is used in the workarea EMPNO. If the file extension is .TMP this is a cursor or query result. If it's a view cursor CURSORGETPROP("Database","EMPNO") could give a hint on the source of that data, also simply browsing EMPNO from the datasession window would help you see what it is.
Bye, Olaf.
The easiest thing to do would be setting a breakpoint to that line. A Breakpoint will start the debugger, and while debugging you can see what the alias EMPNO is addressing, by examining DBF("EMPNO") in the watch window of the debugger. It'll tell you which DBF file is used in the workarea EMPNO. If the file extension is .TMP this is a cursor or query result. If it's a view cursor CURSORGETPROP("Database","EMPNO") could give a hint on the source of that data, also simply browsing EMPNO from the datasession window would help you see what it is.
Bye, Olaf.
-
1
- #8
Olaf Doschke
Programmer
You could also search code via Code References (that's a project search tool in the Tools menu). Search for EMPNO or ALIAS EMPNO or CURSOR EMPNO to find places besides the SELECT EMPNO, where that alias name is used or defined.
Bye, Olaf.
Bye, Olaf.
- Thread starter
- #9
- Status
Similar threads
- Locked
- Question
- Locked
- Question
