firebird - How to combine sql queries where one query uses the MIN function -


i have read-only firebird database want extract data. in ms-access it's easy data want using 3 simple queries in output of each query input next one. want program in php , keep on 1 line want combine these 3 queries in 1 can't figure out.

the first query gives me dates starting today in company planning (max 1000 lines). jobs planned on more day's want first date today. using min function in query 2. in last query use left join combine pending jobs generated first dates.

how can in 1 query? have reading rights can't work temporary tables.

can give me directions please?

**first step** select     salesorderplan.plandate,         salesorderplan.salesorder     salesorderplan     salesorderplan.plandate >= date();  **second step** select      min([1estap].plandate) firstplandate, [1estap].salesorder      [1estap] group      [1estap].salesorder;  **third step** select     salesorder.orderno,     [2estap].firstplandate,     salesorder.description,     salesorder.deliverylocname,     salesorder.deliveryaddress,     salesorder.deliverycity     ((salesorder)     left join 2estap on (salesorder.objectid = [2estap].salesorder))     salesorder.salesstatus=1 order     salesorder.orderno;

i have 2 tables, first called salesorder contains jobs. second called salesorderplan contains dates in planning jobnumbers.

salesorder objectid description etc 342567   blah blah  356782   jwz 384512   not in planning yet  salesorderplan objectid salesorder plandate  23451    342567     12-03-2017  23489    342567     14-04-2017  23490    356782     13-03-2017  23496    356782     18-06-2017  23499    342567     21-08-2017 23499    342567     28-08-2017  23512    356782     30-08-2017  23524    356782      2-09-2017

i want list of orders. if there date in planning want first date after today (that used min fuct for) outcome should be

result objectid description             plandate 342567   blah blah               21-08-2017 356782   jwz                     30-08-2017 384512   not in planning yet     no date

i should preface saying don't have experience firebird, have experience sql. might work you:

-- **third step** select     salesorder.orderno,     [2estap].firstplandate,     salesorder.description,     salesorder.deliverylocname,     salesorder.deliveryaddress,     salesorder.deliverycity, salesorder left join (     -- **second step**     select          min([1estap].plandate) firstplandate, [1estap].salesorder     (         -- **first step**         select             salesorderplan.plandate,                 salesorderplan.salesorder                     salesorderplan                     salesorderplan.plandate >= date()     ) [1estap]     group          [1estap].salesorder ) [2estap] on (salesorder.objectid = [2estap].salesorder))     salesorder.salesstatus=1 order     salesorder.orderno;

edit

as pointed out @markrotteveel, first 2 queries can collapsed one. here's query performed:

-- **third step** select     salesorder.orderno,     [2estap].firstplandate,     salesorder.description,     salesorder.deliverylocname,     salesorder.deliveryaddress,     salesorder.deliverycity, salesorder left join (     -- **first & second steps**     select          min(plandate) firstplandate, salesorder     salesorderplan             plandate >= date()     group          salesorder ) [2estap] on (salesorder.objectid = [2estap].salesorder))     salesorder.salesstatus=1 order     salesorder.orderno;

Comments

Post a Comment

Popular posts from this blog

What is happening when Matlab is starting a "parallel pool"? -

running parallel cpu processes in matlab starts command parpool() according documentation , function: [creates] special job on pool of workers, , [connects] matlab client parallel pool. this function takes bit of time execute, on order of 30 seconds. in other multi-cpu paradigms openmp, parallel execution seems totally transparent -- i've never noticed behavior analogous matlab (granted i'm not experienced parallel programming). so, happening between time parpool() called , when finishes executing? takes long? parallel computing toolbox enables run matlab code in parallel using several different paradigms (e.g. jobs , tasks, parfor , spmd , parfeval , batch processing), , run either locally (parallelised across cores in local machine) or remotely (parallelised across machines in cluster - either 1 own, or 1 in cloud). in of these cases, code run on matlab workers , copies of matlab without interactive desktop. if you're intending run on remo...
Read more

angular - DownloadURL return null in below code -

when change file updated in firebase storage when try downloadurl show null. filechange(event: any) { const imagefolder: string = this.employee.id; const filelist: filelist = event.target.files; const file: file = filelist[0]; const storageref = firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file); console.log(storageref.snapshot.downloadurl); } the put() asynchronous. if want url after file uploaded have this: firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file).then((snapshot) => { storageref = snapshot.downloadurl: console.log(snapshot.downloadurl); });
Read more

php - Cannot override Laravel Spark authentication with own implementation -

during login process of laravel spark need request token of remote api. api stores exact same users , passwords laravel spark application. therefore need username , non hashed password of user during authentication process. i thought overriding authenticated method solution problem. in routes/web.php override post /login endpoint , pointing endpoint own logincontroller: <?php namespace app\http\controllers\auth; use illuminate\support\facades\log; use illuminate\http\request; use laravel\spark\http\controllers\auth\logincontroller sparklogincontroller; class logincontroller extends sparklogincontroller { /** * create new login controller instance. * * @return void */ public function __construct() { parent::__construct(); } /** * handle successful authentication attempt. * * @param request $request * @param \illuminate\contracts\auth\authenticatable $user * @return response */ public fu...
Read more