Using IF in a SELECT statement?

  • Can it be done?

    What I want to do is select a bunch of info from an "account" table, and IF the billing method is "Credit Card", then display the columns w/ the CC info.

    I can use CASE, but then I'll need to do CASE WHEN bill_method = 'CC' 4 times to display CC_Name, CC_Type, CC_Number, CC_Exp. If I can use IF, that will be more efficient, right?

    Thanks in advance.

  • IF Exists(Select Billing

    From Table

    Where Billing = 'Credit Card')

    Begin

    Select CC_1, CC_2, CC_3

    From Table

    Where Billing = 'Credit Card'

    End

    Else

    PRINT 'No Row(s) Found !'

    MW

    Edited by - mworku on 06/18/2003 2:42:41 PM


    MW

  • Nope, can't put that in a SELECT statement.

    This is what I'm doing:

    SELECT account_id, bill_contact, bill_address

    FROM account

    This is what I want:

    SELECT account_id, bill_contact, bill_address,

    (IF bill_method = 'CC' THEN (SELECT CC_Number, CC_TYPE, CC_Exp FROM credit_card)

    FROM account

    Is that possible? Right now I am INNER JOINING accounts to credit_card so the query is returning NULLS on the CC columns if bill_method is not 'CC'. Is that a waste?

  • Hello.

    Joins would be the correct way to handle this. If you'd like more feedback go ahead and post your query.

    Everett



    Everett Wilson
    ewilson10@yahoo.com

  • SELECT account_id, bill_contact, bill_address,isnull(cc_number,' '),

    isnull(cc_type,' '),isnull(cc_exp,' ')

    from account a

    left join credit_card c

    on a.account_id=c.account_id

    where a.bill_method='CC'

    HTH

  • Join is the Answer and right Approach to this problem.

    AMIT KULSHRESTHA

    SOFTWARE ENGINEER

    NEW DELHI,INDIA


    AMIT KULSHRESTHA
    SOFTWARE ENGINEER
    NEW DELHI,INDIA

  • SELECT account_id, bill_contact, bill_address,

    (IF bill_method = 'CC' THEN (SELECT CC_Number, CC_TYPE, CC_Exp FROM credit_card)

    FROM account

  • SELECT account_id, bill_contact, bill_address,bill_method = Case

    When 'CC' Then (SELECT CC_Number, CC_TYPE, CC_Exp FROM credit_card)

    FROM account

    WHen 'ZZ' Then Do something else

    End as 'The Value'

    From account

  • quote:


    SELECT account_id, bill_contact, bill_address,bill_method = Case

    When 'CC' Then (SELECT CC_Number, CC_TYPE, CC_Exp FROM credit_card)

    FROM account

    WHen 'ZZ' Then Do something else

    End as 'The Value'

    From account


    This will not work. You cannot return more than a single field from a CASE statement on a single field. Again, JOIN is correct way of dealing with this, coupled with ISNULL. See Nazim's solution.

  • SELECT a.account_id, a.bill_contact, a.bill_address, cc.cc_number,

    cc.cc_type, cc.cc_exp

    FROM account a

    inner join credit_card cc

    on a.account_ID = cc.account_id

    where a.bill_method = 'CC'


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • Select a.account_id, a.bill_contact, a.bill_address, cc.cc_number, cc.cc_type, cc.cc_exp

    From Account a, (Select *

    From Credit_Card) As cc

    Where (a.bill_method = 'CC' AND

    a.account_id = cc.account_id)

    MW


    MW

  • With the following assumptions I am trying to answer the nikels question ,

    Accounts table is some thing like

    
    
    Cretate table Accounts
    (
    acccountid int ,
    cc_type varchar(2) ,
    ……..
    )

    and credit card table

    
    
    create creditcard
    (
    accountid int ,
    ccnumber varchar(50)
    )

    Here I am trying to use much under explored area of sub quires

    
    
    Select A.accountid,A.cc_type,
    cc_no_or_dc_no
    =case when cc_type=’cc’ then
    ( select cc_no from creditcard B where A.accountid=B.accountid)
    when cc_type=’dc’ then
    ( select dc_no from debitcard B where A.accountid=B.accountid)
    else ‘No more credit’
    end
    From
    Accounts A

    I think this will solve his problem ,just for showing other credit methods are possible I added one more debitcard table ,This method will reduce no matches in the query

    With best

    Regards

    John

  • With the following assumptions I am trying to answer the nikels question ,

    Accounts table is some thing like

    
    
    Cretate table Accounts
    (
    acccountid int ,
    cc_type varchar(2) ,
    ……..
    )

    and credit card table

    
    
    create creditcard
    (
    accountid int ,
    ccnumber varchar(50)
    )

    Here I am trying to use much under explored area of sub quires

    
    
    Select A.accountid,A.cc_type,
    cc_no_or_dc_no
    =case when cc_type=’cc’ then
    ( select cc_no from creditcard B where A.accountid=B.accountid)
    when cc_type=’dc’ then
    ( select dc_no from debitcard B where A.accountid=B.accountid)
    else ‘No more credit’
    end
    From
    Accounts A

    I think this will solve his problem ,just for showing other credit methods are possible I added one more debitcard table ,This method will reduce no matches in the query

    With best

    Regards

    John

  • Not that this thread isn't too belabored already, but I just wanted to point out that in most cases, it is much more efficient to have a normalized solution than not. Therefore this:

    
    
    SELECT
    A.accountid
    , A.cc_type
    , cc_no_or_dc_no = case
    when cc_type=’cc’ then
    (
    select cc_no
    from creditcard B
    where A.accountid=B.accountid
    )
    when cc_type=’dc’ then
    (
    select dc_no
    from debitcard B where A.accountid=B.accountid
    )
    else ‘No more credit’
    end
    FROM Accounts A

    would be better off with a single Card Table, not 2 separate card tables with the same structure called CreditCard and DebitCard. Also, Why have the credit card type in the Account table and the Card number in another table? Doesn't make sense. Instead, schema should have like data in the same table:

    
    
    CREATE TABLE Account
    (
    AccountID INT NOT NULL
    --More Fields
    )
    CREATE TABLE AccountCard
    (
    AccountID INT NOT NULL
    , CardType CHAR(2) NOT NULL
    , CardNo CHAR(20) NOT NULL
    -- More Fields (e.g. CardExpDate, NameOnCard, etc...
    )
    CREATE TABLE CardType
    (
    CardTypeCode CHAR(2) NOT NULL
    , CardTypeDesc VARCHAR(10) NOT NULL
    )

    I hope this sheds some light on the issue in johncyriac's reply.

    jay

  • ReallyReally I wrote something with out seeing the actual question ,

    In general if you want to add more columns go for joins ,

    Why all are complicating elementary things (including me ) ,

    Really if some one like jay is not there it will end up in mars only,

    thank you jay

    John

    nb : Think this much is enough for this topic

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply