DyQL Online Demo

New Query Language?

... und doch nicht frei

Variabler Code

Mensch und Maschine

Cooles Tool

Haftung

DyQL Online Demo

<CommentTime>
to_char(COMMENTS.COMMENTTIME, 'DD-Mon-YYYY HH24:Mi')
</CommentTime>

<CommentType>
case COMMENTS.COMMENTTYPE 
	when 0 then 'Form' 
	when 1 then 'Item' 
	else 'Unknown' end
</CommentType>

<sitefilter>SITEID in {siteid}</sitefilter>
<siteid>(
select SITEID from IRV_USERS_SITES where USERNAME = #sq ($account.personalInfo.userName)#
)</siteid>

<CommentFields>(
select
	SUBJECTID, SITEID, ITEMID, ITEMREV, FORMID, FORMREV
,	COMMENTUSERID, COMMENTTIME, COMMENTTYPE, COMMENTTEXT
from
	IRV_CUR_COMMENT
where
	{sitefilter}
)</CommentFields>

<UserFields>(
select USERID, USERNAME, RIGHTSGROUP from IRV_CUR_USER
)</UserFields>

<SiteFields>(
select
	SITEID, SITECOUNTRY, SITENAME
from
	IRV_CUR_SITE
where
	{sitefilter}
)</SiteFields>

<SubjectFields>(
select
	SUBJECTID, SUBJECTNUMBERSTR
from
	IRV_CUR_SUBJECT_DIM
where
	{sitefilter}
)</SubjectFields>

<FormFields>(select FORMID, FORMREV, FORMNAME from IRV_FORM_REVS)</FormFields>
<ItemFields>(select ITEMID, ITEMREV, ITEMQUESTION from IRV_ITEM_REVS)</ItemFields>

var PrematurelyDiscontinued = TERMINATION.C_RDCTERMYN_ITMTERMDISC;
var LastAttendedVisit = TERMINATION.PLW6_ITMTERMDISC;
var StudyTerminationDate = TERMINATION.DT_ITMTERMDATE;
var FollowUp = EOT.C_RDCEOT3_ITMEOT3;
var ScreeningDate = ENROLMENT.DT_ITMSCRDAT;
var InformedConsentDate = ENROLMENT.DT_ITMENRIC;
var subfilter=and SUBJECTNUMBERSTR not like 'SCR%';

select
	SUBJECTS.*
,	{sel, week=1}
,	{sel, week=3}
,	{sel, week=5}
,	{sel, week=7}
,	{sel, week=9}
,	{sel, week=11}
,	{sel, week=13}
,	{sel, week=15}
,	{sel, week=17}
,	{sel, week=19}
from 
	({subject}) SUBJECTS 
,	{from_treatment, week=1}		-- use variable "from_treatment" with attribute "week"
,	{from_treatment, week=3}		-- variables can be set as xml declaration 
,	{from_treatment, week=5}		-- a xml declaration is always within <extends>...</extends>
,	{from_treatment, week=7}		-- <extends> can be set more than once in a script
,	{from_treatment, week=9}		-- attributes are separated by comma
,	{from_treatment, week=11}		-- a comma in an attribute value must be declared as ,#
,	{from_treatment, week=13}		-- quotes or double quotes are usually not part of a syntax declaration
,	{from_treatment, week=15}
,	{from_treatment, week=17}
,	{from_treatment, week=19}
where 
	{where_treatment, week=1}
and	{where_treatment, week=3}
and	{where_treatment, week=5}
and	{where_treatment, week=7}
and	{where_treatment, week=9}
and	{where_treatment, week=11}
and	{where_treatment, week=13}
and	{where_treatment, week=15}
and	{where_treatment, week=17}
and	{where_treatment, week=19}

<sel>		--- [fields for each week] ---
--	TREATMENT{week}."VisitMnemonic Week{week}",
--	TREATMENT{week}."Administered Week{week}",
	to_char(TREATMENT{week}."Admin Date Week{week}", 'DD-MON-YYYY') "Admin Date Week{week}",
--	TREATMENT{week}."Cycle Number Week{week}",
	TREATMENT{week}."Total Dose Week{week}"
</sel>

