Query Help

[iceman42]

I'm running a query and I need some help going through the logic. First off the data is organized by an invoice number, however the invoice number is not important in the query, the query is looking for the current location of an item in a certain route that started on x date.

So lets say that invoice #456ASD is at area 7 and left the starting point which is area 3, notice not area 1, on 1/31/06.

So I need to get a count of all the invoices that left area 3 on 1/31/06, and then break down the count to which area they are in along the way. 4 are in area 4, 16 are in area 5 and so on.

 

[MarcLodge]

Hi Iceman42,

What SQL have you come up with so far? Show us what you are thinking, and we can help nudge you in the right direction. Don't worry if you are having problems with it, that's what we're here for.

Marc

 

[iceman42]

SELECT COUNT(*) FROM \"ORTL\" WHERE \"STAT\" = 3 AND \"NDX\" = '2006-01-31'

which gets me all the ones that are =3, of course , however the same record could be finished and out the door and I need to know that as well as those that are at area 4, 5, 6 and 7. I know I'm not thinking clearly and the answer is right in front of me.

 

[iceman42]

SELECT COUNT(*) FROM \"ORTL\" WHERE \"STAT\" = 3 AND \"STAT\" !> 3 AND \"NDX\" = '2006-01-31' is what I tried next. then I tried I also did SELECT COUNT(*) FROM \"ORTL\" WHERE \"STAT\" = 3 AND \"STAT\" !> 3 AND \"STAT\" !< 3 AND \"NDX\" = '2006-01-31' I got the same count as the previous query, so then from there I would do something like SELECT COUNT(*) FROM \"ORTL\" WHERE \"STAT\" = 4 AND \"STAT\" !> 4 AND \"NDX\" = '2006-01-31'?

the problem however is that 3 is the starting point so I dont need where they have been I need where they are now, maybe I am looking at this wrong? maybe I need to get the invoice that is past area 3 and then find out its last spot? or can I still do it this way like SELECT DISTINCT and then index the invoice

 

[GhostWolf]

I'm guessing that STAT is the current location, but what hangs me up is knowing:
- whether there's a different database record for each location during the process; or,
- that there's only one record for the invoice and it contains the current STAT.

Assuming that the STAT codes successively increase, you could try something like SELECT INVOICE, MAX(STAT) ... WHERE NDX=2006-01-31 to get you the invoice's current location, (in the first situation).

The second situation seems easier: SELECT STAT ... WHERE NDX=2006-01-31 should return you any invoice which started on 1/31 regardless its location.

The only way I can see a need for STAT in the WHERE clause is if there's a location you don't want reported.

 

[iceman42]

Here is one set of scans from one invoice. Red is the area, green is the invoice #.

655391 E 1 1 10 0 2005-12-22 15:59:00 32 2005-12-22 16:21:00 76 0 1.00 C 1940-01-01
655391 E 2 1 20 1 2006-01-19 16:44:00 5 2006-01-19 16:44:00 5 0 1.00 C 1940-01-01
655391 E 3 1 30 2 2005-12-22 16:21:00 76 2006-01-31 15:45:00 72 0 29.00 C 1940-01-01
655391 E 4 1 40 3 2006-02-02 13:32:00 101 2006-02-03 11:44:00 32 0 1.00 C 1940-01-01
655391 E 5 1 50 4 2006-02-03 15:28:00 25 2006-02-03 20:56:00 26 0 0 C 1940-01-01
655391 E 6 1 60 5 2006-02-08 15:36:00 12 2006-02-08 15:36:00 12 0 1.00 C 1940-01-01
655391 E 7 1 70 6 2006-02-08 16:46:00 67 2006-02-08 16:46:00 67 0 1.00 C 1940-01-01
655391 E 8 1 80 7 2006-02-09 07:08:00 5 2006-02-14 13:51:00 5 0 4.00 C 1940-01-01
655391 E 9 1 90 8 2006-02-14 15:11:00 18 2006-02-14 15:11:00 18 0 1.00 C 1940-01-01

so too answer your question there is a different database record for each location during the process. the problem with MAX is that I need to get a count of the invoices that are in x area, then get the # of units with that invoice.

 

[iceman42]

I forgot to answer the other question about not needing certian ones, 0 I dont need 1 is something all together different

 

[MarcLodge]

Iceman42,

You need to identify the invoices that have a status of 3, plus any matching invoice numbers that have a different status after the date of the status 3 row.

In order to do this I would suggest either joining the table to itself, or (and this is probably the better way to go) an EXISTS subselect on the same table with the invoices numbers matching and the subselect status = 3, plus outer table date greater than subselect date.

If you count(*) on this grouping by status, you might get close to that which you wish.

Hope this helps

Marc

 

[ddiamond]

Is their any flag to indicate that a given record is the record that a invoice started on, or do you need to look at the record with the smallest date to determine this?

 

[iceman42]

There is no flag, however my report starts when the invoice leaves area 2

 

[ddiamond]

Are you saying you can hardcode the starting area? If so the following sql might work.

select a.STAT, count(a.INVOICE)
from ORTL a
where a.STAT >= 3
  and Exists (
    select 1
    from CRTL b
    where a.invoice = b.invoice
      and b.NDX = '2006-01-31'
      and b.STAT = 3)
group by a.STAT;

 

[ddiamond]

Marc had suggested that you need to check the date in the outer query. Now that I think about it he is right. I was assuming that STAT was ascending, but that may not be the case, so you may want to replace a.STAT>=3 with a.NDX>='2006-01-31'

 

[MarcLodge]

Iceman42,

Just had a look at this again, and I reckon that you could do it this way without too much difficulty:

SELECT B.STAT, COUNT(*)
FROM ORTL A
        ,ORTL B
WHERE A.STAT = 3
AND B.INVOICE = A.INVOICE
AND B.NDX >= A.NDX
AND B.STAT > A.STAT
GROUP BY B.STAT

Looking at the data you've supplied, you could probably leave off that AND B.STAT > A.STAT clause as it seems that that is always the case if the date if greater than or equal to.

An EXISTS version along the same lines could work better, but it depends upon the efficiency of the tables and indexes.

Let us know how you get on.

Marc

 

[iceman42]

Okay thanks, just getting back to it after being out a few days.

 

[iceman42]

There is one problem and that is I am doing this through and ODBC connection to an AS/400 and the two tables are in two seperate libraries, so I dont think the joins will work.

 

[ddiamond]

If you need to reference multiple libraries through ODBC, simply qualify your table name with the library in the from clause.

For example:
Select * From MyLibrary.MyTable as a;

Alternatively, you can add your libraries to your ODBC drivers library list.

 

[iceman42]

The way I did my ODBC was through the Data Sources control panel, but I only saw where I could specify one Library, not very familiar with the window DSN setup.

 

[iceman42]

Might have figured this one out myself.

 

[ddiamond]

On my ODBC DSN, I see the library list on the server tab (2nd tab). The location of the library list may vary, however, depending on the version of your i-series ODBC driver.

 

[iceman42]

This is the second time I'm going to attempt to post this, last time it didnt seem to take

Anyway I'm using iSeries Access Driver for ODBC v9.blah.blah. Currently I am configuring a System DNS for each Library, I have tried to list multiple Libraries, on the 2nd tab, the driver for some reason thinks that it needs the Package Library and gives it a default name if I enter nothing. If I try and use that for anything I get the SQL0204 error type *FILE not found.
However if I used that system dns to list the tables in the Libraries, the get printed out without a problem.