Query Help

[JasonMcConnell]

Hi,

I’ve got the following scrip (SQL Plus) that works like a charm but I have noticed that is duplication some records.

SET ECHO OFF
set pagesize 0
set feedback off
set heading on
set validate off
set termout off
set trimspool on
set markup HTML on
spool c:\ApplicationInfo.HTML

SELECT
BCAPPLICATION."APPLICATION_NUMBER", BCAPPLICATION."SITE_ADDRESS", BCAPPLICATION."DEVELOPMENT_DESCRIPTION", BCAPPLICATION."VALIDATED_DATE", BCAPPLICATION."COMMENCEMENT_DATE", BCAPPLICATION."COMPLETION_DATE", BCAPPLICATION."DECISION_DATE",
BCDECISION."DECISION_TYPE_CODE",
LOVS."STD_DATA_CODE", LOVS."LOV_DESC",
OFFICER."FULL_NAME"
FROM
"LAND"."BCAPPLICATION" BCAPPLICATION,
"LAND"."BCRESPONSIBLE_OFFICER" BCRESPONSIBLE_OFFICER,
"LAND"."BCDECISION" BCDECISION,
"LAND"."LOVS" LOVS,
"LAND"."OFFICER" OFFICER
WHERE
BCAPPLICATION."REFERENCE" = BCRESPONSIBLE_OFFICER."BCA_REFERENCE" (+) AND
BCAPPLICATION."REFERENCE" = BCDECISION."BCA_REFERENCE" (+) AND
BCDECISION."DECISION_TYPE_CODE" = LOVS."LOV_CODE" (+) AND
BCRESPONSIBLE_OFFICER."OFFICER_CODE" = OFFICER."CODE" (+)
ORDER BY
BCAPPLICATION."APPLICATION_NUMBER" ASC,
LOVS."STD_DATA_CODE" ASC;

spool off
set markup HTML off


Basically I need to know how to amend this so it groups by bcapplication_number?

Can anybody help?

e.g.FP/2007/00012/A - should only appear once

FP/2007/00010
FP/2007/00012/A
FP/2007/00012/A
FP/2007/00012/A
FP/2007/00015
FP/2007/00016
FP/2007/00017
FP/2007/00022
FP/2007/00023
FP/2007/00024
FP2007/0001/0
FP2007/0003/0
IN/2007/00014
IN/2007/00026
IN/2007/00036
IN/2007/00036
LTC/2007/00011
LTC/2007/00025

 

[Dagon]

The problem with doing a group by is that you need to have rules about which values you take for the other columns. For example, which SITE_ADDRESS do you want to use if there is more than one ? Do you just pick the maximum value ? Or do you want to rank the records e.g. to pick the most recent ?

 

[JasonMcConnell]

Hi Dagon,

I would want to display the most recent entry on all fields. I think the duplication is caused by more that one decision. (E.g. there is also an draft decision).

(The rest of the fields seem to be identical and only the decision is different. )

So if you can tell me how to amend the script to show only the most recent entry that should solve the problem.

Thanks

 

[Dagon]

Is there a field which identifies whether the field is a draft decision ? If so, I would rank on an order by of that. Let's say it's called decision_type with values of 'D' for Draft and 'F' for final. You could do something like:

SELECT * FROM
(SELECT
    BCAPPLICATION."APPLICATION_NUMBER", BCAPPLICATION."SITE_ADDRESS", BCAPPLICATION."DEVELOPMENT_DESCRIPTION", BCAPPLICATION."VALIDATED_DATE", BCAPPLICATION."COMMENCEMENT_DATE", BCAPPLICATION."COMPLETION_DATE", BCAPPLICATION."DECISION_DATE",
    BCDECISION."DECISION_TYPE_CODE",
    LOVS."STD_DATA_CODE", LOVS."LOV_DESC",
    OFFICER."FULL_NAME",
    RANK() OVER (ORDER BY DECODE(BCDECISION.DECISION_TYPE, 'D', 1, 0))
FROM
    "LAND"."BCAPPLICATION" BCAPPLICATION,
    "LAND"."BCRESPONSIBLE_OFFICER" BCRESPONSIBLE_OFFICER,
    "LAND"."BCDECISION" BCDECISION,
    "LAND"."LOVS" LOVS,
    "LAND"."OFFICER" OFFICER
WHERE
    BCAPPLICATION."REFERENCE" = BCRESPONSIBLE_OFFICER."BCA_REFERENCE" (+) AND
    BCAPPLICATION."REFERENCE" = BCDECISION."BCA_REFERENCE" (+) AND
    BCDECISION."DECISION_TYPE_CODE" = LOVS."LOV_CODE" (+) AND
    BCRESPONSIBLE_OFFICER."OFFICER_CODE" = OFFICER."CODE" (+))
