INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

build view

build view

(OP)
I'm using th efololowing SQL:

CODE

SELECT OESHDT.CUSTOMER, OESHDT.ITEM, OESHDT.YR, OESHDT.PERIOD, OESHDT.TRANDATE, OESHDT.DAYENDSEQ, OESHDT.TRANSSEQ, OESHDT.LINENO, OESHDT.TRANNUM, OESHDT.ORDDATE, OESHDT.SHIPDATE, OESHDT.SALESPER, OESHDT.QTYSOLD, OESHDT.SCSTSALES, OESHDT.FAMTSALES, OESHDT.SAMTSALES, OESHDT.FRETSALES, OESHDT.SRETSALES, OESHDT.PONUMBER, OESHDT.FAMTTRAN, OESHDT.SAMTTRAN, OESHDT.FAMTDISC, OESHDT.SAMTDISC, ARCUS.CUSTTYPE, OEINVH.TERMS, ARRTA.TEXTDESC, ICITEM.ALTSET, 'ICITEM.DESC', ICITEM.CATEGORY, ICITEM.CNTLACCT, ICITEM.STOCKITEM
FROM ICITEM INNER JOIN (((ARCUS INNER JOIN OESHDT ON ARCUS.IDCUST = OESHDT.CUSTOMER) INNER JOIN OEINVH ON OESHDT.ORDNUMBER = OEINVH.ORDNUMBER) INNER JOIN ARRTA ON ARCUS.CODETERM = ARRTA.CODETERM) ON ICITEM.ITEMNO = OESHDT.ITEM
ORDER BY OESHDT.CUSTOMER, OESHDT.SALESPER, OESHDT.PONUMBER
I run this query from Aqua Data Studio and ...well the query is still runnuing.......

I tried creating a view with this code:

CODE

Sub Create_View()
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    On Error GoTo ErrorHandler
    conn.Execute "CREATE VIEW vw_com AS "& above sql

ExitHere:
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
    End If
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    If Err.Number = -2147217900 Then
        conn.Execute "DROP VIEW vw_com"
        Resume
    Else
        Debug.Print Err.Number & ":" & Err.Description
        Resume ExitHere
    End If
End Sub
and I get an error...

The reason for trying to build a view is to cut down on the query time......(the query is still running from Aqua Data Studio over 10 minutes)

Any sugestions on this ?
 

RE: build view

I've never used Aqua Data Studio.  How long does the query take when running though the Pervasive Control Center or ODBC Test?  

What error do you get when creating the view?  

A quick look at the query shows that it might not be optimized and might be creating a temp file or performing a table scan (reading all of the records in all of the tables).  

I would suggest generating a Query Plan and then running the Query Plan Viewer to see what's happening.   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: build view

(OP)
Thank you mirtheil... I will try generating a Query Plan and then running the Query Plan Viewer...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close