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
1041 Views

Similar Articles

[PageSpeed] 31

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:

Convert text string in format of cell reference to a cell reference
Is there any way of converting a text string in the format of a valid cell reference i.e. " 'Sheet1'!F23 " to an actaul cell reference that could be used in a formula? Frank You could use the INDIRECT() function. This will use the value in the cell as a cell reference. So if Merges!A1 was in B2, INDIRECT(B2) would return the value in Merges!A1 Andy. "Frank" <anonymous@discussions.microsoft.com> wrote in message news:3EAAD6E9-C337-4BF6-B721-A7C75D5D99EA@microsoft.com... > Is there any way of converting a text string in the format of a valid cell referenc...

Text Converters
I just installed Office Mac X, replacing my old OS9 Office 2001. I have some Microsoft FoxPro files (.dbf) that I wish to use as sources for data merging. There is a FoxPro - dBase text converter that works with 2001. Can I just put the old converter in the proper folder in the new installation? It doesn't seem to work. Any solutions or ideas? -- TIA, G in article 150920041824470402%gburch@columbus.rr.com, Gene Burch at gburch@columbus.rr.com wrote on 16/09/04 0:16: > I just installed Office Mac X, replacing my old OS9 Office 2001. > > I have some Microsoft FoxPro files ...

convert .pub to ???
Need to convert a one page .pub file with text and a couple of pictures to some folks to view that don't have Publisher. In Word or Excel, I can convert to .pdf file(Acrobat Adobe) and send. Suggestions please, L. You can convert a Publisher file to a PDF. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "L." <ofa@yahoo1.com> wrote in message news:e1sSanDiHHA.872@TK2MSFTNGP03.phx.gbl... > Need to convert a one page .pub file with text and a couple of pictures to > some folks to view that don't have ...

Shortcut to convert all formulas to absolute references?
I thought I read that this is possible. Am I wrong -- Kevla ----------------------------------------------------------------------- Kevlar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=723 View this thread: http://www.excelforum.com/showthread.php?threadid=26940 Hi! http://tinyurl.com/5c7fr contains "a gaggle" of such converters, courtesy of Gord Dibben. Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http:...

Export from Access to Excel
Hi when I download or copy numbers from Access to Excel a new feature "convert to number" appears. I am forced to mark all numbers and click "convert to numbers" otherwise Excel doesn't calculate. Is there anyway to download from Access to Excel without having to convert the text to numbers first (like it used to be before)? I have huge data exports and this takes up lots of time. Thank you, Martina What version are you using? From 2000 the "Office LInks" option "analyze with Excel" avoids this problem (though this could well be what you ar...

