how to write inline view ?

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 ?
deve_thomosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter RitzelSenior Software EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.