Query Help

[johnherman]

SELECT ADJUSTER_NO, MAX(CASE_OPEN_DATE)
FROM AUTO_CLAIM GROUP BY ADJUSTER_NO

This query returns more than one result; that is, there are sometimes more than one Auto Claims opened by an Adjuster on the same day. I only need one record; it doesn't matter which, because I want to equi-join to it for a later update to a separate table.


ideas?

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[avjoshi]

John,

I am not sure I understand. I get only one value when I run your query.

for example, I created following table;

SQL> create table AUTO_CLAIM (ADJUSTER_NO number, CASE_OPEN_DATE date);

Table created.

SQL> desc AUTO_CLAIM
 Name                                      Null?    Type
 ----------------------------------------- -------- -------
 ADJUSTER_NO                                        NUMBER
 CASE_OPEN_DATE                                     DATE

SQL>

I then inserted a few records;

SQL> insert into auto_claim values ( 1,trunc(sysdate));

1 row created.

SQL> insert into auto_claim values ( 1,trunc(sysdate));

1 row created.

SQL> insert into auto_claim values ( 1,trunc(sysdate));

1 row created.

SQL> insert into auto_claim values ( 2,trunc(sysdate)+1);

1 row created.

SQL> insert into auto_claim values ( 2,trunc(sysdate)+1);

1 row created.

SQL> select * from AUTO_CLAIM;

ADJUSTER_NO CASE_OPEN
----------- ---------
          1 12-APR-04
          1 12-APR-04
          1 12-APR-04
          2 13-APR-04
          2 13-APR-04
        

SQL>

then I ran your query;

SQL> SELECT ADJUSTER_NO, MAX(CASE_OPEN_DATE)
  2  FROM AUTO_CLAIM GROUP BY ADJUSTER_NO;

ADJUSTER_NO MAX(CASE_
----------- ---------
          1 12-APR-04
          2 13-APR-04

SQL>

As you can see, I got only one record per adjuster number.
Am I doing it wrong?

Anand

 

[mpcurry]

Please use :

SELECT ADJUSTER_NO, MAX(TRUNC(CASE_OPEN_DATE))
FROM AUTO_CLAIM GROUP BY ADJUSTER_NO


By Using TRUNC, it will truncate the time stamp on all the dates.

Regards
Ajay.

 

[johnherman]

Thanks for your help so far.

First for mpcurry, the CASE_OPEN_DATE is a DATE, not TIMESTAMP. If it were TIMESTAMP, I would probably not have duplicates.

Second, for avjoshi, the primary key for AUTO_CLAIM is CLAIM_NO and I can sometimes have multiple CLAIM_NO for each ADJUSTER_NO for each CASE_OPEN_DATE.

Sorry for the confusion.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[avjoshi]

Is that the same query when you get multiple records? Based on what you said so far you shouldn't get more than one result to your query that you posted earlier.

Anand

 

[johnherman]

The problem arises when I use the above query as a subquery.

DECLARE
v_ErrorNumber NUMBER;
v_ErrorText VARCHAR(200);
v_Err_line VARCHAR(250);
A_Adjuster NUMBER(5);
A_MaxCase DATE;
AA_Location NUMBER(5);
CURSOR A_AdjLoc IS
SELECT ADJUSTER_NO, MAX(CASE_OPEN_DATE)
FROM AUTO_CLAIM GROUP BY ADJUSTER_NO;
BEGIN
OPEN A_AdjLoc;
LOOP
FETCH A_AdjLoc INTO A_Adjuster, A_MaxCase;
EXIT WHEN A_AdjLoc%NOTFOUND;
/* this SELECT returns more than one row */
SELECT AA_Location FROM AUTO_CLAIM AC
WHERE AC.Adjuster = A_Adjuster
AND AC.CASE_OPEN_DATE = A_MaxCase;
UPDATE ADJUSTER
SET ADJUSTER_LOCATION = AA_Location
WHERE ADJUSTER.ADJUSTER_NO = A_Adjuster;
EXCEPTION
WHEN OTHERS THEN
v_ErrorNumber := SQLCODE;
v_ErrorText := SUBSTR(SQLERR,1,200);
v_ErrLine := TO_CHAR(SQLCODE) || ' ' || v_ErrorText;
DBMS_OUTPUT.PUT_LINE(v_ErrLine);
END LOOP;
ROLLBACK;
CLOSE A_AdjLoc;
END;

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[johnherman]

Ignore above post - bugs
DECLARE
v_ErrorNumber NUMBER;
v_ErrorText VARCHAR(200);
v_Err_line VARCHAR(250);
A_Adjuster NUMBER(5);
A_MaxCase DATE;
AA_Location NUMBER(5);
CURSOR A_AdjLoc IS
SELECT ADJUSTER_NO, MAX(CASE_OPEN_DATE)
FROM AUTO_CLAIM GROUP BY ADJUSTER_NO;
BEGIN
OPEN A_AdjLoc;
LOOP
FETCH A_AdjLoc INTO A_Adjuster, A_MaxCase;
EXIT WHEN A_AdjLoc%NOTFOUND;
SELECT Service_Center into AA_Location FROM AUTO_CLAIM AC
WHERE AC.Adjuster_no = A_Adjuster
AND AC.CASE_OPEN_DATE = A_MaxCase;
UPDATE ADJUSTER
SET ADJUSTER_LOCATION = AA_Location
WHERE ADJUSTER.ADJUSTER_NO = A_Adjuster;
/* EXCEPTION
WHEN OTHERS THEN
v_ErrorNumber := SQLCODE;
v_ErrorText := SUBSTR(SQLERR,1,200);
v_ErrLine := TO_CHAR(SQLCODE) || ' ' || v_ErrorText;
DBMS_OUTPUT.PUT_LINE(v_ErrLine);
*/
END LOOP;
ROLLBACK;
CLOSE A_AdjLoc;
END;

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[johnherman]

Disregard - solved using MAX function to return only one value. Thanks for your help !!

Sometimes the grass is greener on the other side because there is more manure there - original.