IIf and Trim
Hi, I use IIf([ReserveDate] Is Null,Null,([ReserveDate] & " & " & [ReserveDateTo])) to display ReserveDate & ReserveDateTo if ReserveDate is Null. It works fine. However, when ReserveDate has value, it displayed with &. Ex: 12/22/08 &. Would you please show me how to take the “&” off? Thanks Chi Try: [ReserveDate] & IIf(IsNull([ReserveDateTo]),Null," & " & [ReserveDateTo]) -- Duane Hookom Microsoft Access MVP "Chi" wrote: > Hi, > > I use IIf([ReserveDate] Is Null,Null,([ReserveDate] & " &am...

wich advantage to convert access 2000 to 2003 for my program?
hi i have more program created with vb.net and access 2000 as database i would like convert this databases on versione 2003 or 2007 if i shoud do it, wich adavantage i take for this conversion? security? more running? or other? thanks If the front end is VB.NET application, you will not gain any advantage from converting the back end from an Access 2000 mdb to an Access 2002/3 format mdb. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Amodio" <Amo...

Converting YYYYMMDD Special Format to Text
What formula or function converts a cell with a special date format of YYYYMMDD, lets say 20030801, to the text string "20030801"? In my case the special format option YYYYMMDD makes the number appear as 20030801 but will only display the actual date value of 37834 when attempting to convert the date to a text string. One way: =TEXT(A1, "yyyymmdd") where A1 contains your value. In article <108901c38960$b4c9b6d0$a301280a@phx.gbl>, "Mike" <mbredal@tetontel.com> wrote: > What formula or function converts a cell with a special > ...

converting from access 97 to xp using vb front end
I get an error when I try to run a vb6 program after access 97 has been converted to xp. The error says run time err 3343 unrecognized database format. Is there something I need to set in VB6 to recognize xp> ...

using iif but coming up with error#
ok i have the if down but its causing me a brain drain lol i want to divide my hours utilised by the amunt of pallets moved but all its coming out with is an error# sign PPH: IIf([Delivery Type]="Palletised",[hours]/[pallets]) PPH = pallets per Hour any ideas There are two things that stand out. First, you have no False condition specified in your IIf. That is what value to assign if [Delivery Type]<>"Palletised" The other is that you cannot divide by zero or Null. You need to include code to transform the values of 0 and Null for the divisor and you need ...

Converting from Publisher98 to Publisher2003
I am needing to convert some of my older data from Microsoft Publisher 98 to use in Microsoft Publisher 2003 - If I try to just open up the files from 98 into 2003 it tells me that it can not be used from older versions. Could someone tell me how I can convert these files? Thank you... If you have Norton, disable "script blocking" and in Norton Options, misc. disable "Office Plug-in", if you don't have Norton, disable whatever anti-virus software you do have until the conversion is complete. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvp...

IF,Then statement?
Hi All I am trying to find a way to write and if/then statement. I have a column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a column (B) of gas MPG. I want to average each type of gas. So I was thinking of adding a column (C) for each type of gas. If A#=C(header), THEN place B# in C#. I can then take and average the column. I just don't know how to write it? OR is there a simplier way to write it? Thanks Bill -- bhowe ------------------------------------------------------------------------ bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&user...

Case statement vs Change/lookup table
Hello All Gurus, This is a problem that I have encountered and was wondering what would be a better approach to the solution I have a view that is pulling data from a table which is fed from an external application. Some of the data due to external situations is wrong/spelled wrong, in the past when this happened I updated another view with a complex set of case statements. This is fast and stable, however, it is for only three instances as they are the only mistakes and they happen always, aka the data is always spelled wrong for these three instances. Now there is a situation wher...

Help If Statement
Con you please give me the formula for the following statement Column L must equal column N only if column K=TRUE if column K=FALS column L must equal 0. Thanks Linse -- Message posted from http://www.ExcelForum.com See answer to your previous post. In article <drreamsurfer.1bnjtu@excelforum-nospam.com>, drreamsurfer <<drreamsurfer.1bnjtu@excelforum-nospam.com>> wrote: > Con you please give me the formula for the following statement > > Column L must equal column N only if column K=TRUE if column K=FALSE > column L must equal 0. > > Thanks =IF(K1...

Imported statements in Money 2008 disappear
I have downloaded statements from credit union in 'qfx" format. Using 'Money 2008 Home and Business' the "file' 'import' says the file import complete but does not ask which account should be associated with the statement and the statement seems to be nowhere in Money. Does anyone else have this problem? Where is the imported file? Thank you I had something similar with a recent .ofx statement. Found it in Temporary Internet Files folder. On my WindowsMe system Temporary Internet Files is located C:\WINDOWS I moved the statement to my desktop. ...

Blank entry in an IF statement returns FALSE. I want it to be blank
When I have no entry in a cell within an IF statement it returns as "FALSE". I want it to be blank and have no value. If I use if(ai="","") I still get FALSE unless the entry is done via the spacebar and then I get a blank cell. How do I get a blank cell when no entry is made? Thank you, Rudy You need to give the formula a FALSE option. Assuming that you want the value in A1 use: =IF(A1="","",A1) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com wi...

can not find graphic convert for Publisher 2000
- some of these convereters maybe missing. If so where do I find new grahic converters Although reinstalled Publisher 2000 will not draw up photos. States that need Graphic Converter. No sign of them on MS site. Have cleared PC of all old publisher but still no luck in using it again. It still states can not convert picture Martyn Long Hi Martyn Long (Martyn Long@discussions.microsoft.com), in the newsgroups you posted: || - some of these convereters maybe missing. If so where do I find || new grahic converters Although reinstalled Publisher 2000 will not || draw up photos. States ...

convert or open .pub file
I have recieved a file with a .pub extention. I do not have publisher, any ideas on how to open it, and readers that I can download. No trial is offered for this program to download? I tried acrobat distiller (microsoft said it would work) but it dosent work thank corey corey wrote: > I have recieved a file with a .pub extention. I > do not have publisher, any ideas on how to > open it, and readers that I can download. ================================= Best bet would be open it in the Publisher version that created it. There are no Publisher viewers. Here's some info you ma...

convert formula to VBA
I need some help figuring out how to convert formulas that I have working in an excel spreadsheet into VBA language in Excel. I have the following three formulae that do just what I want. I go through all kinds of hoops to open excel, write this formula in a cell, copy it then write it to a new spreadsheet. would be much faster if i could capture the value of all three formulae in VBA. Suggestions appreciated: =max(h1:h300) =(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0))) =ROW(OFFSET(A1,COUNTA(A:A)-1,0)) What do I need to do to get each of those working. I think i...

