sql - Display all rows of data from both tables even though conditions exist -

-

i have sql query 2 tables need in expanding results.

the query running is:

select      b.obj_full, b.subj_full, b.act_ytd_ty,      sum(a.jnl_value) jnl_total      db2adm2.jnlfile  inner join      db2adm2.tfincatp b on b.obj_full = a.jnl_obj                        , b.subj_full = a.jnl_subj      a.jnl_year ='15'     , a.jnl_processed ='n'     , obj_full = 'tbbbb' group      b.obj_full, b.subj_full, b.act_ytd_ty

the data being returned is:

obj_full subj_full  act_ytd_ty  jnl_total ----------------------------------------- tbbbb    9404       -9666.73    -547.78 tbbbb    9405       -13098.05   -24.39

i have split query above 2 separate queries show data being returned each one.

query 1 - journal values not processed week on code tbbbb

select      jnl_obj, jnl_subj, sum(jnl_value) jnl_value      db2adm2.jnlfile      jnl_year ='15'     , jnl_processed ='n'     , jnl_obj = 'tbbbb' group      jnl_obj, jnl_subj

results on query 1

jnl_obj jnl_subj  jnl_value --------------------------- tbbbb   9404      -547.78 tbbbb   9405      -24.39

query 2 - year date values on code tbbbb

select      obj_full, subj_full, act_ytd_ty      db2adm2.tfincatp        obj_full = 'tbbbb'

results on query 2

obj_full  subj_full  act_ytd_ty ------------------------------- tbbbb     3420     12000 tbbbb     3473     18453.02 tbbbb     3524     2480.4 tbbbb     3704     585 tbbbb     6812     0  tbbbb     6910     0 tbbbb     7704     -4142.71 tbbbb     8199     -25 tbbbb     9400     -168363.1 tbbbb     9403     -457.56 tbbbb     9404     -9666.73 tbbbb     9405     -13098.05 tbbbb     9812     0

the result looking query 2 additional column query 1 being jnl_value, every tbbbb code not have journal each week, rows journal being returned, column must insert 0 if present value not there.

any ideas how please?

if want 0 when there no record matching b one:

select b.obj_full, b.subj_full, b.act_ytd_ty, isnull(a.jnl_value, 0) jnl_value db2adm2.jnlfile  right outer join db2adm2.tfincatp b on b.obj_full = a.jnl_obj , b.subj_full = a.jnl_subj a.jnl_year ='15' , a.jnl_processed ='n' , obj_full = 'tbbbb'

(do search on outer joins: there left, right, , full outer joins.) since want every b row, i've removed group by. , put in right outer join b's when there no matching a.


Comments

Post a Comment

Popular posts from this blog

How to show in django cms breadcrumbs full path? -

-
need advice - how show in breadcrumbs whole path article or page? i've got code: {% ance in ancestors %} <li> {% if not forloop.last %} <a href="{{ ance.get_absolute_url }}">{{ ance.get_menu_title }}</a> {% else %} <span class="active">{{ ance.get_menu_title }}</span> {% endif %} </li> {% endfor %} but shows "home" , current page. how modify show whole path in breadcrumbs? i've found in file menu_tags.py code calls ancestors. @ least there class showbreadcrumbs: class showbreadcrumb(inclusiontag): """ shows breadcrumb node has same url current request - start level: after level should breadcrumb start? 0=home - template: template used render breadcrumb """ name = 'show_breadcrumb' template = 'menu/dummy.html' options = options( argument('start_level', default=0, require...
Read more

php - Invalid Cofiguration - yii\base\InvalidConfigException - Yii2 -

-
Image
i've installed yii2-user module/dektrium yii2-app-basic application command composer require "dektrium/yii2-user:0.9.*@dev" config/console.php return [ . . 'modules' => [ 'gii' => 'yii\gii\module', 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] config/web.php 'components' => [ . . /* 'user' => [ 'identityclass' => 'app\models\user', 'enableautologin' => true, ], */ 'modules' => [ 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] after that, run command $ php yii migrate/up --migrationpath=@vendor/dektrium/yii2-user/migrations updating database schema . but, when run http://localhost/mylawsuit/yii/web/index.php?r=u...
Read more

ruby on rails - npm error: tunneling socket could not be established, cause=connect ETIMEDOUT -

-
i have app uses rails framework , implements angularjs part of front end. i have pushed heroku , have heroku toolbelt installed, when try migrate db using "heroku run rake db:migrate" receive following error(s): installing core plugins heroku-cli-addons, heroku-apps, heroku-fork, heroku-git, heroku-local, heroku-run, heroku-status... error installing package. try running again gode_debug=info see more output. ! `run` not heroku command. ! perhaps meant `-h`, `2fa`, `auth`, `join`, `open`, `orgs`, `pg`, `ps` or `rake`. ! see `heroku help` list of available commands. i run command "gode_debug=info heroku run rake db:migrate" , receive error: npm err! darwin 14.5.0 npm err! argv "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/bin/node" "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/lib/node_modules/npm/cli.js" "install" "heroku-cli-addons" "heroku-apps" "heroku-fork...
Read more