[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to write inline view  ?

Posted on 2014-02-26
2
Medium Priority
?
449 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

656 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