Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have an if condition that contains fields from 2 different tables so I tried to concatenate the tables and when run, I received error message saying the field is not found. This is my current script:
INVENT:
LOAD *, IF(INVENTLOCATION='BTSTORE',REORDERPOINT) AS 'ROP(BT)';
SQL SELECT
.
.
.(OTHER FIELDS)
.
ORDERQTY
INVENTLOCATION
.
.
FROM <FILENAME>;
CONCATENATE(INVENT)
SQL SELECT
.
.
.
REORDERPOINT
.
.
FROM <FILENAME>;
Basically I want to create a column that reads from 2 tables.
Thanks!
Hi, Rachel
This is not the meaning of the concatenate. To reference a field in the load command, it must exist in a precedeing load (in your case, the SQL SELECT) or it must exist in the file you are loading.
In your case, you can make a join of the two source tables, before the load or you load both tables, join them in load/join commands end only then you make the load of the INVENT table referencing the fields you need.
Eduardo
Try something like this:
Tab1:
SQL SELECT
.
.
.(OTHER FIELDS)
.
ORDERQTY
INVENTLOCATION
.
.
FROM <FILENAME>;
concatenate
Load
fieldnames;
SQL SELECT
.
.
.
REORDERPOINT
.
.
FROM <FILENAME>;
noconcatenate
Final:
Load *, IF(INVENTLOCATION='BTSTORE',REORDERPOINT) AS 'ROP(BT)';
resident Tab1;
Hi Rachel,
You can do something like in your script
Table1:
SQL SELECT *
From TB1;
Join
SQL SELECT *
FROM TB2;
New_Table:
Load * , (IF(INVENTLOCATION='BTSTORE',RECORDPOINT ) AS 'POP(BT)'
RESIDENT Table1;
drop table Table1;
Hope this will help you.
-- Rajendra
Hi Nitin,
It seems to me that you'll never have the fields INVENTLOCATION and REORDERPOINT on the same row. If this is true, the field [ROP(BT)] will always have a null content.
For this reason, I proposed a join between the two source tables.
Eduardo
I think this will work, as soon as you have joining fields in bothe tables (TB1 and TB2).
Eduardo
Yes to solve this type problems we must join the table but need to remember join should be used appropriate according to data.