Converting to Money from Quicken
My Quicken 08 upgrade crashes when downloading transactions. I am considering switching to Money. Money Essentials does everything I need done which is download transactions and balance the check book. But, I would like to be able to transfer ~15 years of data into it from Quicken 08. Does Money read QIF files? Bob In microsoft.public.money, BobS wrote: >My Quicken 08 upgrade crashes when downloading transactions. I am considering >switching to Money. Money Essentials does everything I need done which is >download transactions and balance the check book. > >But, I wo...

How can i convert a tiff photo to jpeg format?
I have photos saved in TIFF that I can not send in e-mails or save as screen savers. Most are high definition photos. Can they be saved in JPEG format? How? Do they need to be compressed somehow? How do I do that? I think Infanview will convert your images. http://www.irfanview.com/ -- Don Vancouver, USA "Rita" <Rita@discussions.microsoft.com> wrote in message news:17B9D796-EC87-4FCE-B970-DD221D7E674B@microsoft.com... >I have photos saved in TIFF that I can not send in e-mails or save as >screen > savers. Most are high definition photos. Can they be sav...

How do I convert my 4 OE6 mail accounts into 4 Outlook profiles?
I am in the process of converting from Outlook Express (OE6) to Outlook. I currently have 4 OE6 mail accounts. I want to create one Outlook profile for each of my curent OE6 mail accounts. How do I import the e-mail messages into the new Outlook Profiles -- 1 at a time? The import feature in Outlook does not ask me which OE6 Account I want to import messages from. Thus, I concern that it may messages from all 4 OE6 mail accounts -- that is not what I want. I simply want 4 newly created Outlook profiles/mail accounts for each of the current OE6 mail accounts. ft3 <ft3@discussi...

convert outlook 2003 pst file to outlook 2000 pst file
Hi all I have a little a problem and would appreciate any advice. I uninstalled outlook 2003 and reinstalled outlook 2000. At the time I uninstalled outlook 2003, I did not convert the pst file to the outlook 2000 format. On my pc is currently outlook 2000 but I am unable to import the pst file which is in outlook 2003 format. Any ideas how I can convert .pst file into a format which can be imported into outlook 2000 without reinstalling outlook 2003? Many thanks Ronnie Kotkis Hello Ron, If you just try to add the personal folder into your Outlook 2000 what happens. To add - Tools - S...

how convert Works wdb to Access mdb?
I have several MS Works wdb files that I need to convert to MS Access mdb files. Works can convert wdb files to csv, txt, or dbf files. Access can open only mdb, mda, mdw, and mde files. Is there any intermediary utility? While Access can only open the file types you indicated, it can import csv, txt or dbf files. Look under File | Get External Data. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "David" <anonymous@discussions.microsoft.com> wrote in message news:097201c3dc3d$9c89d3e0$a501280a@phx.gbl... > I have several...

RMS Statements #2
Every month I end up printing statements with credit balances or zero balances. The only solution was to print blank sheets for zero balances. The other issue is sorting. We do everything by customer number and RMS prints by last name. Any suggestions? ...