SELECT statement is showing unwanted display of data and is not passing data to a table.

SELECT statement is showing unwanted display of data and is not passing data to a table.

• Gossimmer

  As for the problem with the browse coming up: add the "Into Cursor" clause to your select statement. Here's how it is used... Select * from SomeTable Into Cursor MyCursor. This will result in a MyCursor alias in the current datasession. Fields can be referenced in MyCursor like MyCursor.MyField.

  There is a lot of information missing from your post such as what the artie_report_table looks like. I mean are you just trying to replace information in the selected record of the report table with information from the selected record in your generated cursor If you want to do that then you could just use a single REPLACE command like this...

  Replace RptTable.MyField1 with MyCursor.MyField1, RptTable.MyField2 with MyCursor.MyField2 IN "RptTable"

  If you wanted to do replacements on the entire report table for the entire cursor you generated, then you could use the "FOR" clause of the REPLACE command or do an UPDATE statement. In any event, the relationship between your report table and the cursor you generated is missing so I cannot give you an exact example of that. Hopefully this information is enough to get you started.

  Other things that I notice about your code is that you don't select the report table (artie_report_table) before you do your replacements and run your report. So, I would think that your report is trying to run off of the generated cursor which doesn't sound like what you want. Try using the SELECT command before that code...

  SELECT ("artie_report_table")

  
  
  

• rahil

  *** Direct the replace command explicitly
  REPLACE stock with stock, year WITH year_1, ;
          make WITH make_1, model WITH model_1 ;
  IN artie_report_table
  BROWSE
  REPORT FORM 'H:\Documents and Settings\dave\My Documents\Visual FoxPro Projects\REPORTS\arties report.frx' TO PRINTER PROMPT

  I guess that you are not selecting the artie_report_table so when you run the report it is in the wrong work area. The lasxt work area setting that you made is for the CURSOR (which you BROWSE) running the report does not automatically change to the work area

  (If you wonder why not, consider what would happen if a report ran on a linked set of three related tables - which one would it select )

  Try adding:

  SELECT artie_report_table

  immediately after your BROWSE and before you call the report

  
  
  

• Bearcats1375

  I tried adding the SELECT artie_report_table line and it had no apparent effect. I did notice though theat the BROWSE is displaying items from the wholesale_table1 , not the artie_report_table as based on the column names I see in the BROWSE window. So it seems like I'm looking at the successful selection of the data from the initial table instead of the successfull insertion of the selected data into the other table. Is there a way I can test to see if the REPLACE is actually working Here's the code:

  SELECT stock, year_1, make_1, model_1 ;

  FROM wholesale_table1 ;

  WHERE In_Date >=DATE()-60 ;

  AND sold_for < 1 ;

  INTO CURSOR cur_result

  SELECT cur_result

  GO TOP

  REPLACE stock with stock, year WITH year_1, ;

  make WITH make_1, model WITH model_1 ;

  IN artie_report_table

  BROWSE

  SELECT artie_report_table

  REPORT FORM 'C:\Documents and Settings\ADMIN\My Documents\Visual FoxPro Projects\REPORTS\arties report.frx' TO PRINTER PROMPT

  
  
  

• Francisco73

  Hi Cetin

  >>Makes quite sense, this site is playing all type of weirdnesses to me as well:)

  If it's any comfort, it's not just you. The UI is certainly weird and it definitely is not a WYSYWYG - the fonts keep changing and so do the font sizes...ah well, it's what we have so what can we do

  Nothing.

  
  
  

• Bizsoft

  Is there actually record in artie_report_table

  If not, you have to add one first - you cannot add data to an empty table! (APPEND BLANK IN artie_report_table)

  But in this case, (if you do not have records there already) why not just run the SQL Select into the table directly YOu can use the "AS" to name the output fields correctly 

  SELECT year_1 AS year ...... INTO TABLE artie_report_table

   

  
  
  

