On 18 Sep 2013 19:23, A Paul wrote:
<<SNIP>> If I have a record as below
ACCT brch TYC yrm flag QUANTITY positive negative
234C 1 25 201312 1 144 144 255
How would I split it into two as below?:
ACCT brch TYC yrm flag QUANTITY positive negative
234C 1 25 201312 1 144 144 0
234C 1 25 201312 2 144 0 255
Basically the flag becomes 2 when there is negative value, and flag
is 1 when there is positive value.
Reminder: This forum, conspicuously, is for RPG concerns. SQL
inquiries not specific to the RPG as the host language, are not
inherently RPG-related; i.e. which host language, or even a need to use
any host language, is inconsequential for many SQL inquiries. Even so,
I am fine with continuing the discussion here.
But also... Please: "SQL question" is horribly vague, and is sadly,
an unremitting subject line; used at least seven times in the past
couple years :-( The actual topic could easily be clarified with
something more specific in the subject-line [as I did in this reply],
and generally also omitting the word "question", as most topics are
inherently such.
Changing the one physical row into two rows can be done generically,
as a data correction, but the given example\scenario is quite specific;
e.g. presumably a row with FLAG=2 could have the same situation? I
hesitate to provide [what I have already coded as] an apparent
resolution, if it is likely to be demonstrated as faulty, due to other
data-issues not yet alluded or explicitly mentioned. I inquire more
about other possible issues with the data, later in my message.
And almost surely, the desired effect is that the QUANTITY should
accordingly, reflect the value assigned to POSITIVE or NEGATIVE; i.e. in
the new row generated with FLAG=2 for the given example, then
QUANTITY=255 would be desirable, and the as-shown QUANTITY=144 would be
*undesirable* for that new row? I expect the intended effect is instead?:
ACCT brch TYC yrm flag QUANTITY positive negative
234C 1 25 201312 1 144 144 0
234C 1 25 201312 2 255 0 255
If the QUANTITY is not set as I have shown just above [contrasted
with what was shown in the quoted message], then the prior
recommendations made in earlier replies to this thread to effect the
correction of the NEGATIVE and POSITIVE values for an existing row, as
set from the value of QUANTITY, would effectively corrupt the data if
ever the procedure were run again [at least without some additional
selection to prevent that]. More explicitly, if such an UPDATE were
issued after the file was modified to effect a new row for a FLAG value
'1' or '2' due to both POSITIVE and NEGATIVE having non-zero values,
then both rows would henceforth have a value of 144 in their respective
POSITIVE and NEGATIVE, thus having /lost/ the [apparently intended]
value of 255. Of course, if the overall intent is, after finalizing
data correction, to effect an ALTER TABLE DROP COLUMN QUANTITY, then any
concern about the [incorrect] value of QUANTITY might be moot... yet an
even temporary data inconsistency is still best avoided in order to
prevent accidental data-loss\data-corruption.
And given there are already such obvious data problems, whereby
somehow the NEGATIVE and POSITIVE were not already separated into two
rows, should one infer there could be other data errors that may need
correction, beyond simply the given example\scenario [and an effective
equivalent, per FLAG=2]? Specifically, is there concern for conditions
whereby FLAG NOT IN ('1','2')? And should every key have a row for both
flag values; e.g. assigning the NULL value for QUANTITY, and zero and
NULL accordingly for the POSITIVE and NEGATIVE for any row that is
generated for the missing FLAG? What about dealing with any rows that
might already suffer with the condition of QUANTITY NOT IN
(POSITIVE,NEGATIVE)? Also, FWiW, the actual DDL for the TABLE versus
just some description of only the data type\length would be more
appropriate, so a reader knows what is the column DEFAULT and its
capabilities for the NULL value.
The initial intent stated in the OP was to re-represent the physical
data. But because the new\added columns POSITIVE and NEGATIVE are
/calculable/ [from other column values within the same row even], they
might best be defined as expressions in the SELECT [and that is likely
best encapsulated in a VIEW]. Again, if the column QUANTITY is to be
dropped, then such a concern about the use of physical data versus
logical representation of the NEGATIVE and POSITIVE columns would be
mollified. And while the additional rows could probably be generated at
run-time, possibly those should be a modification\insert to the physical
data. The whole idea of the flag and the positive\negative is already
suspect, as it seems simply storing a positive or negative value in
column(s) would be complete without the apparent redundancy across rows
and\or columns.
FWiW: Even if there should not always be a row for both FLAG='1' and
FLAG='2', there still should be a CHECK CONSTRAINT to ensure that the
value of the FLAG IN ('1','2') to avoid dealing with invalid values for
FLAG in any [future] data corrections. If any other value(s) were
supported for the FLAG column, then what would that imply about the
values for QUANTITY, POSITIVE, and NEGATIVE? And after any particular
data correction, another CHECK CONSTRAINT should be added to prevent
that same inconsistency from being reintroduced; e.g. something like the
following constraints [but for which likely dealing with the NULL value
would not be required, per having defined the columns as not being null
capable]:
alter table the_file
add constraint chk_flag check(ifnull(flag, '0') in ('1','2'))
add constraint chk_quant check(quantity in (positive, negative))
add constraint chk_quan2 check(
case flag when '1' then positive when '2' then negative else 0 end
= ifnull(quantity, 0) )
add constraint chk_posneg check(
case flag when '2' then positive when '1' then negative end = 0 )
midrange.com
