Home » SQL & PL/SQL » SQL & PL/SQL » Help with query (first_value)
Help with query (first_value) [message #217035] |
Wed, 31 January 2007 07:25  |
tsp120
Messages: 18 Registered: November 2005
|
Junior Member |
|
|
This query is actually embedded in a create view statement, but for the purposes of this thread I wish to just focus on the select statement. I removed a lot of things just to make this query as simple as possible, so you may find syntax errors or just inconsistencies within the query. But what I am working with does work and I am just looking for a new or modified approach to my problem.
The basic query selects data from the EDITS table, where the MEDITS EID equals the EDITS EID.
However, I need to perform a join to the EFS and FNAME tables to get data. This relationship is one to many, but I wish to only return one record per EDITS ID. In doing research I found the FIRST_VALUE function. I implemented this and everything works fine. The problem is that when choosing data from this first record, I need to perform the FIRST_VALUE function on each column I wish to retrieve...and of course each time I use this function I need to re-type the sort.
This is very expensive and is causing very large times when selecting data from this statement. Is there a way for me to select the ID, IDFN, FCC, DISNAME and NAME data while only performing the FIRST_VALUE and sort once, or is there even a better way for me to approach this problem?
Thanks in advance for any help and ideas!
SELECT
ED.ID,
ED.TYP,
ED.DAT,
CASE
WHEN Q2.FCC IS NULL
THEN GET_FCC(ED.ID)
ELSE Q2.FCC
END AS PRIFCC,
Q2.IDEF AS PRIIDEF,
Q2.IDFN AS PRIIDFN,
Q2.DISNAME AS PRIDISNAME,
Q2.NAME AS PRINAME
FROM
EDITS ED
JOIN (
SELECT
DISTINCT(ED.ID),
FIRST_VALUE(Q1.ID)
OVER(
PARTITION BY ED.ID
ORDER BY
CASE Q1.IDFN
WHEN NULL THEN 2
ELSE 1
END,
CASE Q1.FLG
WHEN 'P' THEN 1
WHEN 'S' THEN 2
ELSE 3
END,
CASE SUBSTR(Q1.FCC,1,1)
WHEN 'R' THEN 1
WHEN 'L' THEN 2
WHEN 'K' THEN 3
WHEN 'P' THEN 4
END,
CASE Q1.TYP
WHEN 'M' THEN 1
WHEN 'C' THEN 2
ELSE 3
END
) AS IDEF,
FIRST_VALUE(Q1.IDFN)
OVER(
PARTITION BY ED.ID
ORDER BY
CASE Q1.IDFN
WHEN NULL THEN 2
ELSE 1
END,
CASE Q1.FLG
WHEN 'P' THEN 1
WHEN 'S' THEN 2
ELSE 3
END,
CASE SUBSTR(Q1.FCC,1,1)
WHEN 'R' THEN 1
WHEN 'L' THEN 2
WHEN 'K' THEN 3
WHEN 'P' THEN 4
END,
CASE Q1.TYP
WHEN 'M' THEN 1
WHEN 'C' THEN 2
ELSE 3
END
) AS IDFN,
FIRST_VALUE(Q1.DISNAME)
OVER(
PARTITION BY ED.ID
ORDER BY
CASE Q1.IDFN
WHEN NULL THEN 2
ELSE 1
END,
CASE Q1.FLG
WHEN 'P' THEN 1
WHEN 'S' THEN 2
ELSE 3
END,
CASE SUBSTR(Q1.FCC,1,1)
WHEN 'R' THEN 1
WHEN 'L' THEN 2
WHEN 'K' THEN 3
WHEN 'P' THEN 4
END,
CASE Q1.TYP
WHEN 'M' THEN 1
WHEN 'C' THEN 2
ELSE 3
END
) AS DISNAME,
FIRST_VALUE(Q1.NAME)
OVER(
PARTITION BY ED.ID
ORDER BY
CASE Q1.IDFN
WHEN NULL THEN 2
ELSE 1
END,
CASE Q1.FLG
WHEN 'P' THEN 1
WHEN 'S' THEN 2
ELSE 3
END,
CASE SUBSTR(Q1.FCC,1,1)
WHEN 'R' THEN 1
WHEN 'L' THEN 2
WHEN 'K' THEN 3
WHEN 'P' THEN 4
END,
CASE Q1.TYP
WHEN 'M' THEN 1
WHEN 'C' THEN 2
ELSE 3
END
) AS NAME,
FIRST_VALUE(Q1.FCC)
OVER(
PARTITION BY ED.ID
ORDER BY
CASE Q1.IDFN
WHEN NULL THEN 2
ELSE 1
END,
CASE Q1.FLG
WHEN 'P' THEN 1
WHEN 'S' THEN 2
ELSE 3
END,
CASE SUBSTR(Q1.FCC,1,1)
WHEN 'R' THEN 1
WHEN 'L' THEN 2
WHEN 'K' THEN 3
WHEN 'P' THEN 4
END,
CASE Q1.TYP
WHEN 'M' THEN 1
WHEN 'C' THEN 2
ELSE 3
END
) AS FCC
FROM
EDITS ED
LEFT OUTER JOIN (
SELECT
ED.EID ,
EF.ID,
EF.FLG,
EF.FCC,
EF.TYP,
EF.IDFN,
FN.DISNAME,
FN.NAME
FROM
EDITS ED,
REL R,
EFS EF
LEFT OUTER JOIN
FNAME FN
ON EF.IDFN = FN.ID
WHERE
R.TID = ED.EID AND
R.TYP = 2 AND
EF.TID = R.TID AND
EF.TGID = R.TGID
) Q1 ON Q1.EID = ED.EID,
MEDITS ME
WHERE
ME.EID = ED.EID
) Q2 ON Q2.ID = ED.ID,
MEDITS ME
WHERE
ME.EID = ED.EID
|
|
|
|
Re: Help with query (first_value) [message #217076 is a reply to message #217035] |
Wed, 31 January 2007 10:40  |
Cthulhu
Messages: 381 Registered: September 2006 Location: UK
|
Senior Member |
|
|
Change the inner query to:
(SELECT
ID,
IDFN,
DISNAME,
FCC
FROM
(SELECT
ID,
IDFN,
DISNAME,
FCC,
ROW_NUMBER()
OVER(
PARTITION BY ED.ID
ORDER BY
CASE Q1.IDFN
WHEN NULL THEN 2
ELSE 1
END,
CASE Q1.FLG
WHEN 'P' THEN 1
WHEN 'S' THEN 2
ELSE 3
END,
CASE SUBSTR(Q1.FCC,1,1)
WHEN 'R' THEN 1
WHEN 'L' THEN 2
WHEN 'K' THEN 3
WHEN 'P' THEN 4
END,
CASE Q1.TYP
WHEN 'M' THEN 1
WHEN 'C' THEN 2
ELSE 3
END
) AS rn
FROM EDITS ED)
WHERE RN=1)
|
|
|
Goto Forum:
Current Time: Tue Feb 11 18:51:55 CST 2025
|