[thelist] subselect query?

Dan McCullough dan.mccullough at gmail.com
Tue Mar 7 09:35:38 CST 2006


take out the group by and add in B.\"RDORD#\" for the select statement.

SELECT B.RDSTAT, B.\"RDORD#\" FROM ORPRDETL A, ORPRDETL B WHERE
A.RDSTAT = 200 AND B.\"RDORD#\" = A.\"RDORD#\" AND B.RDENDX =
'2006-02-27' AND B.RDENDX <> '1940-01-01' AND B.RDSTAT = (SELECT MAX(
C.RDSTAT) FROM ORPRDETL C WHERE C.\"RDORD#\" = B.\"RDORD#\" AND
C.RDENDX <> '1940-01-01') ORDER BY B.\"RDORD#\"

On 3/7/06, Dallas Cahker <christmasfruitcake at gmail.com> wrote:
> Okay this is where I am at.
>
> SELECT B.RDSTAT, COUNT(*) FROM ORPRDETL A, ORPRDETL B WHERE A.RDSTAT = 200
> AND B.\"RDORD#\" = A.\"RDORD#\" AND B.RDENDX = '2006-02-27' AND B.RDENDX <>
> '1940-01-01' AND B.RDSTAT = (SELECT MAX( C.RDSTAT) FROM ORPRDETL C WHERE
> C.\"RDORD#\" = B.\"RDORD#\" AND C.RDENDX <> '1940-01-01') GROUP BY B.RDSTAT
>
> The numbers that come from this look plausable, however I dont get invoice
> numbers so I cannot confirm the numbers by looking up the invoices, any
> help?  Also a couple of stations are missing, I believe it might be because
> they have 0 count so they are getting printed, any thoughts on that?
>
>  On 3/6/06, EasyListBox.com Peter Brunone <peter at easylistbox.com> wrote:
> > >
> > > Hi Dallas,
> > >
> > >    Can you tell us (a) what kind of DBMS you are using and (b) the
> > > structure of the tables involved?
> > >
> > > Cheers,
> > >
> > > Peter
> > >
> > > From: "Dallas Cahker" < christmasfruitcake at gmail.com>
> > > Sent: Monday, March 06, 2006 12:47 PM
> > > To: thelist at lists.evolt.org
> > > Subject: [thelist] subselect query?
> > >
> > > I have a problem. My problem is this. I have a database of scans, the
> > > scans are linked together by an invoice number, there might be 8-12
> > > scans
> > > per invoice, I need to get the invoice number of the scan that left area
> > > 20
> > > on a certain date, after that I need to get the area it is currently in.
> > >
> > > Can someone help me with getting the subselect correct for that, or is
> > > there
> > > a better way to do it, even if it is 650,000 records.
> > >
> > > SELECT B.RDSTAT, COUNT(*) FROM ORPRDETL A, ORPRDETL B WHERE A.RDSTAT =
> > > 200
> > > AND B.\"RDORD#\" = A.\"RDORD#\" AND B.RDENDX = '2006-02-27' AND B.RDSTRX<>
> > > '1940-01-01' AND B.RDSTAT = (SELECT MAX(C.RDSTAT) FROM ORPRDETL C WHERE
> > > C.\"RDORD#\" = B.\"RDORD#\") GROUP BY B.RDSTAT - I dont get any invoice
> > > numbers so I cant do any checks to see if the data is accurate.
> > >
> >
> >
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



More information about the thelist mailing list