SQL inner join on alias (for XML) -


i have running query need expand xml hierarchy.

the existing query (working):

select a.fields, (select c.fields c),  (select d.fields d), (select e.fields e)  --repair orders, parts, labor, narrative

i need create level @ b (this job order repair orders, , aliased bb):

--repair orders, job id (job id/parts, job id/labor, job id/narrative)  select a.fields, select b.fields, (select c.fields c),  (select d.fields d), (select e.fields e) b) bb

so here's code (this inner join killing me): (also, think repair narratives c , once going need add d & e)

it's inner join @ comments line stopping me:

declare @oemdealercode nvarchar(20),@sdate smalldatetime,@edate smalldatetime,@dmxservicerojobstatus_readytoinvoice int set @sdate = '01/01/2013' set @edate = '12/31/2013' set @dmxservicerojobstatus_readytoinvoice = dbo.[fn_dmxsysgetenumitemvalue](n'dmxservicerojobstatus', n'readytoinvoice')   -- jobid hierarchy select  ff.qualifyingrox, ff.jobid, ff.jobname,  (     ---------------------------------------------------------------------------------------------------     select distinct --repair narrative         concern, cause, correction, causemore, concernmore, correctionmore             (         select distinct              top (100) percent dbo.dmxdealerinformationtable.oemdealercode,              dbo.dmxservicerotable.roid,              dbo.dmxservicerojob.jobid,              dbo.dmxservicerojob.status,              dmxservicecccstatement_1.text concern,              dbo.dmxservicecccstatement.text cause,              dmxservicecccstatement_2.text correction,              dbo.dmxservicerojob.customcausetext causemore,              dbo.dmxservicerojob.customconcerntext concernmore,              dbo.dmxservicerojob.customcorrectiontext correctionmore,              dmxservicecccstatement_2.recversion expr5,              max(dbo.dmxservicerojob.recversion) expr4,              max(dbo.dmxservicecccstatement.recversion) expr3,              max(dmxservicecccstatement_1.recversion) expr1,              max(dmxservicecccstatement_2.recversion) expr2         dbo.dmxservicerojob (nolock) inner join             dbo.dmxdealerinformationtable (nolock) inner join             dbo.dmxservicerotable (nolock) on dbo.dmxdealerinformationtable.partition = dbo.dmxservicerotable.partition on              dbo.dmxservicerojob.rotableref = dbo.dmxservicerotable.recid left outer join             dbo.dmxservicecccstatement dmxservicecccstatement_2 on              dbo.dmxservicerojob.correctionref = dmxservicecccstatement_2.recid left outer join             dbo.dmxservicecccstatement on dbo.dmxservicerojob.causeref = dbo.dmxservicecccstatement.recid left outer join             dbo.dmxservicecccstatement dmxservicecccstatement_1 on              dbo.dmxservicerojob.concernref = dmxservicecccstatement_1.recid         group dbo.dmxdealerinformationtable.oemdealercode, dbo.dmxservicerotable.roid, dbo.dmxservicerojob.jobid, dbo.dmxservicerojob.status, dmxservicecccstatement_1.text, dbo.dmxservicecccstatement.text,              dmxservicecccstatement_2.text, dbo.dmxservicerojob.customcausetext, dbo.dmxservicerojob.customconcerntext,              dbo.dmxservicerojob.customcorrectiontext, dmxservicecccstatement_2.recid, dmxservicecccstatement_2.partition,              dbo.dmxservicecccstatement.recversion, dbo.dmxservicecccstatement.partition, dmxservicecccstatement_1.partition,              dbo.dmxservicerojob.recversion, dbo.dmxservicerojob.recid, dbo.dmxservicerojob.partition,              dmxservicecccstatement_1.recversion, dmxservicecccstatement_1.recid, dbo.dmxservicecccstatement.recid,              dmxservicecccstatement_2.recversion         having dbo.dmxdealerinformationtable.oemdealercode = @oemdealercode          --and dbo.dmxservicerotable.roid = ff.qualifyingrox          , dbo.dmxservicerojob.status=@dmxservicerojobstatus_readytoinvoice         order expr4 desc, expr3 desc, expr1 desc, expr2 desc         ) cc          inner join ff on cc.roid = ff.qualifyingrox     --------------------------------------------------------------------------------------------------- )  (     select distinct --repair narrative         ee.jobid, ee.jobname, ee.qualifyingrox           (         select distinct top (100) percent          dbo.dmxservicerotable.roid qualifyingrox,          dbo.dmxservicerojob.jobid jobid,          max(distinct dbo.dmxservicerojob.name) jobname         dbo.dmxservicerotable (nolock) inner join dbo.dmxservicerojob (nolock) on dbo.dmxservicerotable.recid = dbo.dmxservicerojob.rotableref         group dbo.dmxservicerotable.roid, dbo.dmxservicerojob.jobid         order qualifyingrox, dbo.dmxservicerojob.jobid     ) ee  ) ff xml path ('jobdetail'), root ('jobs'), type


Comments

Post a Comment

Popular posts from this blog

android - Get AccessToken using signpost OAuth without opening a browser (Two legged Oauth) -

i using signpost oauth access data magento server. have read various tutorials on same , reach point open browser user can enter credentials. however, per requirement have automate part. hence, user should not browser page. have set-up done on server side ( magento ), hit url , returned call page. same through program in android. i have tried below, consumer = new commonshttpoauthconsumer(key, secret); provider = new commonshttpoauthprovider(oauth_init_url,access_token_url, authorize_url); try { provider.retrieverequesttoken(consumer, oauth.out_of_band); log.d("tokens" , consumer.gettoken() + " -- " + consumer.gettokensecret()); } and request tokens. dont know how bypass next step. tried directly accessing accesstoken (stupid of me) provider.retrieveaccesstoken(consumer, "mycallback://callback"); no luck, ends in oauth.signpost.exception.oauthnotauthorizedexception: authorization failed (server replied 401). ...
Read more

org.mockito.exceptions.misusing.InvalidUseOfMatchersException: mockito -

getting error org.mockito.exceptions.misusing.invaliduseofmatchersexception: invalid use of argument matchers! 1 matchers expected, 2 recorded. exception may occur if matchers combined raw values: //incorrect: somemethod(anyobject(), "raw string"); when using matchers, arguments have provided matchers. example: //correct: somemethod(anyobject(), eq("string matcher")); at when( getprogramservice .callservice(any(getprogramtype.class))) .thenreturn(jaxbresponse); please explain error , possible resolution the code posted looks fine; don't see problem it. code before or above causing exception. key here: invalid use of argument matchers! 1 matchers expected, 2 recorded. any , used above, doesn't return "special kind of null" or "special kind of getprogramtype"; don't exist. instead, returns null , side effect puts matcher on stack. whe...
Read more

google shop client API returns 400 bad request error while adding an item -

i'm working on solution add items in google shopping market java application. following tutorial real mess because of missing classes of laste release, after setting 1.5-beta maven managed make base example typecheck correctly classes. still can make simple task of adding 1 item. instead of posting whole code i'm posting part creates product feed. private product createproduct(string id) { product product = new product(); product.title = "red wool sweater"; product.content = new content( "text", "comfortable , soft, sweater keep warm on " + "cold winter nights. red , blue stripes."); product.appcontrol = new appcontrol(); product.appcontrol.addrequireddestination("productads"); product.externalid = id; product.lang = "it"; product.country = "it"; product.condition = "nuovo"; product.price = new price("eu...
Read more