-
Code:
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PERSON_NR"="VW_NSO_2"."$nso_col_1")
3 - access("RP"."PERSON_NR"="P"."PERSON_NR" AND "RP"."REGIMEN_SEQUENCE"="VW_SQ_1"."VW_COL_1")
4 - access("VW_SQ_1"."PERSON_NR"="P"."PERSON_NR")
5 - filter("P"."EMAIL" NOT LIKE '%test%')
8 - access("R"."REGIMEN_TYPE_CODE"='CD')
9 - filter("RP"."REGIMEN_TYPE_CODE"='CD' AND "RP"."PRESCRIPTION_STATUS_CODE"='FNSH')
11 - access("A"."PERSON_NR"="RP"."PERSON_NR")
12 - access("A"."PERSON_NR"="B"."PERSON_NR")
13 - filter("A"."STATUS_CODE"='ACTV' OR "A"."STATUS_CODE"='CHNG')
14 - filter("B"."JOB_QUEUE_TYPE_NR"=4 AND "B"."STATUS_CODE"='FNSH')
sorry for the delay.,
-
Originally Posted by hrishy
Hi
And Abhay is right you have used some regimen_pres table twice
Try This as Abhay Suggested
Code:
SELECT
rp.person_nr,
FIRST_VALUE(rp.regimen_sequence)
OVER (PARTITION BY rp.person_nr, rp.regimen_type_code
order by rp.regimen_sequence desc ) regimen_sequence,
rp.regimen_type_code
FROM REGIMEN_PRES rp,
PERSON p
WHERE
rp.person_nr = p.person_nr
AND rp.regimen_type_code = 'CD'
AND rp.prescription_status_code= 'FNSH'
and p.email not like '%test%'
AND p.person_nr IN (SELECT b.person_nr
FROM JOB_QUEUE b
WHERE
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'))
ORDER BY rp.person_nr
regards
Hrishy
Hrishy,
A distinct is needed as was in my post, else he will get redundant data.
Rgds
Abhay
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
hrishy..,
I tried that query.., it takes looong time.,
I can use previous query itself.,
hrishy cool down pls.,
-
Originally Posted by hrishy
At this rate i will loose my patience and might switch to a SYBASE forum
LMAO... I feel your pain Hrishy, this particular thread is totally out of control, pure insanity.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|