Solved

Repeating Value

Posted on 2012-03-12
8
345 Views
Last Modified: 2012-08-14
please see attached -

fig a current output (select * from o_questions) and fig B the desired output.

I would like the assessments mee_id to repeat for every row of the assessment (ASM).

So the sql needs to check the assessments avd_data value (where the anv_name = 'MEE_ID') and repeat id for every row of the assessment.

It would use partition by but I need some more help on the syntax.

thanks
0
Comment
Question by:tonMachine100
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37710719
I see no attachments.  Please don't post screen images though.  Please copy/past text
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37710721
>>please see attached -

Forget to attach?
0
 

Author Comment

by:tonMachine100
ID: 37710777
here we go - sorry about that
EE-EXAMPLE-12MAR12.xls
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37710800
try this...

S ELECT o.*,
       LAST_VALUE(CASE WHEN anv_name = 'MEE_ID' THEN avd_data END IGNORE NULLS)
           OVER (PARTITION BY asm_id ORDER BY qst_ord)
           meet_id
  FROM o_questions o
0
 
LVL 32

Expert Comment

by:awking00
ID: 37711043
sdstuber,
Couldn't you have just used -
select o.*,
first_value(avd_data) over (partition by asm_id order by qst_ord) meet_id
from o_questions o;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37711071
yes, with the data presented in that exact form however the question said to base it on (anv_name = 'MEE_ID')    not simply first row of each partition
0
 
LVL 32

Expert Comment

by:awking00
ID: 37711125
Gotcha,
I was just looking at the input and desired output and didn't really analyze the question.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37711150
but... what might be even better than FIRST_VALUE or LAST_VALUE is this...

MAX(CASE WHEN anv_name = 'MEE_ID' THEN avd_data END) OVER (PARTITION BY asm_id)

that way the MEE_ID can appear at any position in the partition
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question