Hi there,

I'm using Dynamic Forms to capture some data and I'm trying to set up a conditional data capture.  I have the user entering a zip code and if the zip is in my table, then the data gets populated in table_X, but if it's not in the zip table, then it gets populated in table_Y.

I'm no longer getting errors, but the data does not seem to be going into the table.  Any help with this is appreciated.  Here is my current code:

SELECT zip =
    CASE
        WHEN zip = 12345
        THEN
            'INSERT INTO table_X (name,email,zip)
            VALUES ($(name),$(email),$(zipcode))'
        ELSE
            'INSERT INTO table_Y (name,email,zip)
            VALUES ($(name),$(email),$(zipcode))'
    END
FROM zipcodes;

I've had previous attempts at this and the respective errors are below each:

Try #1:
SELECT zip FROM zipcodes
CASE WHEN zip = '12345'
THEN INSERT INTO table_X (name,email,zip)
VALUES ('$(name)','$(email)','$(zipcode)')
ELSE INSERT INTO TESTtable_Y (name,email,zip)
VALUES ('$(name)','$(email)','$(zipcode)')
END ;

Incorrect syntax near the keyword 'CASE'. Incorrect syntax near the keyword 'ELSE'..


Try #2:
SELECT zip = CASE
WHEN zip = 12345
THEN INSERT INTO table_X (name,email,zip)
VALUES ('$(name)','$(email)','$(zipcode)')
ELSE INSERT INTO table_Y (name,email,zip)
VALUES ('$(name)','$(email)','$(zipcode)')
END
FROM zipcodes;

Incorrect syntax near the keyword 'INSERT'. Incorrect syntax near the keyword 'ELSE'. Incorrect syntax near the keyword 'FROM'.


Try #3:
SELECT zip = CASE
WHEN zip = 12345
THEN 'INSERT INTO table_X (name,email,zip)
VALUES ('$(name)','$(email)','$(zipcode)')'
ELSE 'INSERT INTO table_Y (name,email,zip)
VALUES ('$(name)','$(email)','$(zipcode)')'
END
FROM zipcodes;

Incorrect syntax near '$(name)'.

Thanks much!

-Jahn

Hello Jahn,

Please review this link double check on using the CASE statement:


http://www.jackdonnell.com/articles...L_CASE.htm


http://stackoverflow.com/questions/...n-question


http://articles.techrepublic.com.co...78041.html

Please let me know if these resources help.

Thanks,

Ryan

Hi Ryan,

I was just recently (the day before your post) able to find a solution to this problem and it didn't use CASE at all. Go figure. Below is the code that worked for me.

if not exists(SELECT zip FROM zipcodes WHERE zip='$(zipcode)')
begin
INSERT INTO tableY (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)')
end
else
begin
INSERT INTO tableX (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)')
end

Thanks again for your assistance.

-Jahn

Hello Jahn,

I'm glad that you've found a solution to this.

If you ever have any questions please let me know.

Thanks,

Ryan