Solved

how to write inline view  ?

Posted on 2014-02-26
2
435 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

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!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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