1

Topic: To arrange columns

There is here such data set

ACCD ACCC
-------------------- --------------------
40702810700000001202 20202810300000000000
40702810300000000111 20202810300000000000
20202810300000000000 30102810500000000000
20202810300000000000 40702810900000001204
20202810300000000000 20202810700002222222

It is necessary to receive here such result

ACCD ACCC ALL20202 ABC
-------------------- -------------------- -------------------- --------------------
40702810700000001202 20202810300000000000 20202810300000000000 40702810700000001202
40702810300000000111 20202810300000000000 20202810300000000000 40702810300000000111
20202810300000000000 30102810500000000000 20202810300000000000 30102810500000000000
20202810300000000000 40702810900000001204 20202810300000000000 40702810900000001204
20202810300000000000 20202810700002222222 20202810300000000000 20202810700002222222

That is in the field ALL20202 to deduce numbers beginning on 20202, in the field ABC according to other number.
Tried to use a construction

CASE SUBSTRING (p. ACCD, 1, 3)
WHEN ' 202 ' THEN p. ACCC
else p. ACCD
END

Basically works, but does not fulfill for the last line where on either side of 20202, this line simply doubles.

2

Re: To arrange columns

U_rtqwec;

CASE
WHEN p. ACCD LIKE ' 20202 % ' AND p. ACCD LIKE ' 20202 % ' THEN p. ACCD
WHEN p. ACCD LIKE ' 20202 % ' THEN p. ACCC
ELSE p. ACCD
END as ABC

3

Re: To arrange columns

U_rtqwec wrote:

Basically works, but does not fulfill for the last line where on either side of 20202, this line simply doubles.

And what should it turns out from the last line?

4

Re: To arrange columns

U_rtqwec;
I will risk to assume that you use CASE for both fields, checking for one resultant field a condition with ACCD, for another - ACCC. Translate both CASE to one condition...

5

Re: To arrange columns

TaPaK wrote:

U_rtqwec;

CASE
WHEN p. ACCD LIKE ' 20202 % ' AND p. ACCD LIKE ' 20202 % ' THEN p. ACCD
WHEN p. ACCD LIKE ' 20202 % ' THEN p. ACCC
ELSE p. ACCD
END as ABC

The children, all thanks, you very much helped me, made here such request, that seems that is necessary

CASE
WHEN p. ACCD LIKE ' 20202 % ' and p. ACCC LIKE ' 20202 % ' THEN p. ACCD
WHEN p. ACCD LIKE ' 20202 % ' and pACCC NOT LIKE ' 20202 % ' THEN p. ACCD
WHEN p. ACCD NOT LIKE ' 20202 % ' and p. ACCC LIKE ' 20202 % ' THEN p. ACCC
END as ALL20202;
CASE
WHEN p. ACCD LIKE ' 20202 % ' and p. ACCC LIKE ' 20202 % ' THEN p. ACCC
WHEN p. ACCD LIKE ' 20202 % ' and p. ACCC NOT LIKE ' 20202 % ' THEN p. ACCC
WHEN p. ACCD NOT LIKE ' 20202 % ' and p. ACCC LIKE ' 20202 % ' THEN p. ACCD
END as ABC;

6

Re: To arrange columns

U_rtqwec;
Conditions should not be doubled, case quits on the first entrance

7

Re: To arrange columns

TaPaK wrote:

U_rtqwec;
Conditions should not be doubled, case quits on the first entrance

So the first entrance for field ALL20202, and the second a bit changed for ABC

8

Re: To arrange columns

U_rtqwec;
NOT LIKE in CASE it is not necessary for such conditions

9

Re: To arrange columns

TaPaK wrote:

U_rtqwec;
NOT LIKE in CASE it is not necessary for such conditions

Precisely, thanks!

10

Re: To arrange columns

U_rtqwec wrote:

Basically works, but does not fulfill for the last line where on either side of 20202, this line simply doubles.

Collection consider? I advise then received digits to show not only to head , but also the chief accountant because  one person normally writes, and digits received analyzes another - and at them a little different "representations about fine"...

11

Re: To arrange columns

Andy_OLAP wrote:

it is passed...
Collection consider? I advise then received digits to show not only to head , but also the chief accountant because  one person normally writes, and digits received analyzes another - and at them a little different "representations about fine"...

No, it is outswappings between , there all are much more sad.

12

Re: To arrange columns

U_rtqwec wrote:

it is passed...
No, it is outswappings between , there all are much more sad.

And, precisely, at you attempt to come off Diasoft. Anyway on everyone  connect at least  the chief accountant. And differently digits then go under tables and do not discover, whence they began not so to be considered.