NOT EXISTS syntax? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

NOT EXISTS syntax?

Author

Topic

geobeck
Starting Member

10 Posts

Posted - 2005-09-06 : 14:16:23

I'm quite new to SQL, but because I'm "good with computers" I've been handed the task of creating reports from our ERP database.

I'm trying to write a report that will show all parts that have not had material transactions within a certain time. The only way I can figure out to do this is to use a NOT EXISTS statement in a subquery, something like this:

SELECT Part.PartNum, Part.PartDecription FROM Part
WHERE Part.Company = "Production"
AND NOT EXISTS (SELECT Part.Partnum FROM Part
WHERE PartTran.TranDate BETWEEN 01/01/2004 AND 01/01/2005);

Can someone help me with my syntax?

Kristen
Test

22859 Posts

Posted - 2005-09-06 : 14:29:07

Hi geobeck, Welcome to SQL Team!

Not sure what the name of your Part Transaction file is, but you probably need something like:


SELECT Part.PartNum, Part.PartDecription 
FROM Part
WHERE Part.Company = "Production"
AND NOT EXISTS 
(
    SELECT *
    FROM PartTran
    WHERE     [red]PartTran.PartNum = Part.PartNum[red]
          AND PartTran.TranDate BETWEEN '20040101' AND '20050101'
)

This will include items which have a transaction with a date (and NO time) of exactly 01-01-2005, and thus you might want to do:


    WHERE     PartTran.TranDate >= '20040101' 
          AND PartTran.TranDate <  '20050101'

Kristen

geobeck
Starting Member

10 Posts

Posted - 2005-09-06 : 16:47:01

Thanks for the quick reply!

I've successfully run the corrected query using the SQL Explorer tool included with our ERP system (it's a SQL verification tool that returns a limited number of results). Unfortunately, whenever I try to insert it into a reporting tool (either Crystal Reports or Open Office), I get syntax errors. Open Office seems to want to insert extra quotation marks all over the place. Crystal doesn't give me any clues as to what is wrong.

Does anyone here have experience adapting SQL queries to Crystal, Open Office, or Excel?

geobeck
Starting Member

10 Posts

Posted - 2005-09-06 : 17:20:37

It worked!

w00t for Open Office! A whole bunch of brackets and quotation marks later, the query actually ran, giving us a whole bunch of part numbers we can probably kill from our bloated database.

Thanks again, Kristen.

geobeck
Starting Member

10 Posts

Posted - 2005-09-06 : 19:15:50

{groan} More problems:

I needed to add another filter to this report so it lists only parts that are in inventory, and have not had any transactions in the specified time. I tried this:

select Part.PartNum, Part.PartDescription
from PUB.Part
where
Part.PartNum = PartBin.PartNum
and PartBin.OnhandQty <> 0
and
Part.Company = 'VPVCPROD'
and not exists
(
select *
from PUB.PartTran
where PartTran.PartNum = Part.PartNum
and PartTran.TranDate between '2004-09-01' and '2005-08-26'
);

(new parts in red), and now I get an exception error. Have I confused my DB driver?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-07 : 00:24:27

Post the error. What version/service pack of OS and SQL Server are you using? Also, what kind of a call is this (ODBC, OLE, etc)? What is the version of your MDAC?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-09-07 : 02:27:17

Looks like you haven't included the PartBin table in the mix (using a JOIN):


select Part.PartNum, Part.PartDescription
from PUB.Part
     JOIN PartBin
          ON PartBin.PartNum = Part.PartNum
WHERE     PartBin.OnhandQty <> 0
      and Part.Company = 'VPVCPROD'
      and not exists
      (
          select * 
          from PUB.PartTran
          where     PartTran.PartNum = Part.PartNum
                and PartTran.TranDate between '2004-09-01' and '2005-08-26'
)

Kristen

geobeck
Starting Member

10 Posts

Posted - 2005-09-07 : 19:14:19

Finally got this working. The exception error was my fault; not connecting properly in the SQL Explorer (a tool that came with our ERP system DB).

Open Office handles the JOIN kind of funny. Here's the final statement, with line breaks and indents added for clarity (quotation marks and brackets added by Open Office):

SELECT "Part"."PartNum" AS "Part Number", "Part"."PartDescription" AS "Description"
FROM "PUB"."PartBin" "PartBin", "PUB"."Part" "Part"
WHERE ( "PartBin"."PartNum" = "Part"."PartNum" )
AND ( ( "PartBin"."OnhandQty" > 0
AND "Part"."Company" = 'VPVCPROD' ) )
AND NOT EXISTS
(
SELECT * FROM "PUB"."PartTran"
WHERE ("PartTran"."PartNum" = "Part"."PartNum")
AND ("PartTran"."TranDate" BETWEEN '2004-10-01' AND '2005-08-26')
)
ORDER BY "Part Number" ASC

I finally got the idea to move the On Hand Quantity statement ahead of the Company identifier to avoid a lock table overflow; that reduced the results more in the first step.

Thanks again for your help. I'm sure I'll be back here the next time my boss wants a report on how many parts we sold on the second Tuesday after each new moon or something like that.

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 06:08:20

Yup, that's basically the same as the JOIN I was using.

I think I heard that the "Join Style" of

FROM Table1, Table2
WHERE Table1.SomeColumn = Table2.SomeColumn

will not be available in SQL 2005 ...

Kristen

geobeck
Starting Member

10 Posts

Posted - 2005-09-21 : 17:49:43

Back again. Kristen, you there?

We discovered a problem with this query when we ran it to find parts that hadn't moved in the previous year. We create new parts all the time, so if we run the query to find parts that had no transactions in FY 2003, it includes parts that were created more recently. Of course, they had no transactions before they were created.

