Solved

Repeating Value

Posted on 2012-03-12
8
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

724 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