<subject>	--- [select fields for subject data] ---
(
select * from (
select distinct 
	substr(tSITE.SITECOUNTRY, 2, length(tSITE.SITECOUNTRY)-2) as "Country"
,	tSITE.SITENAME as "SiteName"
--,	tSITE.SITEID as "SITEID"
--,	tSubjectDim.SUBJECTID as "SUBJECTID"
,	tSubjectDim.SUBJECTNUMBERSTR as "Subject Number"
,	DEMOGRAPHY.DT_ITMDOB as "Date of Birth"
,	substr(DEMOGRAPHY.RDCSEX_ITMDEMSEX,1,1) as "Gender"
,	{ScreeningDate} as "Screening Date"
,	{status} as "Status"
,	{Last_Admin_Date} as "Last Admin Date"
--,	TERMINATION.PLW6_ITMTERMDISC as "Last Attended Visit"
,	{reason_for_discontinuation} as "Reason For Discontinuation"
,	EOT.DT_ITMEOT1 as "Last Study Treatment"
from
	(select * from IRV_CUR_SITE where {sitefilter})						tSITE
,	(select * from IRV_CUR_SUBJECT_DIM where {sitefilter} {subfilter})	tSubjectDim
,	(select SUBJECTID, SITEID from IRV_CUR_SUBJECT where {sitefilter})	tSubjectFact
,	(select * from "CV_FRMDEM"  where {sitefilter})						DEMOGRAPHY
,	(select * from "CV_FRMENR"  where {sitefilter})						ENROLMENT
,	(select * from "CV_FRMTERM" where {sitefilter})						TERMINATION
,	(select * from "CV_FRMEOT"  where {sitefilter})						EOT
where
	tSubjectFact.SITEID = tSITE.SITEID
	and tSubjectFact.SUBJECTID = tSubjectDim.SUBJECTID
	and tSubjectDim.SUBJECTID = DEMOGRAPHY.SUBJECTID (+)
	and tSubjectDim.SUBJECTID = ENROLMENT.SUBJECTID (+)
	and tSubjectDim.SUBJECTID = TERMINATION.SUBJECTID (+)
	and tSubjectDim.SUBJECTID = EOT.SUBJECTID (+)
)
)
</subject>

<status>
case
when ({PrematurelyDiscontinued} = 1 and {LastAttendedVisit} is null) then ('Screen.Fail.')
when ({PrematurelyDiscontinued} = 1)	then ('Prematurely Discontinued')
when ({StudyTerminationDate} is not null)	then ('Complete')
when ({FollowUp} = 1)				then ('Follow UP')
when ({Last_Admin_Date} is not null)	then ('Treatment')
when ({ScreeningDate} is not null)		then ('Screening')
when ({InformedConsentDate} is not null)	then ('Enroled')
else (null)
end
</status>

<reason_for_discontinuation>
case
when (TERMINATION.C_RDCTERMYN_ITMTERMDISC is null) then (null)
when (TERMINATION.C_RDCTERMYN_ITMTERMDISC = 0)     then (null) -- Early Withdrawn: Discontinue = No
else -- Yes
coalesce(
	TERMINATION."TXTTERMREASON1_ITMTERMREASON", 
	TERMINATION."TXTTERMREASON2_ITMTERMREASON", 
	TERMINATION."TXTTERMREASON5_ITMTERMREASON", 
	TERMINATION."TXTTERMREASON6_ITMTERMREASON", 
	'NA')
end
</reason_for_discontinuation>

<Last_Admin_Date>	--- [select data for one column in subjects table] ---
(
select 
	max(StudyTreatment.DT_DTMBEVADMYES_ITMBEVADM) "Last Admin Date"
from 
	(select * from "CV_FRMBEV" where {sitefilter}) StudyTreatment
where
	StudyTreatment.SUBJECTID = tSubjectDim.SUBJECTID
group by
	StudyTreatment.SUBJECTID
)
</Last_Admin_Date>

<from_treatment>	--- [define table for data of week 1 to week 13] ---
(
select * from (
select 
	tSubjectDim2.SUBJECTNUMBERSTR Subject{week}
,	tBEV.VISITMNEMONIC "VisitMnemonic Week{week}"
,	to_char(
	case
		when (substr(tBEV.RDCBEVADM_ITMBEVADM,1,3)='Yes') THEN ('Yes')
		when (substr(tBEV.RDCBEVADM_ITMBEVADM,1,2)='No') THEN ('No')
		else (null)
	end) "Administered Week{week}"
,	tBEV.DT_DTMBEVADMYES_ITMBEVADM "Admin Date Week{week}"
,	sum(tBEV.ITMBEVCYCLEV) "Cycle Number Week{week}"
,	sum(tBEV.TXTBEVADMYES_ITMBEVADM) "Total Dose Week{week}"
,	to_char(case when (tBEV.VISITMNEMONIC is not null) then ('1') else (null) end) n{week}
from 
	(select * from "IRV_CUR_SUBJECT_DIM" where {sitefilter}) tSubjectDim2, 
	(select * from "CV_FRMBEV" where {sitefilter}) tBEV
where 
	tSubjectDim2.SUBJECTID = tBEV.SUBJECTID (+)
	and tSubjectDim2.SUBJECTNUMBERSTR not like 'SCR%'
	and tBEV.VISITMNEMONIC = 'Week {week}'
group by 
	tSubjectDim2.SUBJECTNUMBERSTR, 
	tBEV.VISITMNEMONIC, 
	tBEV.RDCBEVADM_ITMBEVADM, 
	tBEV.DT_DTMBEVADMYES_ITMBEVADM
)
) TREATMENT{week}
</from_treatment>

<where_treatment>
SUBJECTS."Subject Number" = TREATMENT{week}.Subject{week} (+)
</where_treatment>

<sitefilter>SITEID in {siteid}</sitefilter>
<siteid>(
select SITEID from IRV_USERS_SITES where USERNAME = #sq ($account.personalInfo.userName)#
)</siteid>

<Tabellen>
LABORBEFUNDWERTE LABWERT, LABORBEFUNDSORTIERUNG SORT
</Tabellen>

