Solved

how to write inline view  ?

Posted on 2014-02-26
2
442 Views
Last Modified: 2014-02-28
SELECT DECODE(Form_Name,'MS_SRA_QUESTION_SETUP','Question Setup','MS_SRA_PROTOCOL_SETUP','Protocol Setup','MS_SRA_VISIT_SETUP','Visit Setup','MS_SRA_VISIT','Visit','MS_SRA_CAP','CAP') As Form_name,
  Field_Name,
  ID,
  Before_Value,
  After_Value,
  Updated_By,
  Updated_On
FROM
  (SELECT a.SOURCE_ID,
    (
    CASE
      when a.SOURCE_NAME='MS_SRA_VISIT'
      THEN 'MS_SRA_VISIT'
      ELSE a.source_name
    END) Form_Name,
    (
    CASE
      WHEN CAST(instr(a.field_name,'#$#') AS NUMBER(3,0)) >0
      THEN SUBSTR(a.field_name,instr(a.field_name,'#$#')+3,LENGTH(a.field_name)-instr(a.field_name,'$')-1)
      ELSE a.field_name
    END) field_name,
    (
    case
      --when a.SOURCE_NAME = 'MS_SRA_PROTOCOL_SETUP' then PROTOCOL_NAME
      --when a.SOURCE_NAME = 'MS_SRA_QUESTION_SETUP' then QUESTION_ID
      when INSTR(a.FIELD_NAME,'#$#')>0
      then cast(SUBSTR(a.FIELD_NAME,INSTR(a.FIELD_NAME,'#$#')+3) as varchar2(20))
      ELSE B.OBJECT_ID
    END) ID,
    a.before_value Before_Value,
    a.after_value After_Value,
    ms_apps_utilities.get_user_full_name(ms_apps_utilities.get_user_name(a.chd_last_modified_by)) Updated_By,
    a.chd_last_modified_on Updated_On
  FROM ms_sra_change_his_detail_rpt a
  INNER JOIN ms_sra_change_his_header_rpt b
  ON a.source_id    = b.source_id
  AND a.source_name = b.source_name
  LEFT OUTER JOIN ms_sra_question_setup c
  ON a.source_id = CAST(c.question_id AS VARCHAR2(30))
  ) qry1
WHERE (upper(Updated_On) >= upper(:Start_Date)
OR upper(:Start_Date)    IS NULL)
AND (upper(Updated_On)   <= upper(:End_Date)
OR upper(:End_Date)      IS NULL)
AND (Field_Name           = :Field_Name
OR :Field_Name           IS NULL)
AND (Updated_By           = :Updated_By
or :Updated_By           is null)
and (Form_Name            = :Form_Name
or :Form_Name            is null)
 and rownum<50000;

Open in new window


  --when a.SOURCE_NAME = 'MS_SRA_PROTOCOL_SETUP' then PROTOCOL_NAME
      --when a.SOURCE_NAME = 'MS_SRA_QUESTION_SETUP' then QUESTION_ID

in both line i need to use inline view  that  
from ms_sra_change_his_detail_rpt  i am getting source name
 but my requirement is
when a.SOURCE_NAME = 'MS_SRA_PROTOCOL_SETUP' then PROTOCOL_NAME i need to see
similarly  
--when a.SOURCE_NAME = 'MS_SRA_QUESTION_SETUP' then QUESTION_ID  ineed to see  QUESTION_ID

protocol_name  i will get from MS_SRA_PROTOCOL_SETUP
QUESTION_ID    i will get from  MS_SRA_QUESTION_SETUP
 how i will get through the inline view there ?
0
Comment
Question by:deve_thomos
[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
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39889826
not sure I completely understand but maybe something like:

...
when a.SOURCE_NAME = 'MS_SRA_PROTOCOL_SETUP' then (select PROTOCOL_NAME from MS_SRA_PROTOCOL_SETUP where some_column = a.some_other_column)
...


You just need the inline view to return only one row EVER.
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39892456
Well,
if I understood your query,  QUESTION_ID field from MS_SRA_QUESTION_SETUP is the same thing as SOURCE_ID from ms_sra_change_his_detail_rpt.
so, for one of your commented lines, the solution will be:
when a.SOURCE_NAME = 'MS_SRA_QUESTION_SETUP' then a.SOURCE_ID

Since the other table you mentioned is not on the query, I cannot give you an answer. Post here how that table could connect with the others in your query and I can help you.

Regards,
Walter.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

687 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