1

Topic: Request about check of lines

Greetings. As the example, is 2 lines. In them the identical goods from 2 warehouses are described. How to make if in one warehouse value 0 and in the second line with other title of a warehouse value 0 would be added.
On an example if in a warehouse ' Pakavimo t ' Poreikis = ' 0 ' that in a warehouse ' Pakavimo ' the result too Poreikis = ' 0 ' is necessary or it is possible to write down these values in other column.
The example is attached

2

Re: Request about check of lines

UPDATE the table
SET an amount = 0
WHERE goods IN (SELECT DISTINCT the goods
FROM the table
WHERE an amount = 0)

3

Re: Request about check of lines

For example

declare @t table (obj_kod nvarchar (50))
insert @t
(obj_kod)
select
obj_kod
from
dbo. View_SPP_poreikis_15
where
Poreikis = 0
update t
set
Poreikis = 0
from
dbo. View_SPP_poreikis_15 t
inner join @t t2 on t2.obj_kod = t.obj_kod

4

Re: Request about check of lines

And without Update it is possible? It already Views. In it that can is possible that to make? Washing the logician works so, but I do not know as it to write correctly SQL to the server.
Case when data = data AND pre_kod = pre_kod AND expr1 = expr1 in a warehouse san_kod ='PAKAVIMO T ' Poreikis = ' 0 ' THEN in a warehouse san_kod ='PAKAVIMO ' Poreikis = ' 0'

5

Re: Request about check of lines

Vadim Z wrote:

It already Views. In it that can is possible that to make?

as an example

select
obj_kod;
case when t2.flag = 1 then 0 else t. Poreikis end as Poreikis
from
dbo. View_SPP_poreikis_15 t
inner join
(
select
obj_kod, 1 as flag
from
dbo. View_SPP_poreikis_15
where
Poreikis = 0
) t2 on t2.obj_kod = t.obj_kod

6

Re: Request about check of lines

You should select simply, instead of to update? Then, can, earns such here:

SELECT..., CASE WHEN 0 = MIN (amount) OVER (PARTITION BY the goods) THEN 0 ELSE amount END...

7

Re: Request about check of lines

Anyway sqlfiddle says that so it is possible.

8

Re: Request about check of lines

Akina wrote:

Anyway sqlfiddle says that so it is possible.

And the screenshot from the HARDWARE says that at it the SQL Server 2000, and to it so is impossible.

9

Re: Request about check of lines

Minamoto wrote:

the screenshot from the HARDWARE says that at it the SQL Server 2000

Well is rather weak I in screenshots...

10

Re: Request about check of lines

MS Server 2014

11

Re: Request about check of lines

Akina wrote:

you should select simply, instead of to update? Then, can, earns such here:

SELECT..., CASE WHEN 0 = MIN (amount) OVER (PARTITION BY the goods) THEN 0 ELSE amount END...

It is possible here more in detail, under my variant to write?
WHEN 0 = MIN (amount) that here means for 0?

12

Re: Request about check of lines

It can is possible them as that to unite, leave only that, where Poreikis=0???

13

Re: Request about check of lines

Vadim Z;
So full outer join + coalesce (sklad1, sklad2, 0).

14

Re: Request about check of lines

Or I did not understand the task and in result it is required to add the lines which are not existing on one of warehouses?

15

Re: Request about check of lines

Vadim Z;
Main point... In this yours Poreikis there can be values of less zero?

16

Re: Request about check of lines

The grandfather;
Poreikis there can not be less zero.
Poreikis=0 When in warehouse PAKAVIMO T residual is more than Expr1. If it is fulfilled, residual in warehouse PAKAVIMO does not interest and it is a line can not be shown or appropriate to it too value 0. And then to do sampling all but 0

17

Re: Request about check of lines

Vadim Z wrote:

it is possible here more in detail, under my variant to write?

That - is more detailed? The big problem to replace names of fields with the?

Vadim Z wrote:

WHEN 0 = MIN (amount) that here means for 0?

the Zero is a number such... The constant... A type constant. Probably WHEN x=0 does not surprise? So why WHEN 0=x surprises?

18

Re: Request about check of lines

Akina wrote:

it is passed...
What - is more detailed? The big problem to replace names of fields with the?
it is passed...
The zero is a number such... The constant... A type constant. Probably WHEN x=0 does not surprise? So why WHEN 0=x surprises?

data - date
pre_kod - The goods code
expr1 - The necessary amount
likutis - residual in a warehouse
san_kod - A warehouse
Poreikis - if in warehouse PAKAVIMO T the amount is more or to exactly necessary amount that 0, differently the necessary amount.
Pppp-if in in warehouse PAKAVIMO T POREIKIS=0 that pppp=0 and in warehouse PAKAVIMO too has to appear pppp=0
Expression will look so?:
SELECT data, pre_kod, expr1, likutis, san_kod, poreikis, pppp, CASE WHEN 0 = MIN (expr1) OVER (PARTITION BY pre_kod) THEN 0 ELSE expr1 END as proba
So?

19

Re: Request about check of lines

Far off tried, like result necessary.
Thanks. Whether tomorrow I will unsubscribe all it turned out. Here the code which transited:

SELECT data, pre_kod, expr1, likutis, obj_kod, san_kod, poreikis, CASE WHEN 0 = MIN (poreikis) OVER (PARTITION BY pre_kod) THEN 0 ELSE poreikis END as proba
FROM dbo. View_SPP_poreikis_15