• Will MC

  Those extra lines in code seem to be due to formatting here. If that is right, how this code worked great I don't understand. It should give syntax error. Probably you meant just select part as great.

  SELECT store, stock, year_1, make_1, model_1, auction1, transport, recon, sold_for, fees, net_check, result ;
   FROM wholesale_table1 ;
   WHERE In_Date >=DATE()-60 ; 
   AND sold_for < 1 ;
   into cursor crsReport

  REPORT FORM ;
   ('C:\Documents and Settings\ADMIN\My Documents\'+;
    'Visual FoxPro Projects\REPORTS\arties report.frx') ;
   TO PRINTER PROMPT

  Why do you need replacement when doing a report Instead adjust fieldnames in cursor or report (do not use alias nor dataenvironment in report). Your replace commands' intent is unclear to me. Assuming it was like that (it would do a single record replacement):

  REPLACE stock with crsReport.stock, ;
    fees WITH crsReport.fees, ;
    year WITH crsReport.year_1, ;
    make WITH crsReport.make_1, ;
    model WITH crsReport.model_1, ;
    recond WITH crsReport.recon ;
   in "artie_report_table"

   

   

  
  

• Stanley Daniel

  It sounds like what I write are only visible to me. Anyway I can read later myself:)

  I wonder why he would need to select with readwrite clause and later do a single replace on that cursor. 

  Actually I can't even see where does year, make, model .. come from in that cursor. I expect it to error, no If included in select why not directly those names and do a replace in top record if any. Must be a trick

   

  
  

• KishoreK

  Is there a way I can test to see if the REPLACE is actually working

  Yes, just

  SELECT artie_report_table
  BROWSE

  after the REPLACE

  You should also explicitly name the source in the REPLACE statement first:

  REPLACE stock with cur_result.stock, year WITH cur_result.year_1, ;
  make WITH cur_result.make_1, model WITH cur_result.model_1 ;
  IN artie_report_table

  I should have made that clear from the start, sorry (the only excuse is that when you do this stuff without thinking, you tend to assume everyone does). When using an "IN" clause VFP assumes that all references apply to the named table unless you specify otherwise...

   

  
  
  

