
                with

                                normativ_list as (
                                    select
                                        dcm.DrugComplexMnn_id,
                                        max(dnls.DrugNormativeListSpec_IsVK) as DrugNormativeListSpec_IsVK
                                    from
                                        v_DrugNormativeListSpecTorgLink dnlstl
                                        inner join v_DrugNormativeListSpec dnls on dnls.DrugNormativeListSpec_id = dnlstl.DrugNormativeListSpec_id
                                        inner join rls.v_Prep p on p.TRADENAMEID = dnlstl.DrugNormativeListSpecTorg_id
                                        inner join rls.v_Drug d on d.DrugPrep_id = p.Prep_id
                                        inner join rls.v_DrugComplexMnn dcm on dcm.DrugComplexMnn_id = d.DrugComplexMnn_id
                                        inner join rls.v_DrugComplexMnnName dcmn on dcmn.DrugComplexMnnName_id = dcm.DrugComplexMnnName_id
                                        inner join v_DrugNormativeList dnl on dnl.DrugNormativeList_id = dnls.DrugNormativeList_id
                                        left join v_DrugNormativeListSpecFormsLink dnlsfl on dnlsfl.DrugNormativeListSpec_id = dnls.DrugNormativeListSpec_id
                                            and dcm.CLSDRUGFORMS_ID = dnlsfl.DrugNormativeListSpecForms_id
                                    where 1=1 and 
                            (case when exists(select DrugNormativeListSpec_id from v_DrugNormativeListSpecFormsLink where DrugNormativeListSpec_id = dnls.DrugNormativeListSpec_id)
                                then dnlsfl.DrugNormativeListSpecForms_id
                                else coalesce(dcm.CLSDRUGFORMS_ID,0)
                            end) = coalesce(dcm.CLSDRUGFORMS_ID,0)
                         and dnl.DrugNormativeList_BegDT <= '2022-02-17' and (dnl.DrugNormativeList_EndDT > '2022-02-17' or dnl.DrugNormativeList_EndDT is null) and dnl.PersonRegisterType_id = '22' and dnl.WhsDocumentCostItemType_id = '2' and dcm.DrugComplexMnnGroupType_id = 2
                                    group by
                                        dcm.DrugComplexMnn_id
                                )
                            
                select
                    d.Drug_id as "Drug_rlsid",
                    d.DrugComplexMnn_id as "DrugComplexMnn_id",
                    d.Drug_Code as "Drug_Code",
                    rtrim(d.Drug_Name) as "Drug_Name"
                    ,coalesce(list.DrugNormativeListSpec_IsVK, 1) as Drug_IsKEK
                from
                    
                            normativ_list list
                            inner join rls.v_Drug d on d.DrugComplexMnn_id = list.DrugComplexMnn_id
                        
                where
                    1=1 and lower(d.Drug_Name) like lower('трамал%')
                order by
                    d.Drug_Name
                limit 500;
            
