Skip to Content
author's profile photo Former Member
Former Member

How to Implement Not Exist Function?

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

)

Add a comment
10|10000 characters needed characters exceeded

Related questions

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


    pastedImage_7.png (25.9 kB)
    pastedImage_8.png (33.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Noel Scheaffer

      Hi Noel

      Diffrence i am sharing . Please look at it.

      For simple situations like the ones in you question, there should be little or no difference, as they all will be executed as joins. In more complex queries, the database might not be able to make a join out of the not in and not exists queryes. In that case the queries will get a lot slower. On the other hand, a join may also perform badly if there is no index that can be used, so just because you use a join doesn't mean that you are safe. You would have to examine the execution plan of the query to tell if there may be any performance problems.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.