Re: SQL Forms 3.0 and displaying linked list info?

In article <1992Apr17.141255.22345_at_casbah.acns.nwu.edu> irving_at_casbah.acns.nwu.edu (Thomas Myers) writes:
:In <1992Apr16.175704.23378_at_cas.org> lwv26_at_cas.org (Larry W. Virden) writes:
:
:>I am hoping to find a neat way to do the following.
 

:>I have an employee type database which has the following as a portion of
:>its information:
 

:>Employee ID Employee Name Employee Manager ID
 

:>100 John Doe 90
:>90 Charlie Brown 80
:>91 Alvin Chipmunk 70
:>80 Calvin Hobbes 70
:>70 Dick Tracy 10
:>10 Gary Larson 0
:
:[description of needing to build a form which lists an employee and all
:managers...]
:
:Try using a simple select with the CONNECT BY clause. Displaying this sort of
:hierarchical data is what it is designed to do.
:
:Look in you SQL language reference manual.

Thanks for the pointer. Several folks pointed me that direction. Unfortunately I cannot seem to get this to work in SQL Forms.

It works almost right in SQL Plus. That is, I can generate a select which will retrive a list of items from the same table in the order that I have in mind.

The problem in Sql Forms is that to fill a block with this data, the select appears to have to be specified within the Block's default where/order by field. And that block won't let you specify a connect/ start clause - it inserts the WHERE before anything you type in that field.

After reading the PL/SQl , SQL and SQL Forms manuals, I could find no other way to tell Forms that I wanted a select to be performed which was going to retrieve for me multiple rows and have that output go into a block. I can have a select retrieve ONE line of info and put that into a specific field - but I cannot seem to figure out a way to get multiple records.

Also, another 'neat' feature of connect/start - no JOINs are permitted. Thus, I cannot get things like titles and other things expanded in one select. Thus I need it appears a post-query trigger to fill the stuff in - once I figure out how to get Forms to do the right select.

The final frustration was in discovering that I do not seem to be able to define a Query only form. There has to be at least one base table column defined for input if I want Sql Forms to actual run the form!

I tried to simulate a Query only form by executing enter_query in the Key-Startup trigger. But this means that no other key triggers will be used (according to the doc, Enter Query enforces all the default values), AND few other types of triggers will work - pre-query appears to be called, but so far, it doesn't look like Post Query is being called.

This appears to be MUCH tougher than I thought it would be!

-- 
Larry W. Virden			UUCP: osu-cis!chemabs!lvirden
Same Mbox: BITNET: lwv26_at_cas    INET: lvirden_at_cas.org
Personal: 674 Falls Place,   Reynoldsburg,OH 43068-1614
America Online: lvirden