ADODB CASE WHEN problem

    forum.ozgrid.com/index.php?attachment/67720/


    Hi folks!



    When connecting ThisWorkbook using ADODB I get following error:


    [ATTACH=CONFIG]67719[/ATTACH]


    The Source string for recordset printed in immediate window is as follows:


    SQL
    SELECT 
CASE WHEN [ITEM1] = "CD36917" AND [SUBGROUP] Like "0999%" AND  [LINE CODE] = "XY00000ZS2N8WV1" AND [GROUP1] Like "%555%" THEN "ABC"  WHEN [ITEM1] = "KM39352" AND [LINE CODE] = "XY00000ZS36W4" AND [GROUP1]  Like "%555%" THEN "DEF" WHEN [ITEM1] = "VW40341" AND [SUBGROUP] Like  "0999%" AND [GROUP1] Like "%555%" THEN "GHI" WHEN [LINE CODE] =  "XY00000ZS2N8WV1" AND [GROUP1] Like "%555%" THEN "JKL" WHEN [ITEM1] =  "LB789516" THEN "MNO" WHEN [LINE CODE] = "ZZ00000TV37Z6" AND [GROUP1]  Like "%555%" THEN "PQR" WHEN [CITY] = "PARIS" THEN "STU" 
END 
FROM  [data$]



    What macro shall do:
    1. Go through every row on data sheet.
    2. For that row, run a loop of every category combination on lookup_ref sheet
    3. Take the category which meets most of the criteria on ref sheet and paste that from Ref onto data sheet, col H.
    4. If more category rows meet the logic per particular data row (like row 4 = either MNO or STU) then take the last one (STU)
    > unfortunately, data cannot be organized different way.

    Re: ADODB CASE WHEN problem


    An error like 'Unrecognized Keyword' is usally an indication that you've typed something wrong, as in mis-spelt. As that's not the case here, it's a dead giveaway you're trying to use something that does not exist in the language or simply forgot to delimit literal strings.


    The CASE WHEN statement exists in MS SQL Server (the product itself, and maybe a couple of others like Oracle & Ingres), not in the version of SQL used by the Access Database engine which is what is being used here.



    A 'standard' SQL statement:

    SQL
    SELECT [Field1], [Field2], [Field3] FROM [TableName] WHERE([Field1='x')


    An example using the Access DB engine with its closest equivalent to CASE WHEN:


    SQL
    SELECT SWITCH([Field1]>Value1, ReturnValue1, [Field1]=Value2, ReturnValue2, [Field1]<=Value3, ReturnValue3, True, DefaultReturnValue), SWITCH([Field2....) FROM [TableName] WHERE ...


    The Final TRUE in the SWITCH gives a default return if nothing else matches and is needed to prevent a NULL value being returned.


    I'm not sure if you can refer to different fields in a SWITCH statement... SELECT SWITCH([FieldABC]>Value1, ReturnValue1, [FieldDEF]=Value2, ReturnValue2


    Typed freehand on a tablet, so example only to illustrate concept.


    I still have no idea what you're trying to do. Sorry. It started relatively simply, but you've lost me here.

    Re: ADODB CASE WHEN problem


    Thanks again cytop. A user starts manually filling Category names in Col B, lookup sheet and defining the way, how category name value shall be assigned to each data record, into col H. So that 1st row on data sheet if it has ITEM = "CD36917" AND SUBGROUP like "0999*" AND .. (every populated cell on lookup grid), it gets "ABC" in col H. If that data record has different values than ABC category dictates, then macro shall go through rest of the combinations (lookup sh) until it finds that which meets the most of values the data record has.
    So I though of using CASE WHEN statement.


    It SWITCH is the alternative here, I'd need to make it something like:


    SQL
    SELECT SWITCH ([ITEM1]='CD36917' AND [SUBGROUP] LIKE '0999%', 'ABC', TRUE, 'XXX') FROM [data$]


    I just tried to connect to recordset and it has not failed yet. I'll try the next stuff later. ..

    Re: ADODB CASE WHEN problem


    Can I use Sheet.Querytables().CommandText after connecting to ThisWorkbook from within? If yes, how the connection string is built? I tried same connection string as at ADODB, but it fails.

    Re: ADODB CASE WHEN problem


    There's no guarantee only 1 record will be returned, that's why I put an asterisk in Col A in my sample. Any row marked with the asterisk returned multiple records.


    If you're happy just selecting the first, you can use the TOP predicate
    Select Top 1 Switch... our you can use the Maxrows parameter of the Copyfromrecordset method.


    Otherwise you're going to have to provide some way for the user to select which item, or Cancel the update, or whatever you're doing and tell the user to input more information so that only 1 record is returned.


    Can't wait to get off the phone and have a look at what you've done...

    Re: ADODB CASE WHEN problem


    Finally I achieved what I desired.


    forum.ozgrid.com/index.php?attachment/67741/


    I left ADO recordset and went with QueryTable property. I believe it works when conditions are sorted from most to least (ABC = 4 to JKM = 1) .
    Only small downsize is that I seem not to get rid of column reader that comes with retrieved categories, so I query results on sheet3 and link just values without header.


    Can this be somehow overcome?
    Also in SQL statement, is there a way not to refer to data$ sheet but rather a listobject name?


    Thank you

    Re: ADODB CASE WHEN problem


    Hi!


    everything has been working fine so far until I encountered following error.


    [ATTACH=CONFIG]67765[/ATTACH]


    Does SWITCH statement have some sort of technical limit?
    It fired when I had around 1000 conditions that stepped into the string.


    Is there another way around ?

    Re: ADODB CASE WHEN problem


    An 'Expression Too complx' error is a dead giveaway you've found a limit of some sort.


    No suggestions other than that 'around 1000 conditions' comment suggests you should approach this in a different way, but I've no idea what the alternative is.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login