Nested SQL into Cursor

[mccartmd]

Hi:
Struggling to Nest, 3 queries to same "Date_Recorded" into a new CURSOR vProducts
I have these Selects. . .which query fine for each, but would like to nest into CURSOR.
(SELECT Product_6A.date_recorded,Product_6A.Symbol_1 FROM Product_6A)
(SELECT Product_6B.date_recorded,Product_6B.Symbol_2 FROM Product_6B)
(SELECT Product_6C.date_recorded,Product_6C.Symbol_3 FROM Product_6C)
but am struggling with the FROM JOIN IN "nested" into Cursor vProducts
Appreciate any assistance in advance.
THX
Mike

 

[Mike Lewis]

In order to join these tables, we need to know how they are related. Tell us which fields provide the links, and we'll help with the code.

If the tables are not related, then you need a UNION. Something like this:

SELECT Product_6A.date_recorded,Product_6A.Symbol_1 FROM Product_6A ;
[b]UNION[/b] SELECT Product_6B.date_recorded,Product_6B.Symbol_2 FROM Product_6B ;
[b]UNION[/b] SELECT Product_6C.date_recorded,Product_6C.Symbol_3 FROM Product_6C ;
INTO CURSOR vProducts

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mccartmd]

Here is my Cursors as far as I got. . .
trying to join the two by date_recorded
. . .all from the same "Prices table
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SELECT Products.symbol AS symbol_1,;
Prices.date_recorded AS date_recorded, Prices.last AS lastprice_1;
FROM ;
market!products ;
LEFT OUTER JOIN market!product_trading ;
ON Products.unique_id = Product_trading.product_id ;
LEFT OUTER JOIN market!prices ;
ON Prices.product_trading_id = Product_trading.product_id;
WHERE Products.symbol = ( "6A" ) INTO Cursor Product_6A
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SELECT Products.symbol AS symbol_2,;
Prices.date_recorded AS date_recorded, Prices.last AS lastprice_2;
FROM ;
market!products ;
LEFT OUTER JOIN market!product_trading ;
ON Products.unique_id = Product_trading.product_id ;
LEFT OUTER JOIN market!prices ;
ON Prices.product_trading_id = Product_trading.product_id;
WHERE Products.symbol = ( "6C" )INTO CURSOR Product_6C
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

 

[Mike Lewis]

Not clear what your question is.

In your first message, you showed three separate SELECTs, each from a different table, with no WHERE clause, and with no indication of any relationship between them.

In your second post, you are showing two SELECTs, both apparently based on the same joins, but with a different WHERE clauses, and sending the results to different cursors.

The heading of the thread suggests that you want to know how use the INTO CURSOR clause, yet you clearly know how to do that.

I think I'll let someone sharper than me handle this, unless you can clarify what you want to know.

By the way, your code will be very much clearer if you could wrap it in [ignore]

[/ignore] tags.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

[url=http://www.ml-consult.co.uk]Visual FoxPro articles, tips, training, consultancy[/url]

 

[TamarGranor]

I think you're saying that you want to put all three results into a single cursor. In this case, the easiest thing to do would be to do a single query with a more complex WHERE clause:

SELECT Products.symbol AS symbol_1,;
Prices.date_recorded AS date_recorded, Prices.last AS lastprice_1;
FROM ;
market!products ;
LEFT OUTER JOIN market!product_trading ;
ON Products.unique_id = Product_trading.product_id ;
LEFT OUTER JOIN market!prices ;
ON Prices.product_trading_id = Product_trading.product_id;
WHERE INLIST(Products.symbol, "6A", "6B", "6C")
INTO Cursor Product_6A

Tamar

 

[mccartmd]

Hey Tamar, Thanks for the feedback. . .
This gives a long list of the three items with Symbol, date_recorded and Price. . .I have this already. . .what I am trying to do is separate by Symbol, into a Cursor 9 field wide, nested beside each other.These values are in "price.dbf table and each are given the same date_recorded.
What I would like to do is to take the three put them side by side so I can do multiple regression analysis between them into a readwrite Cursor.
Symbol_1 date_recorded price, Symbol_2 date_recorded price, Symbol_3 date_recorded price
into a SQL compounded "Nested" . . .with relationship connected by date_recorded.
(found your "Using VFP's SQL Commands in Visual FoxPro9") very good stuff, if I could get my head around it. . .

 

[Olaf Doschke]

mccartmd,

what hinders you to do calculations (multiple regression analysis), if the data is not in one row?
eg you can SELECT into an ARRAY and then do your calculations on array elements, as you can address the rows of an array as you like in some formula. Instead of symbol_1, symbol_2 you'll have arr[1][1] and arr[2][1].

If you still want to left join produccts data you will simply need self joins:
Select p1.symbol as symbol1, p2symbol as symbol2, .... FROM products as p1 LEFT JOIN products as p2 on p2.Symbol="6B" LEFT JOIN products as p3 on p3.Symbol = "6C"

Arrays are better in this situation for general code working with N products. Disadvantage of creating a single row of data here is, you have hardcoded field names instead of an element index of an array.

So I suggest you take Tamars code and instead of INTO Cursor Product_6A you select INTO ARRAY laProducts.

Bye, Olaf.

 

[Dan Freeman]

I think what you really want is a crosstab.

After you've run Tamar's query

DO (_VFPXTAB)

and work with the resulting cursor.