Solved

query help, again

Posted on 2007-04-10
40
913 Views
Last Modified: 2012-05-05
This is going to be a bit tricky but I am hoping for an easy answer :).  I asked a question earlier and took some of the advice and thoughts and created a query from that to solve my issue.  However I noticed a small flaw in the way parcels move in our facility, when a parcel is finished a station it is doesn't necessarily go directly to the next station, it might sit in limbo.  So the table that holds all this information is a single scan table, when a parcel comes in it is scanned into the station, and when it leaves it is scanned out, there is no direct relationship between stations, so when you query for an order you get this for an order that is complete and shipped.

687566   E      1      2      100    150     2007-03-05 09:25:00 2007-03-05 09:25:00 1.00    C      1940-01-01
687566   E      2      2      150    200     2007-03-01 09:28:00 2007-03-01 09:28:00 1.00    C      1940-01-01
687566   E      3      2      200    300     2007-03-09 16:21:00 2007-03-14 16:13:00 4.00    C      1940-01-01
687566   E      4      2      300    400     2007-03-17 14:31:00 2007-03-17 15:13:00 0       C      1940-01-01
687566   E      5      2      400    500     2007-03-23 15:09:00 2007-03-23 15:09:00 0       C      1940-01-01
687566   E      6      2      500    600     2007-03-23 15:09:00 2007-03-23 15:09:00 0       C      1940-01-01
687566   E      7      2      600    700     2007-03-23 15:28:00 2007-03-28 16:26:00 4.00    C      1940-01-01
687566   E      8      2      700    800     2007-03-28 16:26:00 2007-03-28 16:26:00 1.00    C      1940-01-01

This is what an order looks like if it is between stations

687566   E      1      2      100    150     2007-03-05 09:25:00 2007-03-05 09:25:00 1.00    C      1940-01-01
687566   E      2      2      150    200     2007-03-01 09:28:00 2007-03-01 09:28:00 1.00    C      1940-01-01
687566   E      3      2      200    300     2007-03-09 16:21:00 2007-03-14 16:13:00 4.00    C      1940-01-01
687566   E      4      2      300    400     1940-01-01 1940-01-01 0       GAAS      1940-01-01
687566   E      5      2      400    500     1940-01-01 1940-01-01 0       GAAS      1940-01-01
687566   E      6      2      500    600     1940-01-01 1940-01-01 0       GAAS      1940-01-01
687566   E      7      2      600    700     1940-01-01 1940-01-01 0       GAAS      1940-01-01
687566   E      8      2      700    800     1940-01-01 1940-01-01 0       GAAS      1940-01-01

This is what an order looks like if its in a station

687566   E      1      2      100    150     2007-03-05 09:25:00 2007-03-05 09:25:00 1.00    C      1940-01-01
687566   E      2      2      150    200     2007-03-01 09:28:00 2007-03-01 09:28:00 1.00    C      1940-01-01
687566   E      3      2      200    300     2007-03-09 16:21:00 2007-03-14 16:13:00 4.00    C      1940-01-01
687566   E      4      2      300    400     2007-03-14 17:13:00 1940-01-01 0       GAAS      1940-01-01
687566   E      5      2      400    500     1940-01-01 1940-01-01 0       GAAS      1940-01-01
687566   E      6      2      500    600     1940-01-01 1940-01-01 0       GAAS      1940-01-01
687566   E      7      2      600    700     1940-01-01 1940-01-01 0       GAAS      1940-01-01
687566   E      8      2      700    800     1940-01-01 1940-01-01 0       GAAS      1940-01-01

So how do I query for something when there is no real association/relationship between the records, well other then the order number. What I need to say is that this order is located here at station x even if they have not been scanned in, like in the 2nd example.
0
Comment
Question by:iceman19330
  • 18
  • 12
  • 10
40 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
hi
can you please post the names of the columns cause it's not easy to understand what each column do
also, i did not understand what you need to know
how can an order be located in station x if it has not been scanned in ?
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi iceman,

It appears that if a "set" of data can be described by the two timestamp columns that you've shown.

Searching the rows (in order), when one is found matching these rules, the status of the parcel is:
 TS1                    TS2                  Status
  > 1940-10-01   = 1940-01-01  In station
  = 1940-01-01   = 1940-01-01  Between stations
  > 1940-01-01   > 1940-01-01  Delivered

So......


//  parcel is the first column value
//  id is the third column value

