4 Replies Latest reply: Jun 13, 2012 1:12 PM by Kevin Bertsch RSS

Where/how to put LOAD statements for fields from different tables?

Kevin Bertsch
I extract data from a large database using SQL. I want to do some manipulation in the script, but my problem is the two fields are in different tables. We have an "Opportunity Start Date" in one table, OM, and an "Action Date" in another table, CA. I have done joins in SQL on the appropriate fields to link the tables, but I have the following problem. This script works fine:
LOAD       IF(OppStartDate>='1/1/2012',OppStartDate) As OSD,
      *
OMTable:
Select
...
OM.OppStartDate
FROM
....
LOAD
IF(ActionDate>='1/1/2012',ActionDate As AD,
*
CATable: Select
..
CA.ActionDate
From
..
Now, I want to create a new field "Delay" equal to "ActionDate - OppStartDate", but no matter where I put this, I generate an error:
NetWorkDays(OppStartDate,ActionDate) As Delay
If I put this statement in the LOAD before the OMTable Select, I get the error "Field 'Action Date' not found", but if I put that statement before the LOAD for CATable, I get "Field 'OppStartDate' not found". (And I tried various combinations of my new names, eg NetWorkDays(OppStartDate,AD), etc. with no better luck.) I had the company SQL expert look at it, and he advised me to join the tables CA and OM, which I did, but I still get this error. I don't think I can use the MAP function because OSD doesn't exist in CATable, and AD doesn't exist in OMTable. Any thoughts?