Trouble with Create View.  
Author Message

PostPosted: Visual FoxPro General, Trouble with Create View. Top

I am having a real hard time with a sql query becuase of a VERY poorly designed foxpro system. I have tried to run multiple renditions of select from where this and that OR this2 and that but can't seem to get it to work. Inststead i have decided to try and create a VIEW. I am not sure want ver. of VFP i am even using. but here goes nothing. below is the code that i would hope could create this VIEW. Any help is EXTREMEly appreciated.

dim RS, SQL
set RS = Server.CreateObject("ADODB.Recordset")

SQL = " CREATE VIEW all_bidders AS "
SQL = SQL & " SELECT * FROM a_bidder, a_absbid"
SQL = SQL & " WHERE a_absbid.paddle_num = a_bidder.paddle_num AND a_absbid.salenum = a_bidder.salenum AND a_absbid.salepadnum = a_bidder.salepadnum"
SQL = SQL & " SELECT * FROM all_bidders "

I want to join these two tables so that eventually i can compare them to a third table but i get:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC Visual FoxPro Driver]Syntax error or access violation
/jsmc/Ats/all_bidders_SPEC.asp, line 83

line 83 is my Rs.Open SQL, Conn, 3,1 line to open and execute the query. Like i said i am pretty clueless right now and any help is appreciated!


Visual FoxPro2  

PostPosted: Visual FoxPro General, Trouble with Create View. Top

As obvious it's syntax error.

"Create view" and "create sql view" are totally different commands. Considering you passed that very simple and basic step it might still not work. You're using a union and I hope that you know that in order to use union there are a series of rules like fields should match, no memos etc.


PostPosted: Visual FoxPro General, Trouble with Create View. Top

do i need to use a UNION i don't really get what they do what is the correct syntax to create a new VIEW in conjunctin with ASP and then call it to compare to a third table

SQL = " CREATE SQL VIEW all_bidders AS "
SQL = SQL & " SELECT a_bidder.company_name, a_bidder.paddle_num, a_bidder.salenum, a_absbid.paddle_num, a_absbid.company_name, a_absbid.sequence FROM a_bidder, a_absbid"
SQL = SQL & " WHERE a_absbid.paddle_num = a_bidder.paddle_num AND a_absbid.salenum = a_bidder.salenum "

Is that the correct syntax can i even use VIEWs with Visual Foxpro

If that is correct can I then say something like;

Select * from ALL_BIDDERS, A_LOTS where all_bidders.paddle_num = a_lots.paddle_num or all_bidders.sequence = a_lots.sequence ORDER by a_lots.sequence

Does this make sense or am I just completely wrong here Again, any insight is more than I have now and appreciated.


PostPosted: Visual FoxPro General, Trouble with Create View. Top

You don't know what a UNION does Judging like "very poorly designed foxpro system" I thought you had good knowledge about databases.

"Can I even use VIEWs with VFP " :) Yes you can but I doubt you need it. Your next question "..can I then say something like;" increases my doubts.

It's not clear what you're trying to do. With the part I could understand:

strSQL = "select ab.company_name, ab.paddle_num, ab.salenum, abs.paddle_num, abs.company_name, abs.sequence, al.*" & _
FROM a_bidder ab " & _
" inner join a_absbid abs on abs.paddle_num = ab.paddle_num AND abs.salenum = ab.salenum " & _
" inner join a_lots al on ab.paddle_num = al.paddle_num or ab.bidders_sequence = al.sequence" & _
" order by al.sequence"

UNION might have a place in comparison too. Let's say you have 2 tables identical in structure (at least datatype wise or you need to convert to a common comparable type). Lets say its Persons1 and Persons2 tables where you can compare records on first/last names:

select "source1" as source, firstName, lastName, address, city, zip from Persons1 ;
union ;
select "source2" as source, firstName,lastName, address, city, zip from Persons2 ;
order by lastName, firstName

