Nested SHAPE statements

[Johnny101]

Does anyone know if you can nest SHAPE commands. It would really be nice if I could show a grandparent/parent/child relationship.

here is my SQL (for the pubs database):

SHAPE {
SELECT
Pub_ID,
pub_name 'Publisher',
City,
State
FROM
pubs..Publishers
} APPEND ({
SHAPE {
SELECT
t.Pub_ID,
t.Title_ID,
t.Title,
t.pubdate 'Published'
FROM
pubs..Titles t (nolock)
} APPEND ({
SELECT
st.stor_name 'Store Name',
s.Title_ID
FROM
pubs..stores st (nolock),
pubs..sales s (nolock)
WHERE
st.stor_id = s.stor_id
}
RELATE Title_ID to Title_ID)
as rsBooksPerStore
} RELATE Pub_ID to Pub_ID)
as rsBookStores




the inner shape works just fine, but when I try to open the whole thing (using native ADO - no controls or anything) I get a "-2147217900 - Syntax error or access violation" error. The idea behind this sql is, I want the list of publishers, the books that each publisher publishes and the stores which carry those books. I know I can get this list using plain joins and such, but I'm trying to nail this data shaping stuff to the wall.

here is my code for opening the recordset:

Dim cn as ADODB.Connection
Dim rsMaster as ADODB.Recordset
Dim rsChild as ADODB.Recordset
Dim sqlShape as string
Dim sqlPubs as string
Dim sqlStores as string
Dim sqlTitles as string
Dim sqlSubShape as string

sqlPubs = "SELECT Pub_ID, pub_name 'Publisher', City, State "
sqlPubs = sqlPubs & "FROM pubs..Publishers"

sqlTitles = "SELECT t.Pub_ID, t.Title_ID, t.Title, t.pubdate 'Published' FROM pubs..Titles t (nolock)"

sqlStores = "SELECT st.stor_name 'Store Name', s.Title_ID "
sqlStores = sqlStores & "FROM pubs..stores st (nolock), "
sqlStores = sqlStores & "pubs..sales s (nolock) "
sqlStores = sqlStores & "WHERE st.stor_id = s.stor_id"

sqlSubShape = "SHAPE {" & sqlTitles & "} APPEND ({" & sqlStores & "} "
sqlSubShape = sqlSubShape & "RELATE Title_ID to Title_ID) as rsBooks"

sqlShape = "SHAPE {" & sqlPubs & "} "
sqlShape = sqlShape & "APPEND ({" & sqlSubShape & "} "
sqlShape = sqlShape & "RELATE Pub_ID to Pub_ID) as rsBookEmps"

set cn = new ADODB.Connection

With cn
.CursorLocation = adUseClient
.ConnectionString = "Provider=MSDataShape;Data Provider=MSDASQL;Data Source=SLCLASM06T;User Id=sa;Password=;Database=Pubs;"
.Open
End With

set rsMaster = new ADODB.Recordset
set rsMaster = cn.Execute(sqlShape)

End Sub




I'm using ADO 2.1, SQL Server 6.5 SP5, VB6 SP3, and WinNT Wkst 4 SP4

If anyone knows of a way to do this, or if this can not be done for sure, I would appreciate it.

thanks,

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Lothar Haensler]

here is my idea:
SHAPE {select * from publishers} AS publishers APPEND (( SHAPE {select * from titles} AS books APPEND ({select * from sales} AS stores RELATE 'title_id' TO 'title_id') AS stores) AS books RELATE 'pub_id' TO 'pub_id') AS books

I didn't get any syntax errors.
I think, some of the tables you mentioned don't make sense.
You need to join publishers, books and sales IMHO.

[Lothar Haensler]

...actually, this final version is better:
SHAPE {select * from publishers} AS publ APPEND (( SHAPE {select * from titles} AS titles APPEND ({select * from sales} AS sales RELATE 'title_id' TO 'title_id') AS sales) AS titles RELATE 'pub_id' TO 'pub_id') AS titles

I tested the output with a Hierarchical Flexgrid...

[Johnny101]

I was wondering whether or not an extra set of parens were needed - apparently they were.

Thank you!

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Lothar Haensler]

the best way to find the "right" syntax is, add a data environment to your project and construct the hierarchy in the DE. Then, when you are finished just right-click on the top-level command and view the "hierarchy properties" to get the complete ADO shape command.
Copy and paste it in your project if you don't want to use the DE.

[Johnny101]

I almost never use the DE, so I wasn't aware that it could handle hierarchical rs's. I do use to build connectionstrings to unfamiliar data sources, though.

thanks for the tip.

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org