Query Filtered by Data 1

[missmis44]

I am trying to create a query to list only one arrest record per booking.There are two tables JMMAIN (Booking) and ARMAIN (Arrest). The ranking are in the following priority order: F,W,M. If both records are the same, such as BookNum 2 that has two Fs and one M, I want it to display ArrestID 5. In the end I only want one Arrest record for each booking. For example, the table looks like:

JMAIN.BookNum ARMAIN.F/M/W ARMAIN.ArrestID
1 W 3
1 F 4
2 F 5
2 F 6
2 M 7
3 M 8
4 W 9
4 M 10

I want the query to filter the data and only display:

JMAIN.Book ARMAIN.F/M/W ARMAIN.ArrestID
1 F 4
2 F 5
3 M 8
4 W 9

 

[SantaMufasa]

Miss Mis,

First, I presume that the data linkage (FK-to-PK match) between your two tables is the BOOKNUM column. If that is the case, then you probably do not need to even involve the JMAIN table in the query...You have all the data you need in the ARMAIN table. Here is code that should resolve your need:

select booknum, fmw, arrestID
from armain
where (booknum,decode(fmw,'F',1,'W',2,'M',3)||arrestID) in 
       (Select booknum, min(decode(fmw,'F',1,'W',2,'M',3)||arrestID)
         From armain
        Group by booknum);

   BOOKNUM F   ARRESTID
---------- - ----------
         1 F          4
         2 F          5
         3 M          8
         4 W          9

Let us know if you are satisfied with the method and the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[missmis44]

I am trying to combine the following two working scripts. I do have to reference the Booking Table because I need to filter the data based on the BOOKDATE.
Thanks Again!

SELECT BOOK_ID, FEL_MIS_WT, ARRE_ID FROM ARMAIN
WHERE (BOOK_ID,DECODE(FEL_MIS_WT, 'F',1,'W',2,'M',3)||ARRE_ID) IN
(SELECT BOOK_ID, MIN(DECODE(FEL_MIS_WT, 'F',1,'W',2,'M',3)||ARRE_ID)
FROM ARMAIN 
GROUP BY BOOK_ID);
  
  
SELECT J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE
FROM JMMAIN J, ARMAIN A        
WHERE J.BOOK_ID = A.BOOK_ID AND TO_CHAR(BOOKDATE,'YYYYMMDD') BETWEEN '20040101' AND '20041231'
ORDER BY BOOK_ID;

 

[SantaMufasa]

Congratulations, MissMis. Do you need any other help at this point? If so, what specifically do you need?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[missmis44]

I need to get the two scripts to work as one script. I got it to work with your suggestions with just the ARMAIN table but I need to reference both the JMMAIN and ARMAIN TABLES. I am currently working through it. Currently, I can getting an ORA-00920: invalid relational operator Error. This is what I have so far:

SELECT J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE, A.ARRE_ID 
FROM JMMAIN J, ARMAIN A        
WHERE  (J.BOOK_ID,DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDATE,A.ARRE_ID) IN
(SELECT J.BOOK_ID, MIN(DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDDATE,A.ARRE_ID)
AND J.BOOK_ID = A.BOOK_ID AND TO_CHAR(J.BOOKDATE,'YYYYMMDD') BETWEEN '20040101' AND '20041231'
ORDER BY J.BOOK_ID

 

[SantaMufasa]

MissMis,

I believe that your problem is a missing right paren:

SELECT J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE, A.ARRE_ID 
FROM JMMAIN J, ARMAIN A        
WHERE  (J.BOOK_ID,DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDATE,A.ARRE_ID) IN
(SELECT J.BOOK_ID, MIN(DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDDATE,A.ARRE_ID)[b])[/b] -- <-- Missing right paren
AND J.BOOK_ID = A.BOOK_ID AND TO_CHAR(J.BOOKDATE,'YYYYMMDD') BETWEEN '20040101' AND '20041231'
ORDER BY J.BOOK_ID

Let us know if this satisfactorily resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[missmis44]

I added the right paren and am still getting the following error: ORA-00920: invalid relational operator.

SELECT J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE, A.ARRE_ID 
FROM JMMAIN J, ARMAIN A        
WHERE  (J.BOOK_ID,DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDATE,A.ARRE_ID) IN
(SELECT J.BOOK_ID, MIN(DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDDATE,A.ARRE_ID)) 
AND J.BOOK_ID = A.BOOK_ID AND TO_CHAR(J.BOOKDATE,'YYYYMMDD') BETWEEN '20040101' AND '20041231'
ORDER BY J.BOOK_ID

 

[Thargy]

MissMiss,

In your squirrel (SQL), you are using an "IN (blah blah blah)" clause. To wit:-

(SELECT J.BOOK_ID, MIN(DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDDATE,A.ARRE_ID))

Unless I'm missing something, the IN part needs to select FROM a table. You don't appear to have one in that subquery.

I would naievely expect it to read along the lines of:-

(SELECT J.BOOK_ID, 
        MIN(DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3) ||J.BOOKDDATE,A.ARRE_ID) FROM ARMAIN A, JMMAIN J
 ORDER BY blah blah blah)

I await my doom.....

Regards

Tharg

 

[SantaMufasa]

MissMis,

Tharg caused me to take a second/closer look at your code (which I should have done in the first place ). There are multiple remaining issues with your code:

1) Yes, you are missing a "FROM <table>" clause in your subquery.

2) A non-trivial issue is that in your subquery, you use a GROUP FUNCTION, "MIN", which compels you to cause the other expressions in your SELECT to become GROUP expressions, as well. I presume that you would want to use GROUP BY to cause those expressions to become GROUPed expressions.

3) You cannot refer in your subquery to the table aliases "J." and "A." since you define "J." and "A." as aliases in your outer query...The subquery cannot see alias that you define above or outside your subquery. I have inserted (below) into your syntax, references to your "JMMAIN J" and "AMMAIN A" tables and I suggest you revisit that section to confirm correct behaviour according to your logic needs.

4) The "closer look" also focuses on the parentheses that I mentioned earlier: Yes, you do need the additional paren, but I the placement was incorrect. I'm not exactly sure how much of the expression you are "MIN"-ing, but you can confirm correctness in light of the paren-placement adjustment.

Therefore, a more syntactically compliant version of your code would read:

SELECT J.BOOK_ID
      ,A.FEL_MIS_WT
      ,J.BOOKDATE
      ,A.ARRE_ID 
  FROM JMMAIN J
      ,ARMAIN A        
 WHERE (J.BOOK_ID
       ,DECODE(A.FEL_MIS_WT
              ,'F',1
              ,'W',2
              ,'M',3)||J.BOOKDATE
       ,A.ARRE_ID
       )
       IN
       (SELECT J.BOOK_ID
              ,MIN(DECODE(A.FEL_MIS_WT
                         ,'F',1
                         ,'W',2
                         ,'M',3)||J.BOOKDDATE)
              ,A.ARRE_ID[b]
          FROM JMMAIN J, ARMAIN A
         WHERE <some join condition>
         GROUP BY J.BOOK_ID, A.ARRE_ID[/b]
        ) 
   AND J.BOOK_ID = A.BOOK_ID
   AND TO_CHAR(J.BOOKDATE,'YYYYMMDD')
       BETWEEN '20040101' AND '20041231'
 ORDER BY J.BOOK_ID

Let us know how all of this "shakes out" for your.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.