convert IIF statements

hi, 

I am trying to convert IiF statement to query statement.  Probably the CASE 
statement...

here is what i have...

if [TP] = '2' then 'FH' elseif [TP] = '3' then 'FH' elseif [INV]=55555 then 
'FH' else 'NHF' end
 
GS =
if [grp] = 'FN' then 'FN' elseif [grp]='FNREM' then 'FN' elseif 
[grp]='FNREMSL' then 'FN' elseif [grp]='LLL' then 'LLL' else 'Non GS' end
 
Final FH (FH or NHF)
iif([FH]='FH',iif([INV]<>55555,iif([HS]='SSS','FH','NHF'),'FH'),'NHF')
 
Inv Seg 1
iif([Final FH]<>'FH',iif([GS]='FN','FN',iif([GS]='LLL','LLL','Non GS')),FH)
 
Inv seg 2
iif([InvSeg1]='Non GS',iif([HS]='SSS','Other','Non SSS'),[InvSeg1])
 
Inv Seg Final
iif([InvSeg2]='Other',iif([TP]='6','SP','Other'),[InvSeg2])

can someone help me convert this to CASE statements?

Thanks in advance,
geebee

0
Utf
9/10/2010 1:10:03 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
1170 Views

Similar Articles

[PageSpeed] 35

It's hard to know exactly what you need since you didn't include any
table names or variable names, but here's a start that should give you
the basic idea.  I didn't do all of them, since I'm not sure if this
is exactly what you need...

SELECT
    CASE
        WHEN [TP] = '2' OR [TP] = '3' OR [INV] = '55555' THEN 'FH'
        ELSE 'NHF'
    END AS Field1,

    CASE
        WHEN [grp] = 'FN' OR [grp]='FNREM' OR [grp]='FNREMSL' THEN
'FN'
        WHEN [grp]='LLL' THEN 'LLL'
        ELSE 'Non GS'
    END AS GS,

    CASE
        WHEN [FH] = 'FH' THEN
            CASE
                WHEN [INV] <> '55555' THEN
                    CASE
                        WHEN [HS] = 'SSS' THEN 'FH'
                        ELSE 'NHF'
                    END
                ELSE 'FH'
            END
         ELSE 'NHF'
    END AS FinalFH

-Eric Isaacs
0
Eric
9/10/2010 2:51:42 AM
hi, 

thanks.  could you please convert the last 3 for me.. i tried, but my brain 
is fried!!!

thanks in advance,
geebee


"Eric Isaacs" wrote:

> It's hard to know exactly what you need since you didn't include any
> table names or variable names, but here's a start that should give you
> the basic idea.  I didn't do all of them, since I'm not sure if this
> is exactly what you need...
> 
> SELECT
>     CASE
>         WHEN [TP] = '2' OR [TP] = '3' OR [INV] = '55555' THEN 'FH'
>         ELSE 'NHF'
>     END AS Field1,
> 
>     CASE
>         WHEN [grp] = 'FN' OR [grp]='FNREM' OR [grp]='FNREMSL' THEN
> 'FN'
>         WHEN [grp]='LLL' THEN 'LLL'
>         ELSE 'Non GS'
>     END AS GS,
> 
>     CASE
>         WHEN [FH] = 'FH' THEN
>             CASE
>                 WHEN [INV] <> '55555' THEN
>                     CASE
>                         WHEN [HS] = 'SSS' THEN 'FH'
>                         ELSE 'NHF'
>                     END
>                 ELSE 'FH'
>             END
>          ELSE 'NHF'
>     END AS FinalFH
> 
> -Eric Isaacs
> .
> 
0
Utf
9/10/2010 2:58:06 PM
On Thu, 9 Sep 2010 18:10:03 -0700, geebee
<geraldjr30@hotmail.com(noSPAMs)> wrote:

>Inv Seg 1
>iif([Final FH]<>'FH',iif([GS]='FN','FN',iif([GS]='LLL','LLL','Non GS')),FH)

CASE WHEN [Final FH] = 'FH' THEN [Final FH]
     WHEN GS IN ('FN', 'LLL') THEN GS
     ELSE 'Non GS'
END

>Inv seg 2
>iif([InvSeg1]='Non GS',iif([HS]='SSS','Other','Non SSS'),[InvSeg1])

CASE WHEN InvSeg1 <> 'Non GS' THEN InvSeg1
     WHEN HS = 'SSS' THEN 'Other'
     ELSE 'Non SSS'
