sql server - SQL query pivot INSIDE a case statement erroring -

-

the below code looks should work, only 1 expression can specified in select list when subquery not introduced exists. think refering pivot of avg(testresults) on testrgstr_testname.

i have table full of results tests @ various times of day. want take results sludge, dust , particle tests date range , average them on single row.

this has led use of pivot. issue test names change depending on time of year james, frank others names, has led use of case statement allow different test names , hence different columns after pivot.

apologies errors stripped down version of production code , can't test it.

with testdata (        select testname, convert(decimal(10,2),result) testresult   testreg   resultdate between @pfromdate , @ptodate    , (testrgstr_testname '%sludge%'     or testrgstr_testname '%dust%'     or testrgstr_testname '%particle%') )  select case      when @pcamptype = 'james'         (             select [jdtd james cutting sludge at] s_at, [jdtd james cutting sludge ao] s_ao,                    [jdtd james dust at] d_at, [jdtd james dust ao)] d_ao,                    [jdtd james particle_at] p_at, [jdtd james particle_ao] p_ao              testdata              pivot              (                  avg(testresult)                  testrgstr_testname                  in ([jdtd james cutting sludge at],[jdtd james cutting sludge ao],                      [jdtd james dust at, [jdtd james dust ao],                      [jdtd james particle_at],[jdtd james particle_ao])              ) pvt         )     when @pcamptype = 'frank'         (             select [jdtd frank cutting sludge at] s_at, [jdtd james frank sludge ao] s_ao,                    [jdtd frank dust at] d_at, [jdtd frank dust ao)] d_ao,                    [jdtd frank particle_at] p_at, [jdtd frank particle_ao] p_ao             testdata              pivot              (                  avg(testresult)                  testrgstr_testname                  in ([jdtd james cutting sludge at],[jdtd james cutting sludge ao],                      [jdtd james dust at, [jdtd james dust ao],                      [jdtd james particle_at],[jdtd james particle_ao])              ) pvt         )     else         (             select 'error'         )     end

the problem caused fact should return scalar then:

        select [jdtd james cutting sludge at] s_at, [jdtd james cutting sludge ao] s_ao,                [jdtd james dust at] d_at, [jdtd james dust ao)] d_ao,                [jdtd james particle_at] p_at, [jdtd james particle_ao] p_ao         ...

returns result set, not single value.

you can either use if statement , perform statement each of case - @pcamptype = 'james' / @pcamptype = 'frank' or use dynamic sql:

declare @sql nvarchar(max) = '         testdata (            select testname, convert(decimal(10,2),result) testresult       testreg       resultdate between @pfromdate , @ptodate        , (testrgstr_testname ''%sludge%''         or testrgstr_testname ''%dust%''         or testrgstr_testname ''%particle%'')     )'  declare @innerselectsql nvarchar(max) = n''  if (@pcamptype = 'james')     @innerselectsql = n'             select [jdtd james cutting sludge at] s_at, [jdtd james cutting sludge ao] s_ao,                    [jdtd james dust at] d_at, [jdtd james dust ao)] d_ao,                    [jdtd james particle_at] p_at, [jdtd james particle_ao] p_ao ' else if (@pcamptype = 'frank')     @innerselectsql = n'             select [jdtd frank cutting sludge at] s_at, [jdtd james frank sludge ao] s_ao,                    [jdtd frank dust at] d_at, [jdtd frank dust ao)] d_ao,                    [jdtd frank particle_at] p_at, [jdtd frank particle_ao] p_ao '  set @sql = @sql + char(13) + char(10) + innerselectsql + char(13) + char(10) + n'     testdata              pivot              (                  avg(testresult)                  testrgstr_testname                  in ([jdtd james cutting sludge at],[jdtd james cutting sludge ao],                      [jdtd james dust at, [jdtd james dust ao],                      [jdtd james particle_at],[jdtd james particle_ao])              ) pvt'  dynamic sql uglier, avoid repetition.

Comments

Post a Comment

Popular posts from this blog

java - Run spring boot application error: Cannot instantiate interface org.springframework.context.ApplicationListener -

-
i have spring project , try make use spring boot , run @ embadded tomcat following : https://spring.io/guides/gs/rest-service/ this application //@configuration //@enableaspectjautoproxy @springbootapplication @componentscan(basepackages = "gux.prome") public class application { public static void main(string[] args) { springapplication.run(application.class, args); } } if use maven command: mvn spring-boot:run , project starts fine, need debug , run main method @ intelij, exception occurs: exception in thread "main" java.lang.illegalargumentexception: cannot instantiate interface org.springframework.context.applicationlistener : org.springframework.boot.logging.classpathloggingapplicationlistener @ org.springframework.boot.springapplication.createspringfactoriesinstances(springapplication.java:414) @ org.springframework.boot.springapplication.getspringfactoriesinstances(springapplication.java:394) @ org.springframework.bo...
Read more

python - pip wont install .WHL files -

-
when try install .whl gohike typing cmd c:\users\owner\downloads>pip install ta_lib-0.4.9-cp27-none-win_amd64.whl i print out: `processing c:\users\owner\downloads\ta_lib-0.4.9-cp27-none-win_amd64.whl complete output command python setup.py egg_info: traceback <most recent call last>: file "<string>", line 1, in <module> ioerror: [errno 2] no such file or directory:'c:\\users\\owner\\appdata\\lo cal\\temp\\pip-9gwk2c-build\\setup.py' ----------------------------------------- command "python setup.py egg_info" failed error code 1 in c:\users\owner\ap pdata\local\temp\pip-9gwk2c-build\` the part between pip- , -build in pip-9gwk2c-build different on same file. i asked python 2.7.11 pip 8.0.3 can't install gohike whl of ta-lib 0.4.9 few days ago , decided ask differently. can me! you have outdated pip not understand wheels. wheels not supposed have setup.py inside. try upgrading pip pip in...
Read more

Excel VBA "Microsoft Windows Common Controls 6.0 (SP6)" Location Changes -

-
i have excel workbook used generate quotations customers. there main page user inputs data, , there buttons generate various pdfs , send them customer. however, 1 user consistently getting error preventing generating , sending pdfs. i beleive "microsoft windows common controls 6.0 (sp6)". it's installed on users' computers. on mine, location "c:\windows\syswow64\mscomctl.ocx" on others "c:\windows\system32\mscomctl.ocx" the user experiencing issues on 32-bit machine, "mscomctl.ocx" file should in system32. have manually put in there, , pointed common controls reference in vba it. issue seems keep reverting looking in syswow64. since folder doesn't exist, can't find it, , can't produce pdfs. i thought perhaps issue user running older version of office (2007) there users on 2013 , 2016. thought perhaps mixture of 64-bit , 32-bit users. user gets sent workbook other users (as quotation process goes further, gets passed ...
Read more