Conditional Query Runs Forever

[Corr]

Hello,

The following query I'm trying to run takes a really long time. I waited 45 mins and gave up. I need this to run and return the results as quick as possible.

The SCACCT Table contains approximately 1.6 Million Records and the SCPAYM Table contains approx. 150,000 records.

Here is the query I am running.

SELECT gacct#.scacct, glast.scacct, gfirst.scacct, pdate.scpaym, payamt.scpaym, pbalaf.scpaym, gpri.scacct, gnt.scacct
FROM scdata/scpaym, scdata/scacct
INNER JOIN pguar#.scpaym=gacct#.scacct
WHERE PDATE.scpaym>=[&DateFrom] AND PDATE.scpaym<=[&DateTo]

I am using a program called ViewPoint for the AS400, but I understand the Windows version of SQL better. So if anyone can help me find a way to speed this up I would appreciate it. If you give me Windows SQL sever code, I will change it around so it works in ViewPoint. I just need some guidance.


Thanks,

John

 

[SQLBill]

You really should post in a forum dedicated to what you are actually using. SQL Server's TSQL language is not the same as other SQL.

Some issues however, you have two "somethings" in the FROM. Are those database/table? If so, you can only have one table in the FROM, the others need to be JOINed (or all of them need to be in the FROM). Your database/table definitions are not the same everywhere....in the FROM you use a slant (/) and in the INNER JOIN you use a period(.).

Do you have indexes on the columns?

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

I am using a program called ViewPoint for the AS400, but I understand the Windows version of SQL better
...
If you give me Windows SQL sever code, I will change it around so it works in ViewPoint.

Since you understand Windows SQL Server code so well, then maybe you could translate your above code into Windows SQL for us so we could actually answer your question. What you posted isn't any form of T-SQL I've ever seen.

Barring that, you might want to find a forum that uses Viewpoint and ask the gurus there.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Corr]

Well I was hoping that someone would have been able to post a query as it would show in Windows SQL so that I could see the format or sytax. From there I would have probably figured out how it goes in the program I was using. I understand it wasn't the most proper forum to post in, but those AS400 people confuse me entirely. However I have figured out my problem with a little help from ViewPoint Technical Support and do appreciate your responses to my issue.

Thanks,

John

 

[SQLBill]

The problem with us being able to post a SQL Server query based on the information, is that first we would have to understand the information you provided. As I had posted, the formats didn't make sense to me.

-SQLBill

Posting advice: FAQ481-4875