sql server - Avoid picking rows from joined table and use it only to get column value -

-

dbo.matchdate(@date) :an inline tvf returns results dbo.patients table .the inline tvf returns these columns : rowid , percentmatch .

i have query far:

insert #temp2 (rownumber,valfromfunc,funcweight,percentage) select rownumber, d.percentmatch, @constval, d.percentmatch * @constval dbo.matchdate(@date) d inner join dbo.patients p  on d.rownumber = p.rowid  inner join dbo.resultsstored rs on rs.rowid = d.rownumber  p.modifiedat > rs.modifiedat

i needed check if modifiedat value of rows returned dbo.matchdate(@date) function dbo.patients table greater modifiedat of rows in resultsstored table rowid's same returned dbo.matchdate(@date). since dbo.matchdate(@date) returns rowid , percentmatch, modifiedat of rows, joined patients table on rowid column. comparison sake, modifiedat resultsstored table, joined further dbo.resultsstored on rowid column only.

problem : results in duplicated rows inserted i.e not getting rows dbo.matchdate(@date) dbo.resultsstored table not @ intended. dbo.resultsstored check modifiedat value same row id's.

how may modify above query avoid duplication of rows in #temp2 because of dbo.resultsstored table ?

i think can use exists check want:

insert #temp2 (rownumber,valfromfunc,funcweight,percentage) select rownumber, d.percentmatch, @constval, d.percentmatch * @constval dbo.matchdate(@date) d exists (    select 1     dbo.patients p     inner join dbo.resultsstored rs on rs.rowid = d.rownumber     (d.rownumber = p.rowid) , (p.modifiedat > rs.modifiedat) )

this insert #temp2 records tvf, provided there at least one record, same rowid, dbo.patients modifiedat greater modifiedat of corresponding record dbo.resultsstored.


Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

Simple Angular 2 project fails 'Unexpected reserved word' -

-
i trying create simple angular2 project based on ng2do project. using quickstart , have following code... <script src="js/quickstart/dist/es6-shim.js"></script> .... import {component, template, bootstrap, foreach} 'js/quickstart/angular2/angular2'; this gives me following error... uncaught syntaxerror: unexpected reserved word what missing? don't know if helps have been having issues day realised because starting code script reference <script src="app.js"> </script> instead of using <script> system.import('app'); </script> hope helps using in tsconfig { "version": "1.5.0-beta", "compileroptions": { "target": "es6", "module": "amd", "declaration": false, "noimplicitany": false, "removecomments": true, "nolib": fal...
Read more

php - Get process resource by PID -

-
Image
i want write web ssh console, , found 2 problems. what want do. first want execute start.php file have following code. $process = proc_open('start', array( 0 => array("pipe", "r"), 1 => array("pipe", "w"), 2 => array("pipe", "a") ), $pipes); second want run command.php file run command on created process in start.php file , results it. $pid = 12345; print_r(process_command('ping google.com', $pid)); i want access process ( cmd ) created in past, send command , result. why problem, if can create new process each command.php execution? because new process new session, if login mysql in past command.php execution, in next executions must login mysql again, because new process not remember logged. example on windows. i create new process ( cmd ) in php, current directory c:\webserv\ . i write cd / command, current directory c:\ . this example, dont want c...
Read more