how to create a view along with columns ?

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
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:
>>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

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