Solved

how to create a view along with  columns ?

Posted on 2014-02-26
3
354 Views
Last Modified: 2014-03-24
Hello expert i have below query . i want to create a view having  from below query
below column
VISIT_ID  
PROTOCOL_NAME                        
VISIT_STATUS_NAME                    
START_DATE                                          
END_DATE                                        
FACILITY_ID                                        
FACILITY_NAME                          
CITY                                  
COUNTRY                              
FACILITY_CONTACT                      
GSM                                      
VISIT_TYPE                            
INCLUDE_CAV                              
FACILITY_PROFILE_DATE_SENT                          
FACILITY_PROFILE_DATE_SUBMIT                          
TPA_COMPANIES                                        
SR_PROGRAM_MGR                        
ONSITE_LEAD                          
APPLE_LEAD                            
VERIFICATION_LEAD                      
FINDINGS_COUNT                                    
CAPA_COUNT  

select 
qry1.visit_id,
qry1.protocol_name,
qry1.visit_status_name,
qry1.start_date,
qry1.end_date,
qry1.facility_id,
qry1.facility_name,
qry1.city,
qry1.country,
ms_apps_utilities.get_user_full_name(qry1.facility_contact) facility_contact,
k.user_firstname||chr(32)||k.user_lastname GSM,
qry1.visit_type,
qry1.include_cav,
qry1.facility_profile_date_sent,
qry1.facility_profile_date_submit,
i.tpa_companies,
ms_apps_utilities.get_user_full_name(qry1.sr_program_manager) sr_program_mgr,
ms_apps_utilities.get_user_full_name(qry1.onsite_lead) onsite_lead,
ms_apps_utilities.get_user_full_name(qry1.apple_lead) apple_lead,
ms_apps_utilities.get_user_full_name(qry1.apple_verification_lead) verification_lead,
nvl(h.findings_count,0) findings_count,
nvl(j.ca_count, 0) + nvl(j.pa_count, 0) capa_count
from
(select
a.visit_id,
a.protocol_name,
a.visit_status,
get_display_value('MS SRA VISIT STATUS', a.visit_status) visit_status_name,
a.start_date,
a.end_date,
a.gsm,
b.facility_id,
b.facility_name,
b.facility_contact,
b.city,
b.country,
INITCAP(a.surprise) surprise,
get_display_value('MS SRA VISIT TYPE',a.visit_type) visit_type,
INITCAP(a.include_cav) include_cav,
a.facility_profile_date_sent,
a.facility_profile_date_submit,
a.sr_program_manager,
a.onsite_lead,
a.apple_lead,
a.apple_verification_lead
from ms_sra_visit_setup a
inner join (SELECT *
from   (select
      VISIT_ID
      ,FACILITY_ID
      ,FACILITY_CONTACT
      ,DD_OBJECT_TYPE 
      ,facility_name
      ,city
      ,state_province
      ,country
      ,facility_address
      ,FACILITY_LOCAL_NAME
      ,facility_local_address
      ,LONGITUDE
        ,LATITUDE
               ,Row_number() 
                 over ( 
                   PARTITION BY visit_id 
                   ORDER BY CASE WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT' THEN 1 WHEN 
                 DD_OBJECT_TYPE = 
                 'MS_SRA_VISIT_SETUP' then 2 end) RN 
        FROM   sgm.ms_sra_facility_info
        where  DD_OBJECT_TYPE in ( 'MS_SRA_VISIT', 'MS_SRA_VISIT_SETUP' )) 
where  RN = 1) b 
on a.visit_id = b.visit_id) qry1
left outer join
(select visit_id, count(finding_id) findings_count
from ms_sra_visit_findings
group by visit_id) h
on qry1.visit_id = h.visit_id
left outer join
(select a.visit_id, wm_concat(b.org_entity_name) tpa_companies
from ms_sra_visit_setup_tpa a
inner join si_org_entities_t b
on a.audit_company = b.org_entity_id
group by a.visit_id) i
on qry1.visit_id = i.visit_id
left outer join
(select visit_id, sum(case when act_type = 'C' then 1 else 0 end) ca_count,
sum(case when act_type = 'P' then 1 else 0 end) pa_count
from ms_sra_visit_actions
group by visit_id) j
on qry1.visit_id = j.visit_id
left outer join
(select distinct user_ds_id,user_firstname,user_lastname from ms_sra_sis_facilities_cnt) k
on qry1.gsm = k.user_ds_id
WHERE
(qry1.visit_id = :Visit_ID or :Visit_ID is null)
AND
(qry1.protocol_name = :Protocol or :Protocol is null)
AND
(qry1.Start_Date >= :Start_Date OR :Start_Date IS NULL)
AND 
(qry1.End_Date >= :Start_Date OR :Start_Date IS NULL)
AND
(qry1.Start_Date <= :End_Date OR :End_Date IS NULL)
AND 
(qry1.End_Date <= :End_Date OR :End_Date IS NULL)
AND
(qry1.facility_id = :Facility_ID or :Facility_ID is null)
AND
(qry1.facility_name = :Facility_Name or :Facility_Name is null)
AND
(qry1.city = :Facility_City or :Facility_City is null)
AND
(qry1.country = :Facility_Country or :Facility_Country is null)
AND
(qry1.surprise = :Surprise_Visit or :Surprise_Visit is null)
AND
(qry1.include_cav = :Include_CAV or :Include_CAV is null)
AND
(qry1.visit_type = :Visit_Type or :Visit_Type is null)
AND
(qry1.apple_verification_lead = :Apple_Verification_Lead or :Apple_Verification_Lead is null)
AND
(qry1.apple_lead = :Apple_Lead or :Apple_Lead is null)
AND
(qry1.onsite_lead = :Onsite_Lead or :Onsite_Lead is null)
AND
(qry1.sr_program_manager = :Sr_Program_Manager or :Sr_Program_Manager is null)
AND
(qry1.gsm = :GSM or :GSM is null)
AND
(qry1.visit_status_name = :Status or :Status is null)
AND
(TO_CHAR(qry1.facility_profile_date_sent, 'mm/dd/yyyy') = TO_CHAR(:F_P_Last_Sent_Date, 'mm/dd/yyyy') OR :F_P_Last_Sent_Date is null)
AND
(TO_CHAR(qry1.facility_profile_date_submit, 'mm/dd/yyyy') = TO_CHAR(:F_P_Last_Comp_Date, 'mm/dd/yyyy') OR :F_P_Last_Comp_Date is null)
AND
((i.tpa_companies like '%'||:TPA_Companies||'%') or (:TPA_Companies is null))