<PatientVerknüpfung_und_Laborwertfilter>
LABW.PATIENTNR = LABWERT.PATIENTNR
and STORNIERT = 0
and not (
lower(LABORWERT) like '%storno%'
or lower(LABORWERT) like '%folgt%'
or lower(LABORWERT) like '%erledigt%')
</PatientVerknüpfung_und_Laborwertfilter>

<AufnahmeBedingung>
{PatientVerknüpfung_und_Laborwertfilter}
and ID = LABWERT.ID
and trunc(ERSTELLDATUM) = {ErstesErstellDatum}
and ROWNUM = 1
</AufnahmeBedingung>

<ErstesErstellDatum>(
select min(trunc(ERSTELLDATUM)) from LABORBEFUNDWERTE LABW
where {PatientVerknüpfung_und_Laborwertfilter}
and ID = LABWERT.ID
and trunc(ERSTELLDATUM) < {MedianDatum}
)</ErstesErstellDatum>

<EntlassBedingung>
{PatientVerknüpfung_und_Laborwertfilter}
and ID = LABWERT.ID
and trunc(ERSTELLDATUM) = {LetztesErstellDatum}
and ROWNUM = 1
</EntlassBedingung>

<LetztesErstellDatum>(
select max(trunc(ERSTELLDATUM)) from LABORBEFUNDWERTE LABW
where {PatientVerknüpfung_und_Laborwertfilter}
and ID = LABWERT.ID
and trunc(ERSTELLDATUM) >= {MedianDatum}
)</LetztesErstellDatum>

--Durchschnitt berechnen, um nur zwei Wertspalten zu erhalten (Erster und letzter Laborwert)
<MedianDatum>(
select median(trunc(ERSTELLDATUM)) from LABORBEFUNDWERTE where PATIENTNR = LABWERT.PATIENTNR and STORNIERT = 0
)</MedianDatum>

--Spalten definieren für den ersten LABORWERT
<1_Wert> ( select LABORWERT     from LABORBEFUNDWERTE LABW where {AufnahmeBedingung} )</1_Wert>
<1_Patho>( select PATHOLOGISCH  from LABORBEFUNDWERTE LABW where {AufnahmeBedingung} )</1_Patho>
<1_Datum>( select ERSTELLDATUM  from LABORBEFUNDWERTE LABW where {AufnahmeBedingung} )</1_Datum>
<1_Auftrag>( select LFDNR       from LABORBEFUNDWERTE LABW where {AufnahmeBedingung} )</1_Auftrag>

--Spalten definieren für den letzten LABORWERT
<2_Wert> ( select LABORWERT    from LABORBEFUNDWERTE LABW where {EntlassBedingung}  )</2_Wert>
<2_Patho>( select PATHOLOGISCH from LABORBEFUNDWERTE LABW where {EntlassBedingung}  )</2_Patho>
<2_Datum>( select ERSTELLDATUM from LABORBEFUNDWERTE LABW where {EntlassBedingung}  )</2_Datum>
<2_Auftrag>( select LFDNR      from LABORBEFUNDWERTE LABW where {EntlassBedingung}  )</2_Auftrag>

<Felder1>
KLARTEXT
, ID, NORMALWERT, EINHEIT
, {1_Wert} as Wert1, {1_Patho} as Pathologisch, {1_Datum} as Datum1, {1_Auftrag} as Auftrag1
, {2_Wert} as Wert2, {2_Patho} as Pathologisch, {2_Datum} as Datum2, {2_Auftrag} as Auftrag2
, nvl(SORT.SORTKNZ, '9' || upper(substr(KLARTEXT,1,2))) || '-2' as Sortierung
, {MedianDatum} as MedianDatum
</Felder1>

<Header1>
SORT.ID||': ' as KLARTEXT
, null as ID, null as NORMALWERT, null as EINHEIT
, null as Wert1, null as Patho1, null as Datum1, null as Auftrag1
, null as Wert2, null as Patho2, null as Datum2, null as Auftrag2
, SORTKNZ||'-1' as Sortierung
, null as MedianDatum
</Header1>

<Header2>
'Andere: '    as KLARTEXT
, null as ID, null as NORMALWERT, null as EINHEIT
, null as Wert1, null as Patho1, null as Datum1, null as Auftrag1
, null as Wert2, null as Patho2, null as Datum2, null as Auftrag2
, '9' as Sortierung
, null as MedianDatum
</Header2>

<where_exists_in_Laborsort>
where {Patient_und_Laborwertfilter}
and LABWERT.ID = SORT.ID
</where_exists_in_Laborsort>

<where_not_exists_in_Laborsort>
where {Patient_und_Laborwertfilter}
and LABWERT.ID = SORT.ID (+)
and SORT.SORTID is null
</where_not_exists_in_Laborsort>

<Patient_und_Laborwertfilter>
LABWERT.PATIENTNR = '{patientnr}'
and STORNIERT = 0
and not (
lower(LABORWERT) like '%storno%'
or lower(LABORWERT) like '%folgt%'
or lower(LABORWERT) like '%erledigt%')
</Patient_und_Laborwertfilter>