sql server - Why same EXISTS returns different result -

-

each policynumber can have multiple classcode. goal eliminate whole policynumber related columns including classcode's if @ least 1 classcode chosen eliminated. have cte1 policynumber , writtenpremium(wp) , have table tblclasscodesplazacommercial policynumber , classcode. in where clause im choosing:

where       exists (                     select  distinct policynumber                         tblclasscodesplazacommercial                        policynumber not in (select  policynumber tblclasscodesplazacommercial  classcode =5151)                     )

then if select * cte3 classcode =5151 still have policies classcode 5151

enter image description here

the entire select statement below: 

 select                      cte1.policynumber,                 cte1.transactioneffectivedate,                 cc.classcode,                 case                     when row_number() on (partition cte1.quoteid, cte1.policynumber, cc.transactioneffectivedate order (select 0))=1 cte1.wp                      else 0                 end  wp--,            cte1 inner join tblclasscodesplazacommercial cc on cte1.policynumber=cc.policynumber , cte1.quoteid=cc.quoteid , cte1.transactioneffectivedate=cc.transactioneffectivedate            exists (select distinct policynumber tblclasscodesplazacommercial policynumber not in (select  policynumber tblclasscodesplazacommercial  classcode =5151))

but then, if declare @policiesthatdonthaveclasscodes table policynumber's dont have classcode 5151 , use in exists command - works. 

declare @policiesthatdonthaveclasscodes table (policynumber varchar(100))  insert @policiesthatdonthaveclasscodes select distinct policynumber    tblclasscodesplazacommercial                                                                              policynumber not in (select  policynumber tblclasscodesplazacommercial  classcode =5151) select                   cte1.policynumber,             cte1.transactioneffectivedate,             cc.classcode,             case                 when row_number() on (partition cte1.quoteid, cte1.policynumber, cc.transactioneffectivedate order (select 0))=1 cte1.wp                  else 0             end  wp--,        cte1 inner join tblclasscodesplazacommercial cc on cte1.policynumber=cc.policynumber              , cte1.quoteid=cc.quoteid              , cte1.transactioneffectivedate=cc.transactioneffectivedate        exists (select * @policiesthatdonthaveclasscodes t  t.policynumber=cc.policynumber )

what difference? both sub-queries returns same result set. why in first case doesn't work, in second (when declared @policiesthatdonthaveclasscodes) - works? how can achieve same result without declaring table variables?

in first sample provide 

where exists (select  distinct policynumber                     tblclasscodesplazacommercial                    policynumber not in (select  policynumber                                                 tblclasscodesplazacommercial                                                  classcode =5151))

there no correlation between outer query , subquery. evaluate subquery in isolation and, since query returns row, where exists … true.

in second example correlate subquery outer query, that's t.policynumber = cc.policynumber does, because cc outside subquery:

where exists (select * @policiesthatdonthaveclasscodes t                  t.policynumber=cc.policynumber )

so result of subquery (potentially) different every single row of outer query.

thus 2 where clauses evaluate differently.

edit

possible reworking of first predicate

where not exists (select 1 tblclasscodesplazacommercial t                            t.policynumber = cc.policynumber                              , t.classcode = 5151)

Comments

Post a Comment

Popular posts from this blog

c - zlib and gdi32 with OpenSSL? -

-
this follow question undefined reference rsa_generate_key in openssl? have openssl program generate rsa key, follows : #include <stdio.h> #include <openssl/rsa.h> #include <openssl/pem.h> int main(int argc, char* argv[]) { rsa *r = null; int ret; r = rsa_new(); bignum *bne = null; ret = rsa_generate_key_ex(r, 2048, bne, null); return 0; } when compile gcc -i../include rsatest.c -lcrypto -l. i undefined reference errors functions in libraries gdi32 , zlib. so correct this, have add -lz , -lgdi32, as gcc -i../include/ rsatest.c -lcrypto -lz -lgdi32 -l. my question is, aren't static libcrypto , libssl self sufficient? in why having add libraries make work? there option should have added while compiling library prevent this? i'm using 64 bit windows mingw compiler. made openssl library of msys , make.
Read more

java - inputmismatch exception -

-
Image
this question has answer here: scanner skipping nextline() after using next(), nextint() or other nextfoo()? 14 answers import java.util.*; public class ngg { static scanner numberentered; static scanner userinput = new scanner(system.in); static int guessednumber; static int randomnumber = (int) (math.random()* 11); static scanner reply; static string answer; public static void main(string[] args) { guesschecker(guessednumber); } public static void guesschecker(int userguess) { while (userguess != randomnumber) { intro(); userguess = intchecker(); if (userguess == randomnumber) { system.out.println("congradulations!"); system.exit(0); } else { system.out.println("that incorrect!");...
Read more

ios - Align baselines with characters in large line heights with Text Kit -

-
Image
when draw attributed string fixed line height text kit, characters aligned bottom of line fragment. while make sense on 1 line characters varying in size, breaks flow of text multiple lines. baselines appear decided largest descender each line. i've found article people behind sketch explaining exact problem in bit more detail , showing solution does, not explaining how achieved this. this want basically: when showing 2 lines large line height, result far ideal: the code i'm using: let smallfont = uifont.systemfont(ofsize: 15) let bigfont = uifont.systemfont(ofsize: 25) let paragraphstyle = nsmutableparagraphstyle() paragraphstyle.minimumlineheight = 22 paragraphstyle.maximumlineheight = 22 var attributes = [ nsfontattributename: smallfont, nsparagraphstyleattributename: paragraphstyle ] let textstorage = nstextstorage() let textcontainer = nstextcontainer(size: cgsize(width: 250, height: 500)) let layoutmanager = nslayoutmanager() textstorage.append(n...
Read more