Indexed or Seq? optional - outer joins

[Joe Boyle]

and if you want to get the equivalent of an optional link you can use outer
joins as below,


sql in mydb declare mydbquery cursor for     &
    select orders.order_no  ,order_lines.order_no ,sales_id,   &
        order_lineno  from  orders left outer join order_lines   &
        on orders.order_no = order_lines.order_no                 &
        where (sales_id between 40093367 and 41693367)           &
        order by orders.order_no , order_lineno 

 access  mydbquery

 sorted on order_no, order_lineno
 
 foo at order_no order_no order_lineno maximum count

Regards, Joe.



-----Original Message-----
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Darren Reely
Sent: 12 August 2005 02:39
To: powerh-l at lists.sowder.com
Cc: fernando.olmos at hpa.com.au
Subject: Re: Indexed or Seq?

Fernando,

I have used all three methods Conrad mentioned. Stored procedures for 
passing and returning variables, but not row sets. Views look just like 
any table. I use cursors in special one-shot items or in one case where 
a problem (dynamically floating user preferred data to top of a cluster) 
couldn't be solved any other way in Powerhouse.

You need to be aware that Powerhouse will not let you use Oracle 
specific functions such as DECODE(), TO_CHAR(), etc, in cursors . Even 
with this BIG annoyance, cursors can still give you advantages.


Here is your ACCESS statement converted to use a cursor. If I wrote it 
properly, you should be able to paste it into a Quiz session.

sql in hisdb  &
declare my_invoices  &
cursor for  &
;; NOTE: Selecting ALL (*) may impact performance.
select im.* ,id.*  &
from invoice_master im  &
	,invoice_detail id  &
where im.order_no = id.order_no

access my_invoices
choose order_no parm prompt "enter order number: "
report  order_no of my_invoices  product_code of my_invoices


Good luck with your optimizations.

Darren


Whittall, Conrad wrote:
> Fernando,
>  
> I believe the answer to your questions is Yes and Yes...with PowerHouse 
> 8 you can do either -- embed a declared cursor in your PH code, or 
> create a view or stored procedure in the database and have PH access 
> that. You can find all the details in the PowerHouse Series 8 Books, 
> available online from the PowerHouse site...
>  
>     -----Original Message-----
>     *From:* Fernando Olmos
>     *Sent:* Friday, August 12, 2005 8:24 AM
>     *To:* powerh-l at lists.sowder.com
>     *Subject:* RE: Indexed or Seq?
> 
>     Bob and Joe,
>      
>     Thank you both for the input. I fully understand what's going on.
>     Just one more question... Bob, are you saying to declare the cursor
>     using SQL or is there a way to do this in QUIZ and have it embedded
>     into the BUILD of the QUIZ program? OR can the cursor be declared at
>     the database level and accessed via QUIZ? A view? Sorry, but I am
>     not that familiar with Oracle. I know in Oracle RDB, from the VAX,
>     we were able to "stream-line" queries with cursors, but only using
>     embedded SQL within COBOL. I have never seen cursors been used from
>     within Powerhouse.
>      
>     Cheers
-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.