Solved

how to create a view along with  columns ?

Posted on 2014-02-26
3
340 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
  • 2
3 Comments
 
LVL 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query - Oracle 10g - Subract date from next data row 4 38
oracle rollup query 3 37
null value 15 67
report returning null 21 52
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now