SQL Server: Where & Like Statements -

-

i'm rusty sql game. have code (see below) checks on database instance (instance001, instance002, etc), looks when last_read , last_write fields null, along when last reset. nothing on top.

the issue i'm having how execute across multiple instances. you'll see commented out section. if add in appropriate or statement (or inst_name 'instance002'), isn't getting results seeking.

example: instance001 yields 1 records. instance002 yields 60. if use where inst_name 'instance001' or inst_name 'instance002' 210 records. how manipulate sql provide 61? 

declare @timestring nvarchar(50) = convert(varchar(24), getdate(), 120) select db_name, inst_name, min([last_srvr_rst]) min_srvr_rst, last_read, last_write, log_date=@timestring cms_db_last_read_write     -- targeted db data. inst_name                 -- targeted instance(s)     'instance001'     /*     'instance002'     'instance003'       'instance004'     */     , last_read null       -- both read , write must null     , last_write null      -- show no activity.     --and [last_srvr_rst] = min([last_srvr_rst]) group db_name, inst_name, last_srvr_rst, last_read, last_write

and takes precedence on or, need group 2 conditions using parenthesis:

... (inst_name = 'instance001' or inst_name = 'instance002') ,   last_read  null  ,   last_write null group ...

note: changed usage like =, looking exact match, , doesn't make sense use like in situation.

the reason grouping due precedence order mentioned above. order is:

level   operators 1       ~ (bitwise not) 2       * (multiply), / (division), % (modulo) 3       + (positive), - (negative), + (add), (+ concatenate), - (subtract), & (bitwise and), ^ (bitwise exclusive or), | (bitwise or) 4       =, >, <, >=, <=, <>, !=, !>, !< (comparison operators) 5       not 6       , 7       all, any, between, in, like, or, 8       = (assignment)

what means is, of and statements evaluated first. after have been evaluated, or statement evaluated.

this makes query more compiler:

where    (         inst_name = 'instance001' ,     last_read  null ,     last_write null ) or      inst_name = 'instance002'

which why getting 210 results, opposed expected 61, pulling in all records in instance002, disregarding other where filters.

rewriting query grouping 2 conditions mentioned above compiler instead:

where    (         last_read  null ,     last_write null ) ,       (                inst_name = 'instance002' or      inst_name = 'instance001' )

which should logical conditions expecting.

another alternative way can write query without and/or grouping altogether using in clause:

... inst_name in ('instance001', 'instance002') ,   last_read null ,   last_write null group ...

Comments

Post a Comment

Popular posts from this blog

Spring Boot + JPA + Hibernate: Unable to locate persister -

-
i trying work entity inside request method of spring boot application. entity in project , pom.xml has dependencies it. why error message?: org.hibernate.unknownentitytypeexception: unable locate persister: com.ric.bill.model.bs.par package com.ric.web; import java.util.concurrent.atomic.atomiclong; import javax.persistence.entitymanager; import javax.persistence.persistencecontext; import net.sf.ehcache.cachemanager; import net.sf.ehcache.management.cache; import net.sf.ehcache.management.cachestatistics; import org.hibernate.stat.statistics; import org.springframework.beans.factory.annotation.autowired; import org.springframework.boot.autoconfigure.domain.entityscan; import org.springframework.cache.annotation.enablecaching; import org.springframework.transaction.annotation.enabletransactionmanagement; import org.springframework.transaction.annotation.propagation; import org.springframework.transaction.annotation.transactional; import org.springframework.web.bind.annotati
Read more

go - Golang: panic: runtime error: invalid memory address or nil pointer dereference using bufio.Scanner -

-
i implementing go program uses bufio.scanner , bufio.writer have packaged code follows package main import ( "fmt" "player/command" "strings" ) func main() { //enter code here. read input stdin. print output stdout commands.scanner.scan() { //scan new line , send comand variable check command exist or not input := strings.split(strings.trim(commands.scanner.text(), " "), " ") command := input[0] if command == "" { fmt.printf("$ %s:", commands.pwd) continue } if !commands.commands[command] { commands.throwerror("cannot recognize input.") } else { commands.execute(command, input[1:], nil) } fmt.printf("$ %s:", commands.pwd) } } i using init.go file in main package follows package main import ( "flag" "player/sourc
Read more

c - double free or corruption (fasttop) -

-
this code reads null terminated string file (file containing null terminated strings & should provide index wanted string). error: *** error in `./main': double free or corruption (fasttop): 0x090a4a80 *** aborted code: char *tmp_realloc=null; char *sstring=null; int i=1; /*set file pointer point string*/ fseek(fh,index,seek_set); sstring=(char*)malloc(sizeof(char)); if(sstring == null) return null; tmp_realloc=sstring; while( (*(sstring+i-1)=(char)fgetc((file*)fh)) != 0x0 ) { /*reallocate more memory*/ tmp_realloc=(char*)realloc((char*)sstring,++i); /*if not same address copy old new & set old point new*/ if(tmp_realloc != sstring){ strcpy((char*)tmp_realloc,(char*)sstring); free(sstring); sstring=tmp_realloc; } } could tell me pointer causing issue (& reason) ? remove part /*if not same address copy old new & set old point new*/ if(tmp_realloc != sstring){ strc
Read more