UNION [ALL] : If all column values match in 2 or more records UNION returns you a single row for that one (unions - removes duplications). Including ALL keeps all rows from sources and returns you datasets "concatenated" -don't judge me on this one I always forget spelling- (as if you entered all rows to the same table).
In the above sample what I'm doing is to introduce a constant column (source: source1 and source2 constant values) then bring all rows from both tables ordered on last/first names. So if there is "Joe Doe" on both tables I'd see that repeated with "source1"/"source2". Generally this kind of comparison is followed by a "having cnt() > 1" queries.

If you know shape syntax you could then use MSDataShape provider along with VFPOLEDB as the data provider and get comparison recordset as hierarchical relations.


PostPosted: Visual FoxPro General, Trouble with Create View. Top

OK, here are the layouts of the three tables so you can see what I am dealing with more clearly. I should've done this in the first place. I will list the feilds and their value in ( ):

salename (auction name. [ex: 'lg 2006'])
salenum (auction's number. [ex: 'lg 2006' = auction num 192])
sequence (lot numbers of the auction. [ex: 1,2,3,4,5 etc.]
paddle_num (winning paddle number of the lot. [ex: bidder with paddle number 112 won lot number 432 of this auciton]
price (price the lot sold for)
salepadnum (a combination of the salenum and paddle_num)

salenum (same as in the A_Lots table.)
paddle_num (paddle number of the absentee bidder in this table [NOTE: this is not the same as the paddle_num in the A_Lots table UNLESS the absentee bidder won that lot.]
sequence (same as in the A_Lots table)
salepadnum (salenum plus the paddle number of the absentee bidder [NOTE: not necessisarily the same as the A_Lots table unless absentee bidder won that lot.]
company_name (absentee user name)

A_Bidder: [NOTE: all absentee bidders are regeistered in this bidders table]
company_name (username of bidder)
paddle_num (paddle number of a particular bidder [ex: Joe Rogan is paddle_number 45 [NOTE: this is not always constant a user can have more than one number per auction :(]
salename (auction name same as in the A_Lot table)
salenum (auction number same as in other TWO TABLES)
cust_id (unique to customer in bidders table only so is of no use!)
salepadnum (bidder number plus auction number as in other tables)

What I want the final output is to list all of the Absentee bidders that bid on an item as well as all of the bidders who won the item. To do this I would assume that I could call;

SELECT fields WHERE a_absbid.salename LIKE a_lots.salename AND a_absbid.sequence = a_lots.sequence AND a_absbid <> a_lots.paddle_num (this piece would display ONLY the absentee bidders that DIDN'T win the auction lot because that will come from the BIDDERS table)

and then to select the bidders say; OR SELECT fields WHERE a_bidder.salepad = a_lots.salepadnum (lot winner) AND a_bidder.salenum = a_lots.salenum.

I would think that this would be easy yet I continues to baffle me. As you can see by the design of these tables and the in-proper use of column names and lack of keys that it is indeed a poorly designed system. I may not know that much about databases but I do know that having the same coulumn nams in multiple tables that are not related = bad :-X

Anyhow, if it's any use, I am trying to access this via an ASP webpage as well.
Yet again, any help is appreciated.


PostPosted: Visual FoxPro General, Trouble with Create View. Top

It looks like :

strSQL = & _
"Select allB.*, " & _
" al.sequence," & _
" al.paddleNum As lotPaddleNum," & _
" al.price, " & _
" al.salepadnum As lotSalePadNo," & _
" ab.paddleNum As absPaddleNum," & _
" ab.salepadnum As absSalePadNo," & _
" ab.company_name As absCompany " & _
" FROM a_Bidder allB " & _
" LEFT Join a_lots al ON allB.saleName == al.saleName And allB.saleNum == al.saleNum " & _
" LEFT Join A_Absbid ab ON allB.saleNum == ab.saleNum"

However you shouldn't expect me to understand plain English explanations. Someone whose mother tongue is English might understand better. To me it's some kind of horse races info but that's all.