[thelist] subselect query?

Dallas Cahker christmasfruitcake at gmail.com
Tue Mar 7 09:58:43 CST 2006


Okay good, now I can see the query is a bit off, I get count and I can see
the invoices in the other query.  When I check the invoice I see that its
off a bit where I can see that the invoice left area 7 and went into area 8,
but the query says its in 7.

On 3/7/06, Dan McCullough <dan.mccullough at gmail.com> wrote:
>
> 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 !
> >
> --
>
> * * 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