SELECT *
FROM parcelhistory
WHERE ID = (SELECT min(id) FROM parcelhistory WHERE date(TS2) = '1940-01-01' GROUP BY parcel)

Within this list, if TS1 = '1940-01-01' the parcel is between stations (group 2).  Otherwise it is in a station.


Good Luck,
Kent
0
 

Author Comment

by:iceman19330
Comment Utility
SELECT *
FROM FILEDTS.ORPRDETL
WHERE FILEDTS.ORPRDETL.RDSTAT = (SELECT min(FILEDTS.ORPRDETL.RDSTAT) FROM FILEDTS.ORPRDETL WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01' GROUP BY FILEDTS.ORPRDETL.RDORD#)

Error: SQL0811 - Result of SELECT more than one row. (State:S1000, Native Code: FFFFFCD5)

0 Row(s) affected

SQL0811 - Result of SELECT more than one row.

If I am being dense I apologize.

Here is the query that you guys helped with.

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
AND FILEDTS.ORPRDETL.RDSTAT = 200
AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
ORDER BY FILES.CUSTMAST.CUTERM ASC
0
 

Author Comment

by:iceman19330
Comment Utility
Here are the fields with an example of a time stamp

RDORD#   RDPTYP RDLIN# RDPRC# RDSEQ# RDSTAT  RDSTRX     RDSTRT   RDSTRU  RDENDX     RDENDT   RDENDU  RDEDAY  RDADAY  RDCOMP RDECMP    
-------- ------ ------ ------ ------ ------- ---------- -------- ------- ---------- -------- ------- ------- ------- ------ ----------
691154   E      1      1      10     100     2007-04-09 10:21:00 72      2007-04-09 10:21:00 72      0       1.00    C      1940-01-01
691154   E      2      1      20     150     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      3      1      30     200     2007-04-09 10:21:00 72      1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      4      1      40     300     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      5      1      50     400     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      6      1      60     500     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      7      1      70     600     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      8      1      80     700     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      9      1      90     800     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01

momi:  To answer your question the production facility people consider the parcel to be in station y when there is an exit scan for the parcel at station x.  So while the system does not see it scanned in, it is more then likely en route or sitting in a scan queue at station y.
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
i still have not understand what you are trying to acomplish, but the above query that kdo suggested should be

SELECT *
FROM FILEDTS.ORPRDETL t
WHERE FILEDTS.ORPRDETL.RDSTAT = (SELECT min(FILEDTS.ORPRDETL.RDSTAT) FROM FILEDTS.ORPRDETL WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01' and "RDORD#" = t."RDORD#" GROUP BY FILEDTS.ORPRDETL.RDORD#)
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
how do i identify a parcel ?
is a parcel = order ?
how do you know which station comes first ? (how do you know that x is before y) and which column identifies a station ?
0
 

Author Comment

by:iceman19330
Comment Utility
how do i identify a parcel ? RDORD# is the parcel/package/bundle, etc
is a parcel = order ? yes
how do you know which station comes first ? (how do you know that x is before y) and which column identifies a station ? RDSTAT is the station, and RDSEQ# is the x before y in most cases, there are a few exceptions in the case of RDSTAT 150 is acutally outside of the RDSEQ# and a parcel can be scanned into RDSTAT 200 and not scanned into 150 yet, which means the parcel is in 150.

0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
ok, now it's much clearer
so you need something like this :

SELECT now_station."RDORD#", now_station.RDSTAT
FROM FILEDTS.ORPRDETL now_station,
           FILEDTS.ORPRDETL prev_station
where now_station."RDORD#" = prev_station."RDORD#"
and   prev_station.RDENDX >  '1940-01-01-00.00.00.000000'
and  now_station."RDSEQ#" = case prev_station."RDSEQ#" when a then b
                                    when b then c
                            when c then d
                   else e    end


try that,
in the last case you just put the stations according to their order
0
 

Author Comment

by:iceman19330
Comment Utility
Error: SQL0206 - Column A not in specified tables. (State:S0022, Native Code: FFFFFF32)
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
a,b,c,d,e are names of stations or values of the RDSEQ# columns
you need to fill those yourself according to the order of your stations
0
 

Author Comment

by:iceman19330
Comment Utility
Okay sorry about that.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Sorry, I had to step out....

Regarding the data you've laid out:

RDORD#   RDPTYP RDLIN# RDPRC# RDSEQ# RDSTAT  RDSTRX     RDSTRT   RDSTRU  RDENDX     RDENDT   RDENDU  RDEDAY  RDADAY  RDCOMP RDECMP    
-------- ------ ------ ------ ------ ------- ---------- -------- ------- ---------- -------- ------- ------- ------- ------ ----------
691154   E      1      1      10     100     2007-04-09 10:21:00 72      2007-04-09 10:21:00 72      0       1.00    C      1940-01-01
691154   E      2      1      20     150     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      3      1      30     200     2007-04-09 10:21:00 72      1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      4      1      40     300     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      5      1      50     400     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      6      1      60     500     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      7      1      70     600     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      8      1      80     700     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01
691154   E      9      1      90     800     1940-01-01 00:00:00 0       1940-01-01 00:00:00 0       0       0              1940-01-01


Is this a query of a single table or a joining of several?

Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
well,
did my query worked ?
0
 

Author Comment

by:iceman19330
Comment Utility
Kdo, this is a single table and contains data from the last 3-4 years.

momi, yes it worked, however I am trying to incorporate that query into the rest of my query.

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
AND FILEDTS.ORPRDETL.RDSTAT = 200
AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
ORDER BY FILES.CUSTMAST.CUTERM ASC

to


SELECT *
FROM FILEDTS.ORPRDETL t
LEFT JOIN t ON t.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
WHERE t.RDSTAT = (SELECT min(e.RDSTAT)
FROM FILEDTS.ORPRDETL e
WHERE date(e.RDENDX) = '1940-01-01'
AND e.RDORD# = t.RDORD#)
AND year(t.RDSTRX) = 2007
AND t.RDSTAT = 200
AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
ORDER BY FILES.CUSTMAST.CUTERM ASC
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 100 total points
Comment Utility
Try something like this:

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME,
  FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
--  new code
left join
(
  SELECT rdord#, rdendx, rdstrx,
    case when year(rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(e.RDENDX) = '1940-01-01'
    AND rdstat = (select min(rdstat) from filedts.orprdetl group by rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
  ORDER BY FILES.CUSTMAST.CUTERM ASC

The final query should return 'Between Stations', 'In Station', or (null) as the status.  A status of (null) indicates delivered.


Kent
0
 

Author Comment

by:iceman19330
Comment Utility
Error: SQL0199 - Keyword LEFT not expected. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE. (State:37000, Native Code: FFFFFF39)

Between Stations should = In Station, but it would be moved to the next station, which the query that moni came up with does that, however I run into problems with combining that query because the original query was taking Station ID as one of the arguments.

AND t.RDSTAT = 200
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
KDO left out outer
it should be "left outer join"

0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
how do you want to mergew the sqls ?
according to which condition ?
0
 

Author Comment

by:iceman19330
Comment Utility
Here is the main condition AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49), WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007.

So I am trying to get a listing of parcels/orders that the customers do NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49) and are from the current year, orders can stay on the floor for 90 days so I don't want to accidentally miss something, then it would give me the information that I want, the FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD.

I am sorry for the delay in getting back to you we are having a small crisis with two desktop systems going down, so I am running back and forth.
0
 

Author Comment

by:iceman19330
Comment Utility
Error: SQL0199 - Keyword LEFT not expected. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE. (State:37000, Native Code: FFFFFF39)


SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
left outer join
(
  SELECT rdord#, rdendx, rdstrx,
    case when year(rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(e.RDENDX) = '1940-01-01'
    AND rdstat = (select min(rdstat) from filedts.orprdetl group by rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
  ORDER BY FILES.CUSTMAST.CUTERM ASC
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

Expert Comment

by:Kdo
Comment Utility
My fault.  (Too many versions of SQL out there....)

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT rdord#, rdendx, rdstrx,
    case when year(rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(e.RDENDX) = '1940-01-01'
    AND rdstat = (select min(rdstat) from filedts.orprdetl group by rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
--  ORDER BY FILES.CUSTMAST.CUTERM ASC



0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
i'm sorry
but i still can't understnad how you wish to connect the 2 queries
0
 

Author Comment

by:iceman19330
Comment Utility
Dont worry with everything going on today I am sure that I have not explained anything worth a darn.

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
AND FILEDTS.ORPRDETL.RDSTAT = 200
AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
ORDER BY FILES.CUSTMAST.CUTERM ASC

The above query was the original query, however there was a flaw in my thought process.  If a parcel is in between two stations it doesn't get picked up in this query.  So there might be 3 or 4 parcels/orders that are not listed when this sql is run.

So I decided that it might be better to actually come up with a query that would get parcels that are in a certain station even if they haven't been scanned in to the station, but have been scanned out of the previous station.  That is where the next query worked well

SELECT *
FROM FILEDTS.ORPRDETL t
WHERE FILEDTS.ORPRDETL.RDSTAT = (SELECT min(FILEDTS.ORPRDETL.RDSTAT) FROM FILEDTS.ORPRDETL WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01' and "RDORD#" = t."RDORD#" GROUP BY FILEDTS.ORPRDETL.RDORD#)

However the problem with this query was that I needed more information that was contained in the first query, CUTERM, TERMCD, REORD# and RDSTAT right now, and the previous query was doing that based on what the RDSTAT = x, whereas this query didnt have to I could just query for all RDSTAT 100-800 and just give me the FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD

Does that explain it better or should I just start over as some of this might just be white noise.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Iceman,

I believe that the SQL two (now three) entries up should be very, very close.


Kent
0
 

Author Comment

by:iceman19330
Comment Utility
Error: SQL5001 - Column qualifier or table E undefined. (State:S1000, Native Code: FFFFEC77)
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
can you post the sql that returned the error ?
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

I had carried a qualifier into the subquery.  :/



SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT rdord#, rdendx, rdstrx,
    case when year(rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(RDENDX) = '1940-01-01'
    AND rdstat = (select min(rdstat) from filedts.orprdetl group by rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
0
 

Author Comment

by:iceman19330
Comment Utility
SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT rdord#, rdendx, rdstrx,
    case when year(rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(RDENDX) = '1940-01-01'
    AND rdstat = (select min(rdstat) from filedts.orprdetl group by rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)

returns
Error: SQL0811 - Result of SELECT more than one row. (State:S1000, Native Code: FFFFFCD5)
0
 

Author Comment

by:iceman19330
Comment Utility
SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT rdord#, rdendx, rdstrx,
    case when year(rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(e.RDENDX) = '1940-01-01'
    AND rdstat = (select min(rdstat) from filedts.orprdetl group by rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
--  ORDER BY FILES.CUSTMAST.CUTERM ASC

returned

Error: SQL5001 - Column qualifier or table E undefined. (State:S1000, Native Code: FFFFEC77)
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Rats.  Did it again...

Remove the "e.' from line 11.  (changing date(e.RDENDX) to date(RDENDX)


0
 

Author Comment

by:iceman19330
Comment Utility
SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT FILEDTS.ORPRDETL.rdord#, FILEDTS.ORPRDETL.rdendx, FILEDTS.ORPRDETL.rdstrx,
    case when year(FILEDTS.ORPRDETL.rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01'
    AND rdstat = (select min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl group by FILEDTS.ORPRDETL.rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)

returns
Error: SQL0811 - Result of SELECT more than one row. (State:S1000, Native Code: FFFFFCD5)
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Ahh.  A package can be in more than one station during a single day!

Change this line:

    AND rdstat = (select min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl group by FILEDTS.ORPRDETL.rdord#)


to:

    AND rdstat = (select distinct min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl group by FILEDTS.ORPRDETL.rdord#)
0
 

Author Comment

by:iceman19330
Comment Utility
same thing Error: SQL0811 - Result of SELECT more than one row. (State:S1000, Native Code: FFFFFCD5)

just to make sure I am using what you want

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT FILEDTS.ORPRDETL.rdord#, FILEDTS.ORPRDETL.rdendx, FILEDTS.ORPRDETL.rdstrx,
    case when year(FILEDTS.ORPRDETL.rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01'
    AND FILEDTS.ORPRDETL.rdstat = (select distinct min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl group by FILEDTS.ORPRDETL.rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
hi
try this

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT FILEDTS.ORPRDETL.rdord#, FILEDTS.ORPRDETL.rdendx, FILEDTS.ORPRDETL.rdstrx,
    case when year(FILEDTS.ORPRDETL.rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL  t
  WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01'
    AND FILEDTS.ORPRDETL.rdstat = (select distinct min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl    where t.rdord#=rdord#     group by FILEDTS.ORPRDETL.rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

This line is the culprit:

  SELECT FILEDTS.ORPRDETL.rdord#, FILEDTS.ORPRDETL.rdendx, FILEDTS.ORPRDETL.rdstrx,
    case when year(FILEDTS.ORPRDETL.rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL
  WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01'
    AND FILEDTS.ORPRDETL.rdstat = (select distinct min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl group by FILEDTS.ORPRDETL.rdord#)

It should probably be:

  SELECT FILEDTS.ORPRDETL.rdord#, FILEDTS.ORPRDETL.rdendx, FILEDTS.ORPRDETL.rdstrx,
    case when year(FILEDTS.ORPRDETL.rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL filex
  WHERE date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01'
    AND FILEDTS.ORPRDETL.rdstat = (select min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl where FILEDTS.ORPRDETL.rdord# = filex.rdord#)



Sorry about this iterative process.  That's what happens when you write SQL against a database with which you're unfamiliar.   :)

Kent
0
 

Author Comment

by:iceman19330
Comment Utility
kdo I understand I thank you both for sticking with me.


both kdo and momi queries return this error.
Error: SQL5001 - Column qualifier or table ORPRDETL undefined. (State:S1000, Native Code: FFFFEC77)
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
This SQL contains a bit of cleanup, but I don't know that it will solve the problem:

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL
  ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST
  ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS
  ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT FILEDTS.ORPRDETL.rdord#, FILEDTS.ORPRDETL.rdendx, FILEDTS.ORPRDETL.rdstrx,
    case when filedts.orprdetl.rdstrx is null then 'Delivered'
         when year (FILEDTS.ORPRDETL.rdstrx) = 1940 then 'Between Stations'
         else 'In Station' end as status
  FROM FILEDTS.ORPRDETL filex
  WHERE FILEDTS.ORPRDETL.rdstat = (select min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl where FILEDTS.ORPRDETL.rdord# = filex.rdord# and date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01')
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)


If the error persists, try running the inner query:

  SELECT FILEDTS.ORPRDETL.rdord#, FILEDTS.ORPRDETL.rdendx, FILEDTS.ORPRDETL.rdstrx,
    case when filedts.orprdetl.rdstrx is null then 'Delivered'
         when year (FILEDTS.ORPRDETL.rdstrx) = 1940 then 'Between Stations'
         else 'In Station' end as status
  FROM FILEDTS.ORPRDETL filex
  WHERE FILEDTS.ORPRDETL.rdstat = (select min(FILEDTS.ORPRDETL.rdstat) from filedts.orprdetl where FILEDTS.ORPRDETL.rdord# = filex.rdord# and date(FILEDTS.ORPRDETL.RDENDX) = '1940-01-01')

0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 400 total points
Comment Utility
ok
how about

SELECT FILEDTS.ORPRDETL.RDORD#, FILESIZE.ORDDETL.ODCUST, FILES.CUSTMAST.CUNAME, FILES.TERMS.TEDESC, FILES.TERMS.TERMCD FROM FILEDTS.ORPRDETL
LEFT JOIN FILESIZE.ORDDETL ON FILEDTS.ORPRDETL.RDORD# = FILESIZE.ORDDETL.ODORD#
LEFT JOIN FILES.CUSTMAST ON FILESIZE.ORDDETL.ODCUST = FILES.CUSTMAST.CUST#
LEFT JOIN FILES.TERMS ON FILES.TERMS.TERMCD = FILES.CUSTMAST.CUTERM
left outer join
(
  SELECT t.rdord#, t.rdendx, t.rdstrx,
    case when year(t.rdstrx) = 1940 then 'Between Stations' else 'In Stantion' end as status
  FROM FILEDTS.ORPRDETL  t
  WHERE date(t.RDENDX) = '1940-01-01'
    AND t.rdstat = (select distinct min(rdstat) from filedts.orprdetl    where t.rdord#=rdord#     group by rdord#)
) as t0
  on FILEDTS.ORPRDETL.RDORD# = t0.rdord#
WHERE year(FILEDTS.ORPRDETL.RDSTRX) = 2007
  AND FILEDTS.ORPRDETL.RDSTAT = 200
  AND FILEDTS.ORPRDETL.RDCOMP <> 'C'
  AND NOT FILES.CUSTMAST.CUTERM IN (1, 5, 6, 9, 15, 49)
0
 

Author Comment

by:iceman19330
Comment Utility
momi looks like it worked, ie no errors and data is coming out.  I will just have to verify that its actually good data.  :)
0
 

Author Comment

by:iceman19330
Comment Utility
Sorry for the delay I used momi query with one minor change.  There was some data replication in the results that was because of one of the joins was grabbing data that had two results, so was able to just make sure that it was nothing serious and it wasn't, actually simple, and took care of that.

Thank you both for your time and patience with me.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now