[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

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 ?
0
deve_thomos
Asked:
deve_thomos
1 Solution
 
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
 
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now