>>
>>Until that name is "FOO" and you have to scroll up two pages to read and memorize its definition before going back to the bottom.
>>
>>Walter,
>
>That is a nice way to say something, just tell something that looks clever but have no meaning at all :)
>
>You challenged me and asked for code, may I do the same and ask for code that shows CTEs should not be used, less readable or subqueries are perfect, whatever. I ask that because I didn't see a solid thing from you yet, just claims with constraints.
Again, what do you mean by constraints? The argument has been readbility from the beginning. The only technical constraints were brought up by you in your "VeryExpensiveFunction()" argument.
As for an example see below.
As you notice this is an inline function so that this can be re-used else where. Write once, maintain and debug in one place.
Also notice this is using a recursive CTE (even using another CTE), something we already agreed upon of being very useful.
Also note the construct of the Query is such that the selection of record is in the first half, the second half is joining additional information to it. It does not constain the query further.
the OUTER APPLY (i've grown in loving the outer apply) subqueries I can tune and debug exactly in the place where they are used.
The subqueries at the beginning are used to collect, aggregrate and analyse, passing their calculations up to the upper query for further processing. The subqueries themselves have no well known definition (eg invoice totals) and therefore writing them a CTEs (also inline function or view for that matter) would make it a lot more difficult to follow what actually is happening.
CREATE FUNCTION [dbo].[GetEggInfo]
(
@egg_id int,
@Cycleno char(10),
@Acu_id char(10)
)
RETURNS TABLE
AS
RETURN
(
WITH
eggcycle AS
(
SELECT DISTINCT lbd_eggid, lbd_acu_id, lbd_cycleno, lbd_thawdest, lbd_orcycle, lbd_orid
FROM t_labdetails
WHERE (lbd_thawdest > SPACE(10) OR lbd_orcycle > SPACE(10)) AND lbd_eggid = @egg_id AND lbd_cycleno NOT IN (lbd_orcycle, lbd_thawdest)
),
Labevents AS
(
SELECT @Cycleno as cycleno, @Acu_id as Acu_id, @Egg_id as eggid, 1 as level
UNION ALL
SELECT lbd_cycleno, lbd_acu_id, lbd_eggid, Level+1
FROM Labevents
INNER JOIN eggcycle ON ((cycleno = Lbd_thawdest AND acu_id = Lbd_acu_id) OR (cycleno = lbd_orcycle AND acu_id = lbd_orid))
WHERE level < 6
)
SELECT *
FROM (
SELECT
oocyte_State = CASE
WHEN ISNULL(MIN(CASE WHEN Thaw = 1 OR Cryo = 1 THEN row END),99999) < ISNULL(MAX(Fert_Row),99998) THEN 'Thawed'
WHEN MIN(CASE WHEN lba_insem|lba_micro|lba_ivf = 1 THEN row END) > 0 THEN 'Fresh'
WHEN MAX(CASE WHEN Has_collection =1 AND fert_row IS NULL THEN 1 END) = 1 THEN 'Fresh'
WHEN MIN(oocyteSource) IS NULL OR MAX(Has_movedin) = 1 THEN 'Unknown'
END
, oocyte_SourceID = MAX(oocyteSource)
, Emb_state =
CASE
WHEN ISNULL(MAX(Fert_Row),99998) >= MAX(First_cycle_row) THEN 'Fresh'
WHEN MAX(First_cycle_row) > MAX(Fert_Row) AND MAX(Case when lbd_cycleno = @cycleno AND lba_thawed = 1 THEN 1 END) =1 THEN 'Thawed'
WHEN MIN(CASE WHEN thaw = 1 AND rowCycle = 1 AND lbd_day BETWEEN 1 AND 7 THEN 1 END) = 1 THEN 'Thawed'
END
, Insem_method = MAX(Insem_method)
, Collected = MAX(CASE WHEN lba_collected = 1 AND lbd_cycleno = @Cycleno THEN 1 END)
, Insem_date = MAX(Insem_date)
, Pgd_biopsy = MAX(CAST(lba_PGDbiopsy as int))
, PGD_Cycleno = MAX(CASE WHEN lba_PGDbiopsy = 1 THEN lbd_cycleno END)
, PGD_Day = MAX(CASE WHEN lba_PGDbiopsy = 1 THEN lbd_day END)
, MAX(CASE WHEN Cryo_Row <= ISNULL(Fert_row,9999) and (Lbd_day = 0 OR lbd_stage <= 10) THEN 1 END) EggFreezeCount
, MAX(CASE WHEN Cryo_Row > Fert_row OR (Cryo_row >= 0 AND Fert_row IS NULL AND lbd_day > 0 AND lbd_stage > 10) THEN 1 END) EmbFreezeCount
, MAX(lbd_semsource) as SemSource
, MAX(CASE WHEN lba_insem|lba_micro|lba_ivf = 1 THEN lbd_cycleno END) as insemcycle
, MAX(CASE WHEN lba_donoth = 1 AND lbd_cycleno = @cycleno THEN 1 END) as donated
, MAX(CASE WHEN lba_usepat = 1 AND lbd_cycleno = @cycleno THEN 1 END) as Et
, MAX(CASE WHEN lba_insem|lba_micro|lba_ivf = 1 AND lbd_cycleno = @cycleno THEN 1 END) as Insem
, MAX(CASE WHEN lba_micro = 1 AND lbd_cycleno = @cycleno THEN 1 END) as ICSI
, MAX(CASE WHEN lba_micro = 0 AND lba_ivf = 1 AND lbd_cycleno = @cycleno THEN 1 END) as IVF
, MAX(CASE WHEN lbd_orcycle = @Cycleno AND lbd_orid <> lbd_acu_id THEN 1 END) as ReceivedFromDonor
, MAX(CASE WHEN lba_moved = 1 AND lbd_cycleno = @cycleno THEN 1 END) as Moved_in
, MAX(CASE WHEN Max_prevrow < ISNULL(Fert_row,999) AND C_Cryo_row <= Max_prevrow THEN 1 END) as Egg_thaw
, MAX(CASE WHEN Thaw_row > Fert_Row OR Fert_Row < ISNULL(first_cycle_row,99999) THEN 1 END) as Emb_thaw
FROM (SELECT *
, Cryo_row = CASE WHEN cryo = 1 AND lbd_cycleno = @Cycleno THEN Row END
, C_Cryo_row = CASE WHEN cryo = 1 THEN Row END
, C_Thaw_row = CASE WHEN cryo = 1 THEN Row END
, Pgd_row = CASE WHEN lba_pgdbiopsy =1 THEN row END
, Thaw_row = MIN(CASE WHEN lba_thawed = 1 AND lbd_cycleno = @Cycleno THEN Row END) OVER ()
, Prev_Thaw_row = MAX(CASE WHEN lba_thawed = 1 AND lbd_thawdest = @Cycleno AND lbd_cycleno <> lbd_thawdest THEN Row END) OVER ()
, First_cycle_row = MIN(CASE WHEN lbd_cycleno = @cycleno THEN row END) OVER()
, Fert_Row = MIN(CASE WHEN lba_insem|lba_micro|lba_ivf|lba_fertcheck = 1 OR (lba_moved = 1 AND (lbd_stage > 10 OR (lbd_day > 0 AND lbd_stage = 0))) OR lbd_stage > 10 OR lbd_day > 2 THEN Row END) OVER ()
, Has_collection = MIN(CASE WHEN lba_collected = 1 AND lba_moved = 0 THEN 1 END) OVER ()
, Has_movedin = MIN(CASE WHEN lba_moved = 1 THEN Row END) OVER ()
, Max_prevRow = MAX(CASE WHEN (lbd_thawdest = @Cycleno AND lbd_cycleno <> lbd_thawdest) OR (lbd_orcycle = @Cycleno AND lbd_cycleno <> lbd_orcycle) THEN row END) OVER()
FROM (
SELECT
lbd_Eggid
, lbd_cycleno
, lbd_orcycle
, lbd_thawdest
, lbd_orid
, lbd_acu_id
, lbd_Date
, lbd_time
, lbd_day
, lbd_stage
, row = ROW_NUMBER() OVER (ORDER BY lbd_date, lbd_time)
, rowcycle = ROW_NUMBER() OVER (PARTITION BY lbd_cycleno ORDER BY lbd_date, lbd_time)
, Cryo = CASE WHEN lba_storeoth|lba_storepat|lba_storeres = 1 THEN 1 END
, Cryo_day = CASE WHEN lba_storeoth|lba_storepat|lba_storeres = 1 THEN lbd_Day END
, Thaw = CASE WHEN lba_thawed = 1 THEN 1 END
, oocytesource = CASE WHEN lba_collected = 1 AND lba_moved = 0 THEN lbd_acu_id END
, lbd_semsource
, Insem_Method = CAST(
CASE
WHEN lba_Micro = 1 THEN 'ICSI'
WHEN lba_ivf = 1 THEN 'IVF'
WHEN lba_insem = 1 THEN RTRIM(lba_procname)
END as varchar(40))
, Insem_date = CASE WHEN lba_Micro|lba_ivf|lba_insem = 1 THEN lbd_date END
, lba_insem
, lba_ivf
, Lba_micro
, lba_PgdBiopsy
, lba_moved
, lba_collected
, lba_donoth
, lba_usepat
, lba_thawed
, lba_fertcheck
FROM Labevents
INNER JOIN t_labdetails ON LBD_cycleno = cycleno AND lbd_eggid = eggid
INNER JOIN s_labaction ON lbd_Action = lba_action_id
) as Emb
) as emb
) as emb
CROSS APPLY (SELECT oocytesource = CAST(
CASE
WHEN oocyte_SourceID = trm_acu_id THEN 'Own'
WHEN oocyte_SourceID = trm_partner THEN 'Partner'
WHEN oocyte_SourceID IS NOT NULL THEN 'Donor'
END as varchar(10))
,trm_acu_id, trm_partner, trm_semsource, trm_eggsource
FROM t_treatmen
WHERE trm_cycleno = @Cycleno
) as ThisCycle
OUTER APPLY (SELECT TOP 1
Sem_source = CAST(
CASE
WHEN sem_acu_id = Cycle.trm_acu_id THEN 'Own'
WHEN sem_acu_id = Cycle.trm_partner THEN 'Partner'
WHEN sem_acu_id > SPACE(10) THEN 'Donor'
END as varchar(10))
, Sem_State = CAST(
CASE
WHEN Sem_oldSemenid = 0 THEN 'Fresh'
WHEN Sem_oldsemenid <> 0 THEN 'Thawed'
ELSE 'Unknown'
END as varchar(10))
, sem_semenid as insem_semenid
, CASE WHEN sem_oldsemenid > 0 THEN sem_oldsemenid ELSE sem_semenid END as Fresh_semenid
, Sem_acu_id
, sem_prodmethod
FROM (
SELECT sem_acu_id, 1 as seq, sem_semenid, sem_oldsemenid, sem_prodmethod FROM t_semenmain WHERE SemSource = sem_semenid
UNION
SELECT sem_acu_id, 2, sem_semenid, sem_oldsemenid, sem_prodmethod FROM t_semenmain WHERE sem_cycleno = insemcycle
) as Sem
OUTER APPLY (SELECT trm_acu_id, trm_cycleno, trm_partner FROM t_treatmen WHERE trm_cycleno = insemcycle) as cycle
ORDER BY seq
) as sem
OUTER APPLY (SELECT TOP 1 CAST(LBD_PGDGENRESULT as varchar(200)) as LBD_PGDGENRESULT, CAST(RTRIM(A.Cnt_option) as varchar(100)) as PDG_ClinResult
, A.cnt_optioncode as PGDResultCode
, B.cnt_option as PGD_CONCLUSION
, lbd_cycleno as PDG_Cycleno
FROM t_Labdetails
INNER JOIN s_labaction ON lbd_action = lba_action_id AND lba_pgd = 1
LEFT JOIN s_contents A ON LBD_PGDCLINRESULT = cnt_content_id
LEFT JOIN s_contents B ON LBD_PGD_CONCLUSION = B.cnt_content_id
WHERE lbd_eggid = @Egg_ID
ORDER BY lbd_Date DESC, LBD_TIME DESC
) as PGDRes
)
