Scanning a data table

[#1]

Hi Guys,

Is it possible to scan and replace values in a table?

Something like this...

load * from table

for each record

replace record.field with 123

end for

Thanks

DerekJ

[#2]

Not quite sure what you mean - are you just trying to set a specific value to a field regardless of its actual value? if so this will do it

Load fielda,

fieldb,

123 as fieldc

from...

If you want to replace various values with others, take a look at 'map'

Regards,

Gordon

[Oleg_Troyansky]

If you simply reload the table using resident load, and use any expression you want for the target field. If the table existed before, you'll need to reload it into a different name, then drop the "old" table, then rename from new to old, to preserve the original name. Something like this:

NewTable:

load A, B. '123' as C resident OldTable;

drop table OldTable;

rename table NewTable to OldTable;

[#4]

Hi Guys,

Thanks for the quick replies. I'd like to do something like this......

If I have a simple table like this

MyTable:-

Field1

1

2

3

I can :-

SQL

FROM MyTable;

This gives

SELECT field1, 0.0 as field2

Field1 Field2

1 0.0

2 0.0

3 0.0

Then, in memory, I want to change the table to look like this

Field1 Field2

1 2

2 4

3 6

where the value of Field2 comes from some calculation that can't be done in the SQL Select.

Thanks,

DerekJ

[#5]

Hi Darek,

I think I have a solution for your post. here you should use Precedent Load. to get more info on it go to reference manual.

a:

Load Field1,

sum(Field2) as Field 2; //some calculation you want to make here

SQL Select Field1,

0.0 as Field2

from xyz;

The first load without any from statement is the Precedent load. Hope this helps you....

Thanks Joseph......

[markmccoid]

Hi Oleg,

When I try the above both of my tables disappear?!

Here is the code I'm using:

NewProductTable:
Load
ADPRODUCT_ID,
PlacementName,
SuperProduct_ID,
AdSubType,
AdTypeCombo,
Edition,
If(IsNull(BreakoutProduct), ProductName,BreakoutProduct) as ProductName,
RESIDENT AdProduct;

drop table AdProduct;

Rename table NewProductTable to AdProduct;

Is there anything that I could be doing wrong that would cause this?

Thanks

[markmccoid]

I think I've found the answer. All the fields in the new table are the same name. If I add a new dummy field it works.

Thanks,