Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to create a view along with  columns ?

Posted on 2014-02-26
3
Medium Priority
?
367 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 78

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

571 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