Query to find parts with no approved revision

This query may not yield correct results if you have multiple revisions to a part and the latest rev is approved and previous revisions are left unapproved. If anyone can figure out how to select the latest revision and then check to see if it is unapproved, then this could work. I am not sure how to get the max value (latest /most recent value) on the partrev table for a part.

---------------------------------
You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost.

[Non-text portions of this message have been removed]
It sounds so simple, but I guess my SQL skills aren't up to it.

Translate this to SQL:
select part.partnum from part where part.typecode = 'M' where no partrev record with approved = 1 exists.

Can anyone help me out?

I basically want a list of all the parts we make that don't have an approved revision. It's the fact that there's between 0 and infinite revisions possible for any part that's throwing me, I think.







____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Logically it should follow this;



Select Part.PartNum

From Part

Where (Part.Parttype = 'M') and (Part.PartNum Not IN(

(Select PartRev.PartNum

From PartRev

Where PartRev.Approved = "True")

))





Bruce Butler

<mailto:bbutler@...> bbutler@...



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Tony Hughes
Sent: Monday, March 10, 2008 5:48 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Query to find parts with no approved revision



It sounds so simple, but I guess my SQL skills aren't up to it.

Translate this to SQL:
select part.partnum from part where part.typecode = 'M' where no partrev
record with approved = 1 exists.

Can anyone help me out?

I basically want a list of all the parts we make that don't have an approved
revision. It's the fact that there's between 0 and infinite revisions
possible for any part that's throwing me, I think.

__________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ





[Non-text portions of this message have been removed]
>
> Logically it should follow this;
>
> Select Part.PartNum
>
> From Part
>
> Where (Part.Parttype = 'M') and (Part.PartNum Not IN(
>
> (Select PartRev.PartNum
>
> From PartRev
>
> Where PartRev.Approved = "True")
>
> ))
>

I believe that will give what is approved, no? Should it not be:

....

Where PartRev.Approved = False

But it gets a bit trickier if you want manufactured parts that don't have any
revision letters (which I would make a separate query.)

For extra credit, try solving this with a BAQ. ;-)

Mark W.
The nested SQL statement would return all Parts with approved revisions.
The top level statement is looking for parts not in that list.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Mark Wonsil
Sent: Tuesday, March 11, 2008 9:43 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Query to find parts with no approved revision



>
> Logically it should follow this;
>
> Select Part.PartNum
>
> From Part
>
> Where (Part.Parttype = 'M') and (Part.PartNum Not IN(
>
> (Select PartRev.PartNum
>
> From PartRev
>
> Where PartRev.Approved = "True")
>
> ))
>

I believe that will give what is approved, no? Should it not be:

....

Where PartRev.Approved = False

But it gets a bit trickier if you want manufactured parts that don't
have any
revision letters (which I would make a separate query.)

For extra credit, try solving this with a BAQ. ;-)

Mark W.





[Non-text portions of this message have been removed]
As for the extra credit.. Not sure, but perhaps use publish/subscribe
from two different BAQs within a dashboard.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Mark Wonsil
Sent: Tuesday, March 11, 2008 9:43 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Query to find parts with no approved revision



>
> Logically it should follow this;
>
> Select Part.PartNum
>
> From Part
>
> Where (Part.Parttype = 'M') and (Part.PartNum Not IN(
>
> (Select PartRev.PartNum
>
> From PartRev
>
> Where PartRev.Approved = "True")
>
> ))
>

I believe that will give what is approved, no? Should it not be:

....

Where PartRev.Approved = False

But it gets a bit trickier if you want manufactured parts that don't
have any
revision letters (which I would make a separate query.)

For extra credit, try solving this with a BAQ. ;-)

Mark W.





[Non-text portions of this message have been removed]
Bruce, thank you very much. That's exactly what I need, and I learned something valuable in the process. I appreciate it.