WHERE RN=1
ORDER BY
    "APPLICATION_NUMBER" ASC,
    "STD_DATA_CODE" ASC;

 

[Dagon]

Missed out the alias there:

SELECT * FROM
(SELECT
    BCAPPLICATION."APPLICATION_NUMBER", BCAPPLICATION."SITE_ADDRESS", BCAPPLICATION."DEVELOPMENT_DESCRIPTION", BCAPPLICATION."VALIDATED_DATE", BCAPPLICATION."COMMENCEMENT_DATE", BCAPPLICATION."COMPLETION_DATE", BCAPPLICATION."DECISION_DATE",
    BCDECISION."DECISION_TYPE_CODE",
    LOVS."STD_DATA_CODE", LOVS."LOV_DESC",
    OFFICER."FULL_NAME",
    RANK() OVER (ORDER BY DECODE(BCDECISION.DECISION_TYPE, 'D', 1, 0)) as rn
FROM
    "LAND"."BCAPPLICATION" BCAPPLICATION,
    "LAND"."BCRESPONSIBLE_OFFICER" BCRESPONSIBLE_OFFICER,
    "LAND"."BCDECISION" BCDECISION,
    "LAND"."LOVS" LOVS,
    "LAND"."OFFICER" OFFICER
WHERE
    BCAPPLICATION."REFERENCE" = BCRESPONSIBLE_OFFICER."BCA_REFERENCE" (+) AND
    BCAPPLICATION."REFERENCE" = BCDECISION."BCA_REFERENCE" (+) AND
    BCDECISION."DECISION_TYPE_CODE" = LOVS."LOV_CODE" (+) AND
    BCRESPONSIBLE_OFFICER."OFFICER_CODE" = OFFICER."CODE" (+))
WHERE RN=1
ORDER BY
    "APPLICATION_NUMBER" ASC,
    "STD_DATA_CODE" ASC;

 

[JasonMcConnell]

There is only one decision field and a decision status filed that picks up the final or draft decision.

I have set this to final see red


SET ECHO OFF
set pagesize 0
set feedback off
set heading on
set validate off
set termout off
set trimspool on
set markup HTML on
spool c:\ApplicationInfo.HTML

SELECT
BCAPPLICATION."APPLICATION_NUMBER", BCAPPLICATION."SITE_ADDRESS", BCAPPLICATION."DEVELOPMENT_DESCRIPTION", BCAPPLICATION."VALIDATED_DATE", BCAPPLICATION."COMMENCEMENT_DATE", BCAPPLICATION."COMPLETION_DATE", BCAPPLICATION."DECISION_DATE",
BCDECISION."DECISION_TYPE_CODE", BCDECISION."DECISION_STATUS_CODE",
LOVS."STD_DATA_CODE", LOVS."LOV_DESC",
OFFICER."FULL_NAME"
FROM
"LAND"."BCAPPLICATION" BCAPPLICATION,
"LAND"."BCRESPONSIBLE_OFFICER" BCRESPONSIBLE_OFFICER,
"LAND"."BCDECISION" BCDECISION,
"LAND"."LOVS" LOVS,
"LAND"."OFFICER" OFFICER
WHERE
BCAPPLICATION."REFERENCE" = BCRESPONSIBLE_OFFICER."BCA_REFERENCE" (+) AND
BCAPPLICATION."REFERENCE" = BCDECISION."BCA_REFERENCE" (+) AND
BCDECISION."DECISION_TYPE_CODE" = LOVS."LOV_CODE" (+) AND
BCRESPONSIBLE_OFFICER."OFFICER_CODE" = OFFICER."CODE" (+) AND
BCDECISION."DECISION_STATUS_CODE" = 'FINAL'
ORDER BY
BCAPPLICATION."APPLICATION_NUMBER" ASC,
LOVS."STD_DATA_CODE" ASC;

spool off
set markup HTML off



This fixes the duplication problem but it only displays applications that have a final decision? I also need to display applications that have not had a decision?

 

[Dagon]

Did you read what I suggested ?