END

>Inv Seg Final
>iif([InvSeg2]='Other',iif([TP]='6','SP','Other'),[InvSeg2])

CASE WHEN InvSeg2 <> 'Other' THEN InvSeg2
     WHEN TP = '6' THEN 'SP'
     ELSE 'Other'
END

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
0
Hugo
9/10/2010 11:12:50 PM
Reply:

Similar Artilces:

Converting Vista Home Office and Student
I have tried repeatedly to convert my already installed version of the program. It will not load. It will not get off trial, and although I have tried with other trial keys, it still will not operate. Somebody please help! Uninstall the trial version, install the paid version. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "briteyes" <briteyes@discussions.microsoft.com> wrote in message news:86A713A6-97FA-467D-93D1-FB1C443EB12C@microsoft.com... >I have tried repeatedly to convert my already...

Converting a number to Hour & Min format
I am attempting to take a basic number, say 2000, and convert the number into an hour and minute format (i.e. 33:33) only. Everytime I use the custom format tab or the time/date tab I get a date included or an AM/PM setting. Any ideas or suggestions? Thanks in advance. Hi, For any number to be converted into time format you need to divide tha number by 24, since Excel time code is 24 (hr) code. then you forma the divided answer (83.33) into time, you will get 8:00 regards sazi -- Message posted from http://www.ExcelForum.com Use Format/Cells/Number/Custom [hh]:mm In article <...

Query: convert multiple logical fields into single 2 character text result
I've solved this particular puzzle, although I'd like to find a better solution than mine. So my emergency is resolved, nevertheless if you like puzzles, please read on... The school district wants a CSV file with fields in a specific order and with specific formatting. Here are the seven possible results for EthnicCd: 2 Char where ML = Multiple Races UN = Unknown or Declined to Report IN = American Indian or Alaskan Native AS = Asian or Pacific Islander BL = African American HI = Hispanic WH = White Our "People" database contains eight ethnicity Yes/No...

The argument type SqlClient cannot be converted into parameter typ
We are upgrading GP7.5 to GP9. At the same time we upgraded our front office from .NET 1.1 to 2. We are now getting this "The argument type SqlClient cannot be converted into parameter type Microsoft.GreatPlains.eConnect.EnumTypes+ConnectionStringType" message. Both the development PC and the server have eConnect9. What can be wrong? -- Leo Hagebeek Project Manager Top Shop Holding NL ...

Help converting vlookup to VB code and run in loop
I have the following formular working on "sheet21" in cells E4 to AR53 but want to run it though a loop in VB on opening sheet. =IF(ISERROR(VLOOKUP(ABS((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(schedul e!E43,"M",""),"T",""),"P","")," ",""))),Product!$A:$K,2,FALSE)),schedule!E43,VLOOKUP(ABS((SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(SUBSTITUTE(schedule!E43,"M",""),"T",""),"P","")," ",""))),Product!$A:$K,2,FALSE)) Thanks for any he...

Can you convert a newsletter in word format to Publisher?
I am in charge of creating a monthly employee newsletter. I found a design I like in Word. Can I convert it to Publisher? Copy/Paste? Some of the image objects maybe on the header. In Word, view, header and footer. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Linda_gray" <Linda_gray@discussions.microsoft.com> wrote in message news:7CBABDCE-50C4-4C4B-97BB-6CBE487BD534@microsoft.com... >I am in charge of creating a monthly employee newsletter. I found a design I > like in Word. Can I convert it to Publis...

More than 7 IF(AND) statements
How can I get more than 7 IF(AND) statements??? -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4918 View this thread: http://www.excelforum.com/showthread.php?threadid=390076 try http://www.cpearson.com/excel/nested.ht -- Bryan Hesse ----------------------------------------------------------------------- Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2105 View this thread: http://www.excelforum.com/showthread.php?thread...