Open in new window


Regards
Thomos
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
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39889618
>>i want to create a view having  from below query below column

I do not understand.  What isn't working?

Syntax should be:
create or replace view MYVIEW as
SELECT ... -- your select statement.
0
 

Author Comment

by:deve_thomos
ID: 39889680
Hello expert,these are the parameters i need pass the value so i cannot  create view along with
WHERE
(qry1.visit_id = :Visit_ID or :Visit_ID is null)
AND
(qry1.protocol_name = :Protocol or :Protocol is null)
AND
(qry1.Start_Date >= :Start_Date OR :Start_Date IS NULL)
AND
(qry1.End_Date >= :Start_Date OR :Start_Date IS NULL)
AND
(qry1.Start_Date <= :End_Date OR :End_Date IS NULL)
AND
(qry1.End_Date <= :End_Date OR :End_Date IS NULL)
AND
(qry1.facility_id = :Facility_ID or :Facility_ID is null)
AND
(qry1.facility_name = :Facility_Name or :Facility_Name is null)
AND
(qry1.city = :Facility_City or :Facility_City is null)
AND
(qry1.country = :Facility_Country or :Facility_Country is null)
AND
(qry1.surprise = :Surprise_Visit or :Surprise_Visit is null)
AND
(qry1.include_cav = :Include_CAV or :Include_CAV is null)
AND
(qry1.visit_type = :Visit_Type or :Visit_Type is null)
AND
(qry1.apple_verification_lead = :Apple_Verification_Lead or :Apple_Verification_Lead is null)
AND
(qry1.apple_lead = :Apple_Lead or :Apple_Lead is null)
AND
(qry1.onsite_lead = :Onsite_Lead or :Onsite_Lead is null)
AND
(qry1.sr_program_manager = :Sr_Program_Manager or :Sr_Program_Manager is null)
AND
(qry1.gsm = :GSM or :GSM is null)
AND
(qry1.visit_status_name = :Status or :Status is null)
AND
(TO_CHAR(qry1.facility_profile_date_sent, 'mm/dd/yyyy') = TO_CHAR(:F_P_Last_Sent_Date, 'mm/dd/yyyy') OR :F_P_Last_Sent_Date is null)
AND
(TO_CHAR(qry1.facility_profile_date_submit, 'mm/dd/yyyy') = TO_CHAR(:F_P_Last_Comp_Date, 'mm/dd/yyyy') OR :F_P_Last_Comp_Date is null)
AND
((i.tpa_companies like '%'||:TPA_Companies||'%') or (:TPA_Companies is null))

beacuse i am passing the value for these parameters
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39889694
>>,these are the parameters i need pass the value so i cannot  create view along with

You cannot.  Oracle doesn't support parameterized views.

You can set contexts before selecting from a view but that is about it.

An example of views with contexts take a look at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1448404423206
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

752 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