To Mark, no it's correct, the True is correct in this case because look at the beginning of it, where there's a "Not".
What we want is parts where there is NOT an approved revision. So the WHERE is saying limit my Part table rows to ones where the Part is not a part of the list returned by PartRev with approved revisions.

Or something like that. :)

again, thank you!



----- Original Message ----
From: Mark Wonsil <mark_wonsil@...>
To: vantage@yahoogroups.com
Sent: Tuesday, March 11, 2008 9:42:32 AM
Subject: RE: [Vantage] Query to find parts with no approved revision

>
> Logically it should follow this;
>
> Select Part.PartNum
>
> From Part
>
> Where (Part.Parttype = 'M') and (Part.PartNum Not IN(
>
> (Select PartRev.PartNum
>
> From PartRev
>
> Where PartRev.Approved = "True")
>
> ))
>

I believe that will give what is approved, no? Should it not be:

.....

Where PartRev.Approved = False

But it gets a bit trickier if you want manufactured parts that don't have any
revision letters (which I would make a separate query.)

For extra credit, try solving this with a BAQ. ;-)

Mark W.




<!--

#ygrp-mkp{
border:1px solid #d8d8d8;font-family:Arial;margin:14px 0px;padding:0px 14px;}
#ygrp-mkp hr{
border:1px solid #d8d8d8;}
#ygrp-mkp #hd{
color:#628c2a;font-size:85%;font-weight:bold;line-height:122%;margin:10px 0px;}
#ygrp-mkp #ads{
margin-bottom:10px;}
#ygrp-mkp .ad{
padding:0 0;}
#ygrp-mkp .ad a{
color:#0000ff;text-decoration:none;}
-->

<!--

#ygrp-sponsor #ygrp-lc{
font-family:Arial;}
#ygrp-sponsor #ygrp-lc #hd{
margin:10px 0px;font-weight:bold;font-size:78%;line-height:122%;}
#ygrp-sponsor #ygrp-lc .ad{
margin-bottom:10px;padding:0 0;}
-->

<!--

#ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;}
#ygrp-vitnav{
padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
#ygrp-vitnav a{
padding:0 1px;}
#ygrp-actbar{
clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
#ygrp-actbar .left{
float:left;white-space:nowrap;}
..bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;font-size:77%;padding:15px 0;}
#ygrp-ft{
font-family:verdana;font-size:77%;border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;}

#ygrp-vital{
background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
#ygrp-vital #vithd{
font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
#ygrp-vital ul{
padding:0;margin:2px 0;}
#ygrp-vital ul li{
list-style-type:none;clear:both;border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
#ygrp-vital ul li .cat{
font-weight:bold;}
#ygrp-vital a{
text-decoration:none;}

#ygrp-vital a:hover{
text-decoration:underline;}

#ygrp-sponsor #hd{
color:#999;font-size:77%;}
#ygrp-sponsor #ov{
padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;margin:0;}
#ygrp-sponsor #ov li{
list-style-type:square;padding:6px 0;font-size:77%;}
#ygrp-sponsor #ov li a{
text-decoration:none;font-size:130%;}
#ygrp-sponsor #nc{
background-color:#eee;margin-bottom:20px;padding:0 8px;}
#ygrp-sponsor .ad{
padding:8px 0;}
#ygrp-sponsor .ad #hd1{
font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
#ygrp-sponsor .ad a{
text-decoration:none;}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;}
#ygrp-sponsor .ad p{
margin:0;}
o{font-size:0;}
..MsoNormal{
margin:0 0 0 0;}
#ygrp-text tt{
font-size:120%;}
blockquote{margin:0 0 0 4px;}
..replbq{margin:4;}
-->






____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping

[Non-text portions of this message have been removed]
> To Mark, no it's correct, the True is correct in this case because look at
> the beginning of it, where there's a "Not".

How did I not see that? I should not know better to not not agree with Bruce
without not knowing not conditions that are not double-negatives which are
usually not missing in SQL statements...

This message is giving me (and the rest of you) knots in my stomach...