Help with if statement
Ok, I can't quite make this formulat work. Assume: Column D is Birth Year, Column E is Death Year, Column F is Age at Death The basic formula is =E-D (Death Year minus birth year gives me age at death (F)) What I’m trying to do is if the death year column is blank (E), add 100 to the birth year (D), then use that number as the E in the basic formula. Similarly, if the birth year column (D) is blank, subtract 100 from the death year (E) then use that as D in the =E-D formula. This is the statement I've tried: =IF(ISBLANK(E3), (E3=D3+100)), IF(ISBLANK(D3), (D3=E3-...

How to use multiple if statements?
Does Excel 2002 offer something that will replace using multiple if statements (if that is possible)? Say I am calculating state taxes. If income meets a certain range, I apply a specific tax amount. I need a way to test that the income is within the range. How can this be done? Thanks, Brett Hi maybe something like =IF(AND(A1>1,A1<=100),1,IF(AND(A1>100,A1<=200,2,3)) -- Regards Frank Kabel Frankfurt, Germany "Brett" <account@cygen.com> schrieb im Newsbeitrag news:c003b25a.0405151136.2833671a@posting.google.com... > Does Excel 2002 offer something that will...

How to convert a BSTR to CString ??
I'm sure I'm missing something easy but..... How does one convert a BSTR to a CString? More specifically, I am processing the returned VARIANT value from an ActiveX control and it's value is a BSTR that I would like to move to a CString. Thanks in advance for your help. Bob Curry BSTR bstrValue; CString szValue; szValue = (LPCWSTR)bstrValue; Sreeram. Bob Curry wrote: > I'm sure I'm missing something easy but..... How does one convert a BSTR > to a CString? More specifically, I am processing the returned VARIANT value > from an ActiveX control and it...

Powerpoint 2007 is converting PPT 2003 videos into pictures
I opened a PPT 2003 presentation containing a number wmv videos in Powerpoint 2007 to make some minor revisions. When I tried to save the presentation, the program converted all of the videos into pictures. I tried saving in both the current and an older format(2003), neither seemed to work. "PB" wrote: > I opened a PPT 2003 presentation containing a number wmv videos in Powerpoint > 2010 to make some minor revisions. When I tried to save the presentation, the > program converted all of the videos into pictures. I tried saving in both the > current an...

Convert a .wps file to a .pdf file?
How do I convert a .wps file to a .pdf file? ..wps, if I recall correctly, is the extension typically associated with documents created in MS Works, is it not? If that's not it, of course, you probably need to identify which application did create it. In any case, what you'll need to do is find a converter that will work against this document and use it to convert the document to something else, like MS Word format, from which you can then generate a PDF file. Inasmuch as this forum is intended to provide support for MS Access, the database program which is part of MS...

Converting NAD83
is there a formula or add-in with Excel to convert NAD 83 to NAD 27? What are NAD 83 and NAD 27? If just text in cells use Edit>Replace Gord Dibben MS Excel MVP On Thu, 27 May 2010 14:11:24 -0700, ShelleyW <ShelleyW@discussions.microsoft.com> wrote: >is there a formula or add-in with Excel to convert NAD 83 to NAD 27? ...

Converting to web publication
Hello ... I am not sure where exactly to post this problem and apologise in advance if this is the wrong newsgroup .. I am using Publisher 2003 and Front Page 2003 to create and post a calendar on the web. I created the calendar in Publisher and generated the web files using the "Create to web Publishing" and "Web Preview" options in Publisher. I imported the image and htm files into FrontPage to do some minor modifications and create some new links (the htm files are excellent, v. impressive) .. I have one small problem, when I click on a file expecting it (htm fi...

Please convert a .pub Publisher file for me
I know that there is no way to convert a .pub outside of Publisher. I tried the PageMaker converter but it says "cannot convert this document". Please, I need someone to convert 2-3 files to PDF (or anything else readable by me and still has the text edible) ASAP. Someone either reply here and/or preferable email me at squeakypants [at] gmail [dot] com. I know it doesn't mean much anymore, but I'll send you a gmail invite ;) Send the files to gsauer at columbus dot rr dot com Can you edit PDF files? I can convert to Word, the formatting will be lost but the text will be ...

Importing/converting MS Works spreadsheets
Hi, I have many Works spreadsheets (version 4.5a) that need to be converted to Excel format (Excel 2000 - 9.0.3821-SR-1). How can I do this please ? Peter Hi Peter, Here are the instructions for converting Works (version 4.5 incl) to XL 97. Don't worry XL 97 is compatible with XL 2000. If you can open it in XL 97, you will have now difficulty with XL 2000. If you had some complicated macros, then there is a chance you might have had difficulties of some sort. But given that you are using Works, I suspect that you have data only. Here is the link for reference: http://support.micro...

Convert Publisher File to Word
Is anyone aware of a way to convert a file created in MSFT Publisher to Word? Hi JA (u4ia12000@yahoo.com), in the newsgroups you posted: || Is anyone aware of a way to convert a file created in MSFT || Publisher to Word? Well, Word cannot import Publisher files. And Publisher does not have a compatible export option to Word (unless you save it as text only). Your best bet is to copy/paste between the two applications (or drag and drop) or convert each page in Publisher to an image (on each page, go to File > Save As > File Type > *.jpg), and insert into Word as an image for each ...

Converting Work Orders into Purchase Orders
Does anyone have a plug in that can do this? We process about 20 drop ship orders a day, and would love to have a simple way to convert a work order into a purchase order without having to retype everything. You should try the Retail Realm Allocation Manager. Contact me for more information. Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "jessica from Greencoast" <jessicafromGreencoast@discussions.microsoft.com> wrote in message news:CB57FA94-C8BF-4CA2-8134-01319AD14EAA@microsoft.com... > Does anyone have a plug in that can do t...

convert currency to text
how to convert a currency number (for example $100) into a text representing that number (one hundred dollars only) http://support.microsoft.com/default.aspx?scid=kb;EN-US;q213360 -- Kind Regards, Niek Otten Microsoft MVP - Excel "Excel_Instructor" <Excel_Instructor@discussions.microsoft.com> wrote in message news:8F661A1B-1204-415D-94A8-885B705FE7DA@microsoft.com... > how to convert a currency number (for example $100) into a text > representing > that number (one hundred dollars only) Thank you very much for the help you provided .. I really do apprecia...

Converting Quicken 2003 Premier to Money 2003 Deluxe
I just acquired MS Money 2003 and wish to convert my Quicken 2003 files to Money. Money says that I need to use an older version of Quicken to make the conversion. I do not use or have previous versions of Quicken. All previous versions were converted to 2003. Is there anything I can do but discard Money 2003? Export everything you can in QIF, import it all into Money and then try to pick up the pieces. It won't work terribly well and will leave lots of data behind. M04 will be Real Cheap Real Soon Now. At $20 or less, it seems that discarding M03 for M04 (or M05 at maybe $40...

Sum If Not Statement
Hi, I wonder whether someone can help me please. I currently have a text box with the following control source code within it: =Sum(IIf(Not[Impact],[TotalCost],0)) Basically this takes the figure in the 'TotalCost' box on a continuous Subform and where the tick box 'Impact' isn't ticked it takes the figure and puts it on the main form. What I would like to do is to add another variable from another tick box again on the continuous subform. This time it's called 'StatusSelected' and again where it isn't ticked I want it to take the 'TotalC...

converting to Excel from Quattro Pro
I am trying to convert some spreadsheets from Corel Quattro Pro (version 8) to Microsoft Excel 2003. We had no problem bring in word processing files from Corel WP8 to MS Word 2003, but with Excel it doesn't seem to work. (Gives an error message saying " the file format is not valid"). Does anyone have any suggestions for how to do this? Perhaps there is an interim conversion that is required (ie: from Corel 8 to Corel 10 and then to MS Excel)??? Any suggestions would be appreciated. Do you have the option In Quattro Pro to save the file as an Excel file, or as a SYL...

convert cells to negative number
I have a list of numbers that I need a quick and easy way to convert to a negative number. I have tried highlighting the numbers and using a custom format to add the negative sign and parenthesis but when you add the column up; it is not recognizing the numbers as negative. Any suggestions? Why not create a helper column to do it? "sparker" wrote: > I have a list of numbers that I need a quick and easy way to convert to a > negative number. I have tried highlighting the numbers and using a custom > format to add the negative sign and parenthesis but when you add t...

Expert to convert a Lotus Notes 6 .nsf to Outlook .pst file
Hi all, I need an expert to convert my 600 MB Lotus Notes 6 mail archive to a Outlook .pst file. I tried it by installing Domino server and Client, but somehow i couldn't run it properly. I will pay also for it... Does anybody know who or where I can let it to be done?? Thanx much! Adam ...

Vlookup & If statement
I am trying to write a formula that returns the following: I am wanting to do a Vlookup and if the value that the vlookup return is less than zero I want to return zero. If the value that the vlooku returns is greater than zero then I want to see that value. Any Help would be appreciate -- rmeiste ----------------------------------------------------------------------- rmeister's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3016 View this thread: http://www.excelforum.com/showthread.php?threadid=51690 =MAX(vlookup_formula,0) -- HTH Bob Phillips (remove ...