Solved

how to create a view along with  columns ?

Posted on 2014-02-26
3
356 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

617 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