Help with MySQL Coalesce and Stored Procedures

Question

I'm (attempting) to write a MySQL stored procedure that parses a large text file. Part of what this procedure does is check to see if the entities (in this case, government contractors) named in each record are already contained in the db. (This is a follow up to this question.) This is my first stored procedure and so I'm sure I've wondered off the rails here, and I would appreciated any help.

Here's what I have right now (after declaring the variables):

-- try and fetch first organization (a government agency)
SET agency = COALESCE(SELECT org_agency_o_id FROM orgs_agencies WHERE org_agency_code = maj_agency_cat,SELECT min(org_id) FROM orgs WHERE org_name LIKE CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5)))
-- check to see if that worked  
IF agency = NULL THEN 
    INSERT INTO orgs (org_name,org_name_length,org_type,org_sub_types) VALUES (CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5)),LENGTH(CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5))),'org','Org,GovernmentEntity,Federal,Agency');
    SET agency = LAST_INSERT_ID();
END IF; 
-- try and fetch second organization
SET org = COALESCE(SELECT MIN(org_id) FROM orgs WHERE org_name IN (vendorname, vendoralternatename, vendorlegalorganizationname, vendordoingasbusinessname), SELECT MIN(org_alias_org_id) FROM orgs_aliases WHERE org_alias in (endorname, vendoralternatename, vendorlegalorganizationname, vendordoingasbusinessname)) 
IF org = NULL THEN
    INSERT INTO orgs(org_name,org_name_length,org_type,org_sub_types,org_created) VALUES (vendorname,LENGTH(vendorname),'org','org',DATE());
    SET org = LAST_INSERT_ID();
END IF

Right now MySQL is throwing an error on the line:

SET agency = COALESCE(SELECT org_agency_o_id FROM orgs_agencies WHERE org_agency_code = maj_agency_cat,SELECT min(org_id) FROM orgs WHERE org_name LIKE CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5)))

'maj_agency_cat' is a variable that I declare at the beginning of the procedure and then is assigned dynamically using a cursor that goes through my staging data. The full stored procedure can be viewed here.

I'm sure I'm missing something basic and would appreciate any help.

score 1 · Accepted Answer · answered May 12 '11 at 18:04

1

Try wrapping another () around the inner SELECT statements in your COALESCE arguments. Otherwise, they are not treated as subqueries to be executed first and the value returned, but as query objects passed into COALESCE, which is not a valid argument type for COALESCE:

 SET agency = COALESCE((SELECT ..), (SELECT ..))

answered May 12 '11 at 18:04

mellamokb

53,762
11
101
131

Great thanks! Now I need to figure out what's wrong with my IF statements. – tchaymore May 12 '11 at 19:25
1

@tchaymore: You need to use `IF org IS NULL`. You can't compare to `NULL` directly, it will never work. – mellamokb May 12 '11 at 19:27
That makes sense but still throwing an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF agency IS NULL THEN INSERT INTO orgs (org_name,org_name_length,org_type,' at line 53 – tchaymore May 12 '11 at 19:36
I thought I should post a new question: http://stackoverflow.com/questions/5983600/mysql-if-then-statements-in-stored-procedures. If you have the time, and the answer, please take a look. – tchaymore May 12 '11 at 19:47

Help with MySQL Coalesce and Stored Procedures

1 Answers1