I've tried adding an ...AND EXISTS subquery to make sure the query only returns parts that had transactions before the period of interest, but now I get an "inconsistent types" error. Is it not possible to have an EXISTS and a NOT EXISTS subquery in the same query?

Full query follows; line breaks added for clarity, new part in red:

SELECT "Part"."PartNum" AS "Part Number", "Part"."PartDescription" AS "Description", "PartBin"."OnhandQty" AS "On Hand Qty", "Part"."AvgLaborCost" AS "Avg Labor Cost", "Part"."AvgBurdenCost" AS "Avg Burden Cost", "Part"."AvgMaterialCost" AS "Avg Matl Cost"
FROM "PUB"."PartWhse" "PartWhse", "PUB"."Part" "Part", "PUB"."PartBin" "PartBin"
WHERE ( "PartWhse"."PartNum" = "Part"."PartNum"
AND "PartWhse"."Company" = "Part"."Company"
AND "PartBin"."Company" = "Part"."Company"
AND "PartBin"."PartNum" = "Part"."PartNum" )
AND ( ( "PartBin"."OnhandQty" > 0
AND "Part"."Company" = 'VPVCPROD'
AND "PartWhse"."WarehouseCode" = 'PRO' ) )
AND NOT EXISTS
(
SELECT * FROM "PUB"."PartTran"
WHERE ("PartTran"."PartNum" = "Part"."PartNum")
AND ("PartTran"."TranDate" BETWEEN '2004-10-01' AND '2005-08-26')
)
AND EXISTS
(
SELECT * FROM "PUB"."PartTran"
WHERE ("PartTran"."PartNum" = "Part"."PartNum")
AND ("PartTran"."TranDate" BETWEEN '2000-01-01' AND 2004-09-30')
)
ORDER BY "Part Number" ASC

Kristen
Test

22859 Posts

Posted - 2005-09-21 : 18:02:48

There's a missing single quote mark - is that just an error in cut and paste to here, or actually in your original?

AND ("PartTran"."TranDate" BETWEEN '2000-01-01' AND '2004-09-30')

Kristen

geobeck
Starting Member

10 Posts

Posted - 2005-09-21 : 18:36:33

LOL! Query killed by typo!

Fixing that got rid of the "inconsistent types" error. Unfortunately...

"Lock table is full"

Gaah! If it's not one thing it's another. Any clues as to how to reduce the number of locks this query uses? The lock table value is set at ~120,000.

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 01:34:23

"Lock table is full"

I don't reckon your database is "SQL Server", is that right?

If so what is it? - 'coz the locks should have been escalated to a table lock, rather than running out of lock table resource!

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-22 : 23:12:16

What version/service pack of OS and SQL Server are you using? Also, what kind of a call is this (ODBC, OLE, etc)? What is the version of your MDAC?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

geobeck
Starting Member

10 Posts

Posted - 2005-09-23 : 11:33:35

You're right; I'm not running SQL Server. I know these forums are only for MS SQL Server, but they're the best resource I've found for SQL syntax help!

I'm querying a Progress 9.1d database through a Data Direct Technologies ODBC driver. SQL 92 is the standard. I'm looking into my lock table problems, but would appreciate any general suggestions on how my query could be made less cumbersome.

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 14:09:49

I reckon your query is spot on.

And I reckon your Progress 9.1d is pants if it won't escalate a large number of record/page locks to a table lock!

Maybe there is a "locking hint" you can give to the database to tell it to use a table lock from the get-go?

Kinda naughty to give Lock Hints rather than letting the query planner have a free-rein, but if its doing it badly I suppose then its "okay"

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-25 : 18:43:28

Progress is such a piece of crap. They should have just left the stupid accounting database as BTrieve if they were going to use that. List the data types for each of the columns in the WHERE clause of the NOT EXISTS statement. Also, are you still getting the first error, or is this the only one you have now?

Also, doesn't that version of Progress support the "java stored procedures"? If so, have you considered putting the code in there and seeing how it's handled by your programs?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

geobeck
Starting Member

10 Posts

Posted - 2005-09-26 : 11:44:53

Quote: "Progress is such a piece of crap."

Nice to know my general opinion of Progress is shared by at least one DBA! Somehow, my desktop icon for Vantage (the ERP system that sits on top of our Progress DB) got changed to DisadVantage 6.1, and I haven't found any reason to change it back. ;-)

Anyway, these are the data types in my NOT EXISTS subquery:

PartNum = char x(20)
TranDate = date 99/99/99

As far as java stored procedures are concerned, I guess I'll have to read up on that. Like I said in my first post, I got wrangled into doing this just because I'm "good with computers", not because I have any actual training.

Thanks again.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-26 : 21:31:58

:) Read the tutorial on how to create them. You can find it on the website. It's actually a decent tutorial. I'm sooooooo glad I don't have to mess with that stupid thing anymore. Locking escalation has never been one of their strengths btw. They had scalability issues back in the 90's and I haven't seen that much improvement. There are a lot of idiotic, die-hard progress fans out there though. The product belongs in the dumpster.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

geobeck
Starting Member

10 Posts

Posted - 2005-09-27 : 11:15:10

I know what you mean. Everyone here hates Progress/Vantage, from the end users who have to deal with the annoying quirks of the UI, to the managers who look at the dozens of available reports, none of which show them what they want (hence my current tasks), to the financial controller who wishes we had stuck with Agama (now Encore/Syspro).

But head office is of the opinion that we've thrown so much money down this hole, we can't abandon it. I guess they figure that if we keep throwing more money down the hole, eventually the Progress Fairy will throw some back.

Kristen
Test

22859 Posts

Posted - 2005-09-27 : 12:09:38

Progress is for Fairies?

No wonder real DBAs don't use it ...

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources