Loading a selection from a database based upon whats already loaded.

[#1]

Hello I have a database I wish to use to create some data associations which are only held within the DB. I am having problems using the 'where exists Clause'  as it tells me that the tables dont exist after the database loads. Here is the code I am using as an example.

Also please see attached.

Customer_Table :
LOAD
     BP
    
FROM

(qvd);

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0; etc etc

Mosiac_raw:
LOAD *,
LEFT([Client Reference],10) AS BP;
SQL SELECT `Client Reference`,
    `pc_mosaic_public_sector_group_alpha`,
    `pc_mosaic_public_sector_group_num`,
    `pc_mosaic_public_sector_type`
FROM `Water_Append_Output`
where exists(BP,LEFT([Client Reference],10))
;

Mosaic:
LOAD BP,
    min(pc_mosaic_public_sector_type) as Type
Resident Mosiac_raw
Group By BP;

inner Join(Mosaic)

LOAD BP,
pc_mosaic_public_sector_type as Type,
    pc_mosaic_public_sector_group_alpha & num(pc_mosaic_public_sector_type,'00') as Code
Resident Mosiac_raw;

drop Table Mosiac_raw;

[jonathandienst]

Hi

Your Where Exists is in the SQL section of the script, which is processed by the Jet driver. This is not a valid SQL command that Jet recognises, hence the failure. Try this instead:

Mosiac_raw:

LOAD *,

LEFT([Client Reference],10) AS BP

where exists(BP,LEFT([Client Reference],10))

;

SQL SELECT `Client Reference`,

    `pc_mosaic_public_sector_group_alpha`,

    `pc_mosaic_public_sector_group_num`,

    `pc_mosaic_public_sector_type`

FROM `Water_Append_Output`

;

Hope that helps

Jonathan

[#3]

Hello,

  can you try to run the script without the creation of the table "Mosaic" and check if the table "Mosaic_raw" is correct?

You could have an error on "Mosaic_raw" that leads your error on "Mosaic"

Regards,

Daniele