sql - Using case and like statment without duplicate results -


i trying use case , statement. when run below query duplicating results on rows y on particular columns. using sql server 2000.

select distinct [rjvn_pound_reference]     ,t_reference     ,t_street_name     ,t_zone_name     ,(         case              when rjvn_note '%correspondence%'                 'y'             else 'n'             end         ) correspondencereceived     ,(         case              when rjvn_note '%review form complete%'                 'y'             else 'n'             end         ) reviewformcomplete     ,(         case              when rjvn_note '%manually issued nto - drive off%'                 'y'             else 'n'             end         ) manuallyissuednto     ,(         case              when rjvn_note '%manually issued ntk - drive off%'                 'y'             else 'n'             end         ) manuallyissuedntk     ,(         case              when rjvn_note '%drive off- final reminder sent%'                 'y'             else 'n'             end         ) finalremindersent [icps].[dbo].[removal_job_vehicle_notes] inner join tickets t on (a.rjvn_pound_reference = t.t_number) inner join dbo.ticket_hold_record thr on (t.t_number = thr.thr_system_ref) thr_hold_type = '2207555'     , t.t_contract in ('18','1')

results:

805464  xl05512211  selby (60-100)  abbey walk  n   n   y   n   n 805464  xl05512211  selby (60-100)  abbey walk  n   y   n   n   n 805464  xl05512211  selby (60-100)  abbey walk  y   n   n   n   n

but want displayed on single row instead like:

805464  xl05512211  selby (60-100)  abbey walk  y   y   y   n   n.  updated : 944786  xl07075730  selby (60-100)  abbey walk  n   n   n   n   y    should 944786  xl07075730  selby (60-100)  abbey walk  n   n   y   n   y

my actual table data record :

rjvn_pound_reference    rjvn_time                 rjvn_userid      rjvn_note 944786                  2014-01-08 10:38:17.000   jamied           vehicle drove away during pcn issue 944786                  2014-01-08 14:22:21.000   adrianj          correspondent scanned 944786                  2014-02-05 15:44:53.000   ellied           manually issued nto drive off 944786                  2015-03-12 10:16:53.000   katieb           drive off- final reminder sent  --manually issued nto drive off displayed 'n' instead of 'y'

because column references in query not qualified (to indicate which table returned from), , because don't have schema definition go through sort out... can't tell table rjvn_note column comes from.

as far getting "duplicate" rows, explanation join, there's one-to-many association, , multiple rows being returned. expected behavior.

to "collapse" rows, can add group by end of query. can use aggregate functions (like min() or max()) "pick out" values returned expressions collapsed rows.

the distinct keyword (as in query) operates on entire set of expressions in select list. eliminate rows exact duplicates.

there's couple of other alternatives useful in situations. example, using exists predicate (it requirement test existence of row in related table, without producing "duplicates" join operation would. or, using subquery in select list.

because column references not qualified, , because don't have schema definition, we're guessing. of "maybe try this" guesses might turn out right, guesses.

here's "guess" @ changes need make query specified resultset:

  • ditch distinct keyword
  • add max() aggregate around case expressions

  • add group by clause listing non-aggregate expressions select list

  • (optional) qualify column references table alias (several reasons this: aid future readers, , insulate statement future failure

select a.rjvn_pound_reference      , t.t_reference      , t.t_street_name      , t.t_zone_name      , max(case when a.rjvn_note   '%correspondence%'                 'y' else 'n' end        ) correspondencereceived      , max(case when a.rjvn_note   '%review form complete%'                 'y' else 'n' end        ) reviewformcomplete      , max(case when a.rjvn_note   '%manually issued nto - drive off%'                 'y' else 'n' end        ) manuallyissuednto      , max(case when a.rjvn_note   '%manually issued ntk - drive off%'                 'y' else 'n'end        ) manuallyissuedntk      , max(case when a.rjvn_note   '%drive off- final reminder sent%'                 'y' else 'n' end        ) finalremindersent   [icps].[dbo].[removal_job_vehicle_notes]   join [tickets] t     on t.t_number = a.rjvn_pound_reference   join [dbo].[ticket_hold_record] thr      on thr_system_ref = t.t_number  thr.thr_hold_type = '2207555'    , t.t_contract in ('18','1')  group     a.rjvn_pound_reference      , t.t_reference      , t.t_street_name      , t.t_zone_name

Comments

Post a Comment

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

i have following sample code used pre-decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(--c); cout << c << endl; return; } } this function give output input 4 : dp3 dp2 dp1 dp0 0 1 2 3 but when use post decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(c--); cout << c << endl; return; } } output goes in infinite loop dp4 can explain me in detail why happens ? this happens because function(c--) called same value of c , when finishes c decremented. recursively called, hence called same value no chance of return , hit stack overflow error. assume this, int x = 1, y = 0; y = x++ result y == 1 , x == 2. if y = ++x , bot x , y 2.
Read more

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

i added new project. made mistake in projectname.conf file in sites-available apache2, led me reinstalling apache2. after reinstalling , re-enabling projects on every page of every project .. <body> run(); </body> .. i've run composer update, restarted service, restarted pc, checked config files, tried disabling new project. there nothing in error log, , access.log: 127.0.0.1 - - [15/may/2015:01:40:47 +0200] "get / http/1.1" 304 181 "-" "mozilla/5.0 (x11; ubuntu; linux x86_64; rv:38.0) gecko/20100101 firefox/38.0" maybe i'm forgetting apache2 reinstall? don't think did different last time installed it. looks haven't setup php correctly. take @ this great guide , maybe you're forgetting link php config apache2.
Read more

php - How can I echo out this array? -

i don't know how go grabbing data in variable , put foreach loop. class tree{ private $info = array( array('name'=>'jim','state'=>'nu','company'=>'nu','phone'=>array('cell'=>'5615111111','office'=>'5611111111'),'email'=>array('primary'=>'exs@example.com','ex@there.com')), array('name'=>'joe smith','city'=>'phoenix','phone'=>'4805551111','email'=>'jsmith@some_email.com'), array('name'=>'john doe','city'=>'chandler','company'=>'doe co.','email'=>array('jdoe@gmail.com','personal'=>'email@email.com'),'phone'=>'6025550002') ); } if you're inside of class, can access private variable using $this->variablename . example, i...
Read more