• luigi_loaec

  >> The SELECT statement seems to default to browsing the data it selected in a new window ( I don't know how to get it to not do that but sure wish I did )

  You need an INTO clause on SQL statement, otherwise VFP creates a temporary cursor named "QUERY" and displays it in a Browse window. You don't say what version of VFP this is, but you should be able to do this:

  SELECT.......INTO CURSOR artie_report_table READWRITE

  You need the "READWRITE" to ensure that you can then update the result set - the second part of your question:

  >> the resulting data does not seem to make it to the new table via the REPLACE commands

  Result sets created by SQL statements on native VFP tables are Read-Only unless you include the ReadWrite clause. If you are using VFP 6.0 or earlier you cannot use "READWRITE" so you either have to create a proper table (i.e. INTO TABLE....) or use a trick to make the result set writable.

  SELECT .... INTO CURSOR junk
  USE DBF("junk") AGAIN IN 0 ALIAS artie_report_table
  USE IN junk

  This is the equivalent of the READWRITE clause and leaves you with a writable cursor named "artie_report_table".

  The benefit of using a cursor instead of a table for reports is that cursors are always local to the user (so if two users create the same report there is no conflict) and also that they are destroyed by VFP when the application is closed so you don't have to clean up old tables all the time.

  By the way, as Craig pointed out, you can replace multiple fields in one replace statement...

  REPLACE stock with stock, fees WITH fees, year WITH year_1, ;
          make WITH make_1, model WITH model_1, recond WITH recon ;
          IN artie_report_table

  (Note you don't even need the "," but it does make it more readable I think)

   

  
  
  

• Ken Elmy

  Sorry for the confusion here. I think we all made the same mistake, let's try and summarize this:

  [1] When you issue an SQL SELECT in VFP you must specify the output destination. If you omit the INTO clause, then VFP creates a local cursor named "query" and displays it in the browse window. This is why you get the browse! So amend your query to read:
  SELECT store, stock, year_1, make_1, model_1, auction1, ;
         transport, recon, sold_for, fees, net_check, result ;
  FROM wholesale_table1 ;
  WHERE In_Date >=DATE()-60 ;
  AND sold_for < 1 ;
  INTO CURSOR cur_result

  [2] When you issue a REPLACE there are two things to notice:

  [item a] You can do multiple fields at once. There is no need to have separate statements for each field.

  [item a] The scope of REPLACE is "next 1" by default, and it uses the table in the work area that is selected. If you are at EOF() in that area, then no replace will be done unless you explicitly use the IN [alias] clause.

  So to be sure that your replace works correctly you should use code like this:

  *** Ensure the correct work area
  SELECT cur_result
  *** Ensure not at EOF()
  GO TOP
  *** Direct the replace command explicitly
  REPLACE stock with stock, fees WITH fees, year WITH year_1, ;
          make WITH make_1, model WITH model_1, recond WITH recon ;
          IN artie_report_table
  

  [3] Of course, if what you are doing is populating a pre-defined field list, you could do the whole thing in one step using SQL anyway because you can create columns named anything you like in the result set using the AS command directly in the SELECT, and even define completely new columns like this:

  SELECT store, stock, year_1 AS year, make_1 AS make,;
         model_1 AS model, auction1, transport, ;
         recon AS recond, sold_for, fees, net_check, result, SPACE(20) AS andys_column  ;
  FROM wholesale_table1 ;
  WHERE In_Date >=DATE()-60 ;
  AND sold_for < 1 ;
  INTO TABLE artie_report_table
  

  
  
  

• Sree C

  If I choose SELECT artie_report_table and then browse, the BROWSE window shows no data in it other than the column headers. If I change the code to SELECT cur_result, than the data showed by the SELECT code is properly shown. Does this mean the REPLACE command isn't working It seems like it, as the data is not being written in to the table.

  SELECT stock, year_1, make_1, model_1 ;
  FROM wholesale_table1 ;
  WHERE In_Date >=DATE()-60 ;
  AND sold_for < 1 ;
  INTO CURSOR cur_result
  SELECT cur_result
  &&GO TOP
  REPLACE artie_report_table.stock with cur_result.stock, artie_report_table.year WITH cur_result.year_1, ;
  artie_report_table.make WITH cur_result.make_1, artie_report_table.model WITH cur_result.model_1 ;
  IN artie_report_table
  SELECT artie_report_table
  BROWSE
  &&BROWSE
  && SELECT cur_result
  &&BROWSE
  &&REPORT FORM 'C:\Documents and Settings\ADMIN\My Documents\Visual FoxPro Projects\REPORTS\arties report.frx' TO PRINTER PROMPT
  

   

  
  
  

• dmssjt

  Hi Cetin

  >> It sounds like what I write are only visible to me

  Well I certainly didn't see your reply when I wrote mine probably a caching issue because I only saw it AFTER I posted my response.

  >> I wonder why he would need to select with readwrite clause and later do a single replace on that cursor

  On re-reading that, I think he is actually trying to replace the fields in "artie_repor...." with the data FROM the cursor - not IN the cursor. Looks like I mis-read the question here!!!

  >> Actually I can't even see where does year, make, model .. come from in that cursor. I expect it to error, no If included in select why not directly those names and do a replace in top record if any. Must be a trick

  No, like you I thought he was trying to update the cursor, but I think he is actually doing the SELECT into a cursor and then using the cursor to update the table. We have just given him the answer to a different question!

  I'll post an update....

   

  
  
  

• fxtrader

  Hi Andy,

  "Well I certainly didn't see your reply when I wrote mine probably a caching issue because I only saw it AFTER I posted my response."

  Makes quite sense, this site is playing all type of weirdnesses to me as well:)

  I was surprised to see such an interface from MS itself. Anyway living with it (and probably this ill behaving control is DHTML control:)

  
  

• Skyler

  Wow! Thank you everyone for jumping in on this one...  It seems like my report is not getting the data from the table as when it previews in the BROWSE, the selection is accurate and all data is where it should be from the SELECT statement. If I set the report to use the data from the artie_report_table, the printer will eject a single blank sheet of paper. If I remove the table from the Report's data environment, the report will print all the bands, labeling and so on but with no data. This is confusing to me as the BROWSE shows me the data is properly selected, but it is not making it to either the artie_report_table, or I am not doing something in the code to permit the transfer of the data. Here's the code:

  SET SAFETY OFF

  SELECT stock, year_1, make_1, model_1 ;

  FROM wholesale_table1 ;

  WHERE In_Date >=DATE()-60 ;

  AND sold_for < 1 ;

  INTO CURSOR cur_result

  *** Ensure the correct work area

  SELECT cur_result

  *** Ensure not at EOF()

  GO TOP

  *** Direct the replace command explicitly

  REPLACE stock with stock, year WITH year_1, ;

  make WITH make_1, model WITH model_1 ;

  IN artie_report_table

  BROWSE

  REPORT FORM 'H:\Documents and Settings\dave\My Documents\Visual FoxPro Projects\REPORTS\arties report.frx' TO PRINTER PROMPT

  
  
  
• SELECT statement is showing unwanted display of data and is not passing data to a table.