Data Shaping

Results 1 to 5 of 5

Thread: Data Shaping

  1. #1
    Darren Hoefgen Guest

    Default Data Shaping

    Ok. I&#039ve read the 2 articles on data shaping, and I have tried this at home, and it doesn&#039t work.<BR><BR>Using SQL 7.0 I enter the folowing in the query analyzer:<BR><BR>SHAPE { SELECT account_id FROM account_tbl }<BR>APPEND {(SELECT employee_id FROM account_manager_tbl where account_manager_type_id = 1) lead<BR> RELATE account_id to account_id},<BR> {(SELECT employee_id FROM account_manager_tbl where account_manager_type_id = 2) sales<BR> RELATE account_id to account_id},<BR> {(SELECT employee_id FROM account_manager_tbl where account_manager_type_id = 3) manager<BR> RELATE account_id to account_id}<BR><BR>And I get:<BR>[Microsoft][ODBC SQL Server Driver]Syntax error or access violation<BR><BR>I can run other SQL statements just fine. <BR><BR>The account_id is the primary key in account_tbl and is FK&#039d in the account_manager_tbl. <BR><BR>Another interesting note -- the word "SHAPE" doesn&#039t change to blue in the query analyzer as do all the other SQL commands...<BR><BR>Any help would be appreciated..<BR><BR>Thanks!!

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Data Shaping

    Your braces and parenthesis are all mixed up.<BR><BR>SHAPE { SQL statement }<BR>APPEND ( { child SQL Statement }<BR>RELATE column TO child column )<BR><BR>Hope this helps!

  3. #3
    Darren Hoefgen Guest

    Default RE: Data Shaping

    I&#039ve tried them both ways -- <BR>SHAPE { SELECT account_id FROM account_tbl }<BR>APPEND ({SELECT employee_id FROM account_manager_tbl where account_manager_type_id = 1} lead<BR> RELATE account_id to account_id),<BR> ({SELECT employee_id FROM account_manager_tbl where account_manager_type_id = 2} sales<BR> RELATE account_id to account_id),<BR> ({SELECT employee_id FROM account_manager_tbl where account_manager_type_id = 3} manager<BR> RELATE account_id to account_id)<BR><BR>I just cut and pasted the wrong one. But switching the braces and parens didn&#039t change a thing..<BR><BR>thanks though!

  4. #4
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Data Shaping

    You&#039ve got to include the column you&#039re going to relate in both the child and parent SQL statements:<BR><BR>SHAPE { SELECT account_id FROM account_tbl }<BR>APPEND ({SELECT employee_id, account_id FROM account_manager_tbl where account_manager_type_id = 1} lead<BR>RELATE account_id to account_id),<BR><BR>Note that I added account_id in the SELECT list of the child SQL statement...


  5. #5
    Darren Hoefgen Guest

    Default RE: Data Shaping -- PROBLEM SOLVED

    First, you have to set your connection object to :<BR>Conn.Provider = "MSDataShape"<BR><BR>then I changed my code to:<BR><BR> SQL = "SHAPE { SELECT a.account_id, a.username FROM account_tbl a where a.username &#060;&#062; &#039&#039 AND username is not null} " & _<BR> "APPEND ({SELECT e.account_id,emp.fname, emp.lname FROM account_manager_tbl e JOIN employee_tbl emp ON emp.employee_id = e.employee_id} lead " & _<BR> "RELATE account_id to account_id)"<BR> <BR> &#039 Response.Write sql<BR> RS.Open sql, conn, adOpenForwardOnly,adLockReadOnly<BR> do while not RS.eof<BR> i = i + 1<BR> Response.Write rs("username") & vbcrlf<BR> set objempRS = rs("lead").value<BR> do while not objempRS.eof <BR> Response.Write objemprs("fname") & " " & objemprs("lname") & vbcrlf <BR> objemprs.movenext<BR> loop<BR> objemprs.close<BR> Response.Write "<BR>"<BR> <BR> RS.MoveNext<BR> loop<BR> RS.Close<BR> set rs = nothing<BR> set conn = nothing<BR> set objemprs = nothing<BR> %&#062;<BR><BR>not I can easily produce one line with data from multiple related tables -- this is great!<BR><BR>thanks everyone!<BR><BR>ok, now how do I give this query to the CEO to run in excel -- solve that one<BR><BR>:)<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •