June 18, 2003 at 2:05 pm
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.
June 18, 2003 at 2:30 pm
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
June 18, 2003 at 3:34 pm
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?
June 18, 2003 at 4:10 pm
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
June 19, 2003 at 12:00 am
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
June 19, 2003 at 1:02 am
Join is the Answer and right Approach to this problem.
AMIT KULSHRESTHA
SOFTWARE ENGINEER
NEW DELHI,INDIA
AMIT KULSHRESTHA
SOFTWARE ENGINEER
NEW DELHI,INDIA
June 19, 2003 at 2:46 am
SELECT account_id, bill_contact, bill_address,
(IF bill_method = 'CC' THEN (SELECT CC_Number, CC_TYPE, CC_Exp FROM credit_card)
FROM account
June 19, 2003 at 2:48 am
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
June 19, 2003 at 6:48 am
quote:
SELECT account_id, bill_contact, bill_address,bill_method = CaseWhen '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.
June 19, 2003 at 9:41 am
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
June 19, 2003 at 11:25 am
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
June 20, 2003 at 12:28 am
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
June 20, 2003 at 12:38 am
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
June 20, 2003 at 6:28 am
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
June 20, 2003 at 9:00 am
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