Re: can these queries be combined into one?

On 2012-03-25, hamann(dot)w(at)t-online(dot)de <hamann(dot)w(at)t-online(dot)de> wrote:
>
>
> Hi,
>
> I am currently doing something like
>
> select ordercode, descr, codes into temp table x from products where ...
> Here codes is a bit-mapped field
> update x set codes = codes | 512 from othertable t where ordercode = t.ordercode and ....
> select * from x

basically what you do is do a join and put some expressions in the
select list. I used coalesce here as it was a better fit, but case
can also be used...

perhaps this: (needs procucts to have a primary key whih will need to
be substituted in)

select distinct on ( p.PRIMARYY_KEY )
coalesce( t.ordercode|512, p.orderecode ) as ordercode ,
p.descr, p.codes
from products as p
left outer join othertable as t
on p.ordercode = t.ordercode and ....
where ... ;

--
⚂⚃ 100% natural