Results 1 to 12 of 12

Thread: IIF Statement?

  1. #1
    Join Date
    Apr 2009
    Posts
    12

    Question Unanswered: IIF Statement?

    Greetings.

    I have a DB2 connection to a table that 1)has more than 255 fields , an MS access restriction, and 2) a specific value can exist in any of 55 fields.

    I solved # 1 by using a passthrough query

    Is there a way to query all fields for this specific value, while avoiding a very lengthy iif statement, and return that value in an expression column?

    I hope I explained that clearly, I am not a developer nor a SQL expert and can use any advice available.

    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wonder if this construct will work - will have to find some time to test tomorrow...
    Code:
    SELECT CASE 'search value'
             WHEN col1 THEN col1
             WHEN col2 THEN col2
             WHEN col3 THEN col3
             WHEN col4 THEN col4
             ...
    
             WHEN colN THEN colN
           ELSE
             'Not found'
           END
    FROM   da_table
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    mmmmm How About

    DECLARE @i INT
    WHILE @i < 55
    BEGIN
    SET @i=@i+1
    EXEC [dalinkedserver].master.dbo.sp_executesql N'SELECT FROM [da_table] WHERE [da_column]'+ i + '= [da_value]'
    IF @@ROWCOUNT > 0 BREAK
    END
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Apr 2009
    Posts
    12

    Question Responses to the Newbie

    gvee - I tried the case statement for 2 hours last night, only to relaize that CASE is not support in an MS Access query. Any SQL code I try would need to be supported in MS access.

    This looks like the way to go, I just need to consider possible options, as I only have MS access at my disposal. Any thoughts on how to write the case statement outside iof access, what could I use?

    GWilliy - Thank you for the response. Unfortunatley , I have no clue what all that means. Would that be in an MSaccess SQl query, a form, function, etc?

    Thanks for the ideas!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dz1rfj
    gvee - I tried the case statement for 2 hours last night, only to relaize that CASE is not support in an MS Access query.
    Dude - that was a wasted 2 hours. You've posted in the SQL Server forum, so you've been given SQL Server answers.

    I'll move this to Access.
    I recommend you look up the Switch function and translate George's CASE expression to that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2009
    Posts
    12
    pootle flump - Man......thanks for the advice.

    While in MS access, I can switch to SQL view, so thought that meant any SQL code could be used....Thanks for the help!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    IIF isn't valid syntax in DB2.....
    if you tried you'd be using JET queries, and therefore hit the 255 column limit in JET
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL is a language - Structured Query Language, the (pretty close to only) language for interacting with relational database systems. SQL Server is an MS relational database product.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2009
    Posts
    12
    healdem -Thanks for the information. OPerhaps I should post elsewhere, but let me ask you - does one need to be serving a databse to use SQL server, or can it be used as a client to other odbc sources?

    Is there another way / software package that will allow native connectivity to more that 255 fields, AND use the case statement?
    Thanks!!!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - if you are "only" needing to look at 55 columns then you can submit those names into your passthrough query, which then means the 255 limit does not apply.

    Do I need to mention that it sounds like that DB2 table is a s****y design?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can link SQL Server to DB2. You can also use the SQL Server equivalent of passthrough queries (called OPENROWSET) also to connect to DB2.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are connecting to a DB2 dataabse then use pass through queries or ODBC queries to 'talk' to DB2 using a SQL that DB2 understands. that measn use either ANSI SQL or ANSI SQL with DB2/IBM's propriatory addons, not JET or SQL Server of whoever's add ons.

    If you are using DB2 as your back end, and your problem seems to be DB2 SQL then you may well be better off getting this questions moved to the DB2 forum on this site, to get an answer suitable for DB2... is that what you would like to happen?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •