sql server - T-SQL: Can a subquery in the SELECT clause implicitly reference a table in the main outer query? -


in t-sql, possible have subquery in select clause implicitly references tables in main, outer query? example:

select name,        case when exists (select o.orderid) 1 else 0 end buyer   customer c   left join order o     on c.custid = o.custid

in other words, can write subqueries without clause?

intellisense seems recognize outer table aliases in subqueries, can't find documentation says acceptable t-sql. can run of own tests, wanted check community. thanks.

yes valid syntax.

a select without from treated though selecting single row table. referencing columns outer query required correlated sub queries , valid there.

the particular query have makes no sense though. evaluate 1 subquery returns single row (with single column containing corresponding o.orderid) going exists check.

probably want check o.orderid null

select name,         case           when o.orderid null 0           else 1         end buyer,    customer c         left join order o                on c.custid = o.custid

where make sense use type of syntax in null safe equality check.

e.g.

select a,         b,         case           when exists (select t.a                        except                        select t.b) 1           else 0         end distinctfrom    t

is equivalent 

select a,         b,         case           when <> b                 or ( null                      , b not null )                 or ( not null                      , b null ) 1           else 0         end distinctfrom    t

Comments

Post a Comment

Popular posts from this blog

rdbms - what exactly the undo information lives in oracle? -

some docs says, there "undo tablespaces" implies should create tablespace undo imformation. some docs says, there "undo segment" implies undo information lives in normal tablespaces , use of it's segment. so, undo information lives , organized? earlier releases of oracle database used rollback segments store undo. oracle9i introduced automatic undo management, simplifies undo space management eliminating complexities associated rollback segment management. oracle recommends (oracle 9i , on words) use undo tablespace (automatic undo management) manage undo rather rollback segments. when creating undo tablespace, these automatically created: n undo segments (based on sessions parameter value) named _syssmun$ owned public (usable ops configuration) not manually manageable
Read more

bash - How do you programmatically add a bats test? -

i'd have bats test created every file in directory, i'm not sure best way done is. approach below creates single test when there many files in directory. #!/usr/bin/env bats in $(ls) @test "testing $i" { pwd } done to extract ls output not batter solution go wrong on filenames spaces. you can below shopt -s nullglob file in /dir/* //test command on $file done shopt -u nullglob you can using find command also find /some/directory -maxdepth 1 -type f -exec cmd option {} \;
Read more

clojure - 'get' replacement that throws exception on not found? -

i'd access values in maps , records, throwing exception when key isn't present. here's i've tried. there better strategy? this doesn't work because throw evaluated every time: (defn get-or-throw-1 [rec key] (get rec key (throw (exception. "no value.")))) maybe there's simple method using macro? well, isn't it; has same problem first definition, if evaluation of throw happens later: (defmacro get-or-throw-2 [rec key] `(get ~rec ~key (throw (exception. "no value.")))) this 1 works letting get return value (in theory) never generated other way: (defn get-or-throw-3 [rec key] (let [not-found-thing :i_would_never_name_some_thing_this_021138465079313 value (get rec key not-found-thing)] (if (= value not-found-thing) (throw (exception. "no value.")) value))) i don't having guess keywords or symbols never occur through other processes. (i use gensym generate special value of...
Read more