Am converting the FHSQL Deski Reports(3.1) to Webi Reports(4.1).For the Below Query i have designed the universe,i.e i have created the derived table for the from clause select statement(which i have made bold in the query) and stored it as x.then i have created the predefined filter in universe for no exists functions(which i have made italics in the query).but while creating the report in webi if drag this filter am getting"no data to retrieve", i have validated SQL, Query Script is Correct.i don't have any idea how to proceed this query ..Suggestion Plss?....
Query:
select distinct a.study, a.dcibook, a.patient, a.site, a.visit, a.page, a.SubStrPage, a.display_sn,
a.DISCONTINUED, Previous_studyenddate, FirstDoseDate, max_offset_Date, PREWITHDL,
LastDoseDate, randomization_date, ConsentDate, MaxUNSCHEDPage, reported_patient_reference,
status_comment_text, visit_number, term_ful, reported_first_name, reported_last_name
from OPS$OCVIEW.EXPECTED_VISIT_DATE_109MS303_t a,
(SELECT A.patient, A.reported_first_name, A.reported_last_name
FROM RXA_DES.PATIENT_POSITIONS A
WHERE A.clinical_study_id =156903)x---(created derived table)
where a.study ='109MS303'
and (max_offset_Date+ @variable('Days of Monitor Delay:') ) < nvl(studyenddate, today_date)
--and (max_offset_Date+ 10 ) < nvl(studyenddate, today_date)
and x.patient(+) = a.patient
and not exists
( SELECT b.study, b.patient, b.clin_plan_eve_name, b.PAGE
FROM RECEIVED_PAPERS b
where b.study='109MS303'
and a.study = b.study
AND B.patient = a.patient
and a.visit = b.clin_plan_eve_name
and a.page = b.page )
AND not exists
(
SELECT *
FROM CRF_IN_CRFTRACK_INDEXED c
WHERE A.STUDY = c.STUDY
and c.study='109MS303'
and c.PATIENT= A.PATIENT
and c.PAGE= A.PAGE
)
AND not exists
(
SELECT *
FROM s109ms303_unexpected_pages d
where d.subject = a.patient
and a.visit = d.visit
and a.page = d.crf_page
)AND not exists
(
SELECT *
FROM s109ms303_page_not_expected e
where e.subject = a.patient
and a.visit = e.visit
and a.page = e.page
)
1 Answer
-
Posted on Dec 05, 2014 at 03:18 PM
I think you should try join the OPS$OCVIEW.EXPECTED_VISIT_DATE_109MS303_t table to each of the following tables with outer joins.
RECEIVED_PAPERS
CRF_IN_CRFTRACK_INDEXED
s109ms303_unexpected_pages
s109ms303_page_not_expected
Here's how to do that...
Create an outer join: Information design tool 4.x - YouTube
Since it looks like each of those table are join on multiple columns you may need to use a complex join which means you are joining multiple columns within one join.
Then create a filter in the Business Layer that looks something like this...
WHERE (IsNull(RECEIVED_PAPERS.ID)
AND IsNull(CRF_IN_CRFTRACK_INDEXED)
AND IsNull(s109ms303_unexpected_pages)
AND IsNull(s109ms303_page_not_expected))
Here is an example from universe of mine...
And the resulting query that gives all of the data from the Account table where there is no match in the DMAccount table...
Hope this gets you on the right track.
Noel
Add a comment
Add a comment