November 16, 2010 at 3:01 pm
I'm using Excel to query a SQL database. I used Microsoft Query in Excel to create the query to retrieve the data from the desired fields in 4 tables.
I need all the records from table imitmidx_sql for item_no (limited by a couple of WHERE filter statements), not limited by the corresponding records in table edablkfl_sql
(refer to my SQL statement below)
As it is now, my resulting output is missing records for the field item_no that appear in imitmidx_sql but don't appear in edablkfl_sql.
How do I do this?
TIA,
Doug
SELECT iminvloc_sql.item_no, imitmidx_sql.item_desc_1, imitmidx_sql.item_desc_2, imitmidx_sql.uom, iminvloc_sql.price, imitmidx_sql.item_weight, imitmidx_sql.item_weight_uom, imitmidx_sql.commodity_cd, edablkfl_sql.package_code, edablkfl_sql.items_pack, edablkfl_sql.pack_weight, imitmidx_sql.comm_cd, imitmidx_sql.user_def_fld_4, imitmidx_sql.upc_cd, edablkfl_sql.filler02, imitmidx_sql.cube_width, imitmidx_sql.cube_length, imitmidx_sql.cube_height, imitmidx_sql.prod_cat, edapkgfl_sql.item_volume_h, edapkgfl_sql.item_volume_l, edapkgfl_sql.item_volume_w
FROM "001".dbo.edablkfl_sql edablkfl_sql,
"001".dbo.edapkgfl_sql edapkgfl_sql,
"001".dbo.iminvloc_sql iminvloc_sql,
"001".dbo.imitmidx_sql imitmidx_sql
WHERE iminvloc_sql.item_no = imitmidx_sql.item_no
AND edapkgfl_sql.code = edablkfl_sql.package_code
AND edablkfl_sql.item_num = imitmidx_sql.item_no
AND ((imitmidx_sql.activity_cd='A')
AND (imitmidx_sql.item_no Not Like '%-FS'
And imitmidx_sql.item_no Not Like '%-GRG'))
November 17, 2010 at 8:50 am
FROM "001".dbo.imitmidx_sql imitmidx_sql
left join "001".dbo.edablkfl_sql edablkfl_sql
on edablkfl_sql.item_num = imitmidx_sql.item_no
left join "001".dbo.edapkgfl_sql edapkgfl_sql
on edapkgfl_sql.code = edablkfl_sql.package_code
left join "001".dbo.iminvloc_sql iminvloc_sql
on iminvloc_sql.item_no = imitmidx_sql.item_no
where imitmidx_sql.activity_cd='A'
AND imitmidx_sql.item_no Not Like '%-FS'
And imitmidx_sql.item_no Not Like '%-GRG'
and
"001" should be [001]
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 4:42 pm
Thanks, "Old Hand". That seems to be working.
I didn't make the change identifying the database to [001] (which I think I've seen as being the right way to do it), because the code I copied and pasted in was directly out of what MS Query created.
I might try it with [001] to see if it works that way, too.
Again, THANKS!
Doug
October 17, 2013 at 4:19 pm
OK, I'm back with another challenge.
I'm incorporating an outer join, but I'm getting an error: Incorrect syntax near ' '. Statement could not be prepared.
I cannot figure out where this issue is:
SELECT
imitmidx_sql.item_no,
imitmidx_sql.item_desc_1,
imitmidx_sql.item_desc_2,
iminvloc_sql.status,
imitmidx_sql.uom,
iminvloc_sql.last_cost,
iminvloc_sql.avg_cost,
iminvloc_sql.std_cost,
iminvloc_sql.price,
imitmidx_sql.item_weight,
imitmidx_sql.item_weight_uom,
iminvloc_sql.loc,
imitmidx_sql.commodity_cd,
imitmidx_sql.comm_cd,
imitmidx_sql.user_def_fld_4,
iminvloc_sql.cube_length,
iminvloc_sql.cube_width,
iminvloc_sql.cube_height,
imitmidx_sql.upc_cd,
imitmidx_sql.prod_cat,
imitmidx_sql.user_def_fld_2,
imitmidx_sql.user_def_fld_1,
iminvloc_sql.picking_seq,
edablkfl_sql.package_code
FROM
"001".dbo.edablkfl_sql edablkfl_sql,
"001".dbo.iminvloc_sql iminvloc_sql,
"001".dbo.imitmidx_sql imitmidx_sql
left join "001".dbo.iminvloc_sql iminvloc_sql
on iminvloc_sql.item_no = imitmidx_sql.item_no
left join "001".dbo.edablkfl_sql edablkfl_sql
on edablkfl_sql.item_num = imitmidx_sql.item_no
WHERE
iminvloc_sql.item_no = imitmidx_sql.item_no AND
edablkfl_sql.item_num = imitmidx_sql.item_no AND
((imitmidx_sql.activity_cd='A') AND (iminvloc_sql.byr_plnr<>40))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply