June 22, 2009 at 4:16 am
HI all
I have a table that looks like this:
PublicIdentifierCurrencyOldBalanceOldCurrencyNewBalanceNew
paulthethi@gmail.comEUR0.0NULLNULL
it@novalusus.comNULLNULLEUR1017.58
pslnov@hotmail.co.ukEUR38.50NULLNULL
morgan@hightek.seEUR0.00NULLNULL
rainer.salzbrunn@gmail.comNULLNULLEUR0.00
peterstenslunde@yahoo.co.ukEUR10.00NULLNULL
info@focus-invest-trading.comEUR0.00NULLNULL
tor@imano.noEUR0.00NULLNULL
paulnov@qa.paymentsystemsltd.comEUR0.00NULLNULL
tobbehallberg@telia.comEUR15.00NULLNULL
how do i retrieve results like this following
select PublicIdentifier, Currency, Balance
but where there are no NULL values
June 22, 2009 at 4:22 am
Please provide a sample data of the data you would like to see in the resultset.
June 22, 2009 at 4:31 am
Hi sql_prodigy,
You could try:
select PublicIdentifier
, Currency = case when isnull(CurrencyOld) = 1 then CurrencyNew else CurrencyOld
, Balance = case when isnull(BalanceOld) = 1 then BalanceNew else BalanceOld
, IsNewCurrency = case when isnull(CurrencyOld) = 1 then 'NEW' else 'OLD'
, IsNewBalance = case when isnull(BalanceOld) = 1 then 'NEW' else 'OLD'
from myTable
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
June 22, 2009 at 4:42 am
select * from table
where BalanceOld is not null AND CurrencyNew is not null
June 22, 2009 at 5:02 am
i want to have one Currency and one Balance column.
where the CurrencyOld is null exclude it and include CurrencyNew
and the CurrencyNew is null exclude it and include CurrencyOld
June 22, 2009 at 5:10 am
You can use the query I supplied for exactly that purpose. Just drop the last 2 columns...
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
June 22, 2009 at 5:46 am
select currency=
case when currencyold is null then currencynew
when currencynew is null then currencyold
end,
balance=
case when balanceold is null then balancenew
when currencynew is null then balanceold
end,
publicidentifier
from tablename
June 22, 2009 at 5:48 am
Correction:::
select currency=
case when currencyold is null then currencynew
when currencynew is null then currencyold
end,
balance=
case when balanceold is null then balancenew
when balancenew is null then balanceold
end,
publicidentifier
from tablename
June 22, 2009 at 6:03 am
Have you considered a coalesce instead as that will take the first non null value it finds from two or more fields
COALESCE
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.
Return Types
Returns the same value as expression.
Remarks
If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,...n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
June 22, 2009 at 6:11 am
Hiiiiiii
u can use the below one...
select (case when CurrencyOld is NULL then CurrencyNew else CurrencyOld end) Currency,
(case when BalanceOld is NULL then BalanceNew else BalanceOld end) Balance from URTable
June 22, 2009 at 6:12 am
Yes this will work as well;
select COALESCE (currencyold,currencynew) as currency
from tablename
I thought SQL_PRODIGY wanted a case statement but this is better.
Nice one Laura.
June 22, 2009 at 7:43 am
thanks Laura, the coalesce statement works nicely:w00t:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply