sql server 2000 - Dynamic sql statement to generate multiple large case statements -

-



i staging data legacy sql server 2000 system i'm limited on how can write following query. came query viable solution doesn't use temp tables after posted question yesterday. i need sql server 2000 compatible alternative cte query

i need write type of query many tables , there instances have upwards of 20 nested case statements. there way can use dynamic sql generate these queries? otherwise i'm not sure it's going can maintain. here's example. thank you!

select cst.*       ,modifieddatetime = case                               when dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(dim.modifiedtime, ''), coalesce(dim.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(dlm.modifiedtime, ''), coalesce(dlm.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(dlt.modifiedtime, ''), coalesce(dlt.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(dnc.modifiedtime, ''), coalesce(dnc.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(itl.modifiedtime, ''), coalesce(itl.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(pdg.modifiedtime, ''), coalesce(pdg.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(pyt.modifiedtime, ''), coalesce(pyt.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(ssp.modifiedtime, ''), coalesce(ssp.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(tgh.modifiedtime, ''), coalesce(tgh.modifieddate, ''))                                , dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, '')) > dateadd(second, coalesce(vig.modifiedtime, ''), coalesce(vig.modifieddate, '')) dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, ''))                               else case                                        when dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(dim.modifiedtime, ''), coalesce(dim.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(dlm.modifiedtime, ''), coalesce(dlm.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(dlt.modifiedtime, ''), coalesce(dlt.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(dnc.modifiedtime, ''), coalesce(dnc.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(itl.modifiedtime, ''), coalesce(itl.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(pdg.modifiedtime, ''), coalesce(pdg.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(pyt.modifiedtime, ''), coalesce(pyt.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(ssp.modifiedtime, ''), coalesce(ssp.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(tgh.modifiedtime, ''), coalesce(tgh.modifieddate, ''))                                         , dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, '')) > dateadd(second, coalesce(vig.modifiedtime, ''), coalesce(vig.modifieddate, '')) dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, ''))                                        else case                                                 when dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(dim.modifiedtime, ''), coalesce(dim.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(dlm.modifiedtime, ''), coalesce(dlm.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(dlt.modifiedtime, ''), coalesce(dlt.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(dnc.modifiedtime, ''), coalesce(dnc.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(itl.modifiedtime, ''), coalesce(itl.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(pdg.modifiedtime, ''), coalesce(pdg.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(pyt.modifiedtime, ''), coalesce(pyt.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(ssp.modifiedtime, ''), coalesce(ssp.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(tgh.modifiedtime, ''), coalesce(tgh.modifieddate, ''))                                                  , dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, '')) > dateadd(second, coalesce(vig.modifiedtime, ''), coalesce(vig.modifieddate, '')) dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, ''))                                                 else case                                                          when dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(dim.modifiedtime, ''), coalesce(dim.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(dlm.modifiedtime, ''), coalesce(dlm.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(dlt.modifiedtime, ''), coalesce(dlt.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(dnc.modifiedtime, ''), coalesce(dnc.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(itl.modifiedtime, ''), coalesce(itl.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(pdg.modifiedtime, ''), coalesce(pdg.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(pyt.modifiedtime, ''), coalesce(pyt.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(ssp.modifiedtime, ''), coalesce(ssp.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(tgh.modifiedtime, ''), coalesce(tgh.modifieddate, ''))                                                           , dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, '')) > dateadd(second, coalesce(vig.modifiedtime, ''), coalesce(vig.modifieddate, '')) dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, ''))                                                          else case                                                                   when dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(dim.modifiedtime, ''), coalesce(dim.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(dlm.modifiedtime, ''), coalesce(dlm.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(dlt.modifiedtime, ''), coalesce(dlt.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(dnc.modifiedtime, ''), coalesce(dnc.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(itl.modifiedtime, ''), coalesce(itl.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(pdg.modifiedtime, ''), coalesce(pdg.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(pyt.modifiedtime, ''), coalesce(pyt.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(ssp.modifiedtime, ''), coalesce(ssp.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(tgh.modifiedtime, ''), coalesce(tgh.modifieddate, ''))                                                                    , dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, '')) > dateadd(second, coalesce(vig.modifiedtime, ''), coalesce(vig.modifieddate, '')) dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, ''))                                                                   else case                                                                            when dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(chd.modifiedtime, ''), coalesce(chd.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(cms.modifiedtime, ''), coalesce(cms.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(cpm.modifiedtime, ''), coalesce(cpm.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(csg.modifiedtime, ''), coalesce(csg.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(cst.modifiedtime, ''), coalesce(cst.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(dim.modifiedtime, ''), coalesce(dim.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(dlm.modifiedtime, ''), coalesce(dlm.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(dlt.modifiedtime, ''), coalesce(dlt.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(dnc.modifiedtime, ''), coalesce(dnc.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(itl.modifiedtime, ''), coalesce(itl.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(pdg.modifiedtime, ''), coalesce(pdg.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(pyt.modifiedtime, ''), coalesce(pyt.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(ssp.modifiedtime, ''), coalesce(ssp.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(tgh.modifiedtime, ''), coalesce(tgh.modifieddate, ''))                                                                             , dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, '')) > dateadd(second, coalesce(vig.modifiedtime, ''), coalesce(vig.modifieddate, '')) dateadd(second, coalesce(ctg.modifiedtime, ''), coalesce(ctg.modifieddate, ''))                                                                        end                                                               end                                                      end                                             end                                    end                           end dbo.custtable                           cst      left join dbo.cashdiscount              chd on chd.cashdiscountcode    = cst.cashdiscount                                                 , chd.companyid           = cst.companyid      left join dbo.commissionsalesgrouptable cms on cms.groupid             = cst.salesgroup                                                 , cms.companyid           = cst.companyid      left join dbo.custpaymmodetabletable    cpm on cpm.paymmode            = cst.paymmode                                                 , cpm.companyid           = cst.companyid      left join dbo.customergroup             csg on csg.customergroup       = cst.customergroup                                                 , csg.companyid           = cst.companyid      left join dbo.custstatisticsgrouptable  ctg on ctg.custstatisticsgroup = cst.statisticsgroup                                                 , ctg.companyid           = cst.companyid





wiki

Comments

Post a Comment

Popular posts from this blog

python - Read npy file directly from S3 StreamingBody -

-
i have many .npy (numpy) , .json files saved in s3, need download , load these files. for json files downloading files , loading dictionary using following code: obj = s3.object(bucket, key) response = obj.get() body = response['body'].read().decode('utf-8') data = json.loads(body) that way avoid writing .json file disk , reading it. now want same .npy files, equivalent? currently downloading file, saving temp file on disk , loading np.load avoid if possible. following https://stackoverflow.com/a/28196540/3509999 : obj = s3.object(bucket, key) io.bytesio(obj.get()["body"].read()) f: # rewind file f.seek(0) arr = np.load(f) wiki
Read more

kotlin - Out-projected type in generic interface prohibits the use of metod with generic parameter -

-
i start using kotlin , defined interface this: interface aadapter<vh : recyclerview.viewholder> { fun oncreateaviewholder(parent: viewgroup): vh fun onbindaviewholder(v: vh, position: int) } and when try use on code: class klasa ( private val adapter: aadapter<*> ) { fun dosth(){ //... val vh = this.adapter.oncreateaviewholder(parent) //on below line error adapter.onbindaviewholder(v, position) //... } } i error out-projected type 'aadapter<*>' prohibits use of 'public abstract fun onbindaviewholder(v: t, position: int): unit i tied add "in" or "out" definition i'm confused. how allow it. replace aadapter<*> aadapter<recyclerview.viewholder> . class klasa ( private val adapter: aadapter<recyclerview.viewholder> ) { // ... because have declared aadapter has type parameter vh type subclass of recyclerview....
Read more

Asterisk AGI Python Script to Dialplan does not work -

-
i'm stuck project have check database of sip extension incoming caller id , dial sip extension. below receive when run script on asterisk cli. -- registered sip '9125' @ 192.168.1.102:50142 > saved useragent "zoiper rv2.8.40" peer 9125 [2017-08-22 13:53:33] notice[5095]: chan_sip.c:24558 handle_response_peerpoke: peer '9125' reachable. (17ms / 2000ms) == using sip rtp tos bits 184 == using sip rtp cos mark 5 -- executing [0112617769@from-trunk:1] answer("sip/obitrunk1-0000000d", "") in new stack > 0x1ca50d0 -- probation passed - setting rtp source address 192.168.1.98:16834 -- executing [0112617769@from-trunk:2] agi("sip/obitrunk1-0000000d", "test.py") in new stack -- launched agi script /var/lib/asterisk/agi-bin/test.py args: ['/var/lib/asterisk/agi-bin/test.py'] env line: agi_request: test.py env line: agi_channel: sip/obitrunk1-0000000d env line: agi_language: en env line: a...
Read more