Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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