Return string in query

esteban

Hi,
I have a inconvenient with a query, I'll try to explain me of the best way.
I have accounts like current account, savings account, investments, loans, but everybody doesn´t have all the accounts. So that, for example a person has CA, SC and investments, but when i seek loans, I want to receive a string like "no loans", because in the example the person only has 3 accounts and he doesn´t have loans. Please help me

Jan 16 '09 #1

ck9663

Would it be possible for you to post some sample data of the tables you are working with and your desired output?

-- CK

Jan 16 '09 #2

esteban

Really i can't do it because i only have access to few fields, but an example could be
Person 1 has accounts: current account, savings account, investments and loans, so that I search a CC, SC, Inv or Loans and i obtain a number account

Person 2 has accounts: current account, savings account and investments, but not loans and for example i want to bring loans, but like person 2 doesn´t have loans, I want to present a message. My query is:


select @value=count(account) from dbdata..loans where customer = @id
IF(@value > 0)
select account as CUENTA from dbdata..loans where customer = @id
ELSE
select 'NO POSEE CUENTA AHORROS' as CUENTA

I want to optimize my query, and exist tables different for every account

Jan 16 '09 #3

ck9663

If you have a look up table for the account type, you can use it as main table - left join to your "Persons" table and check if the join keys are NULL. If it is, then return a hard coded string.

Good luck.


-- CK

Jan 16 '09 #4

esteban

sometime like it:

select ISNULL(CC.cuenta,'NO POSEE CUENTA CORRIENTE')
from dbdata..cc CC left join dbdata..cifmst_1 CL on CC.cliente = CL.cliente where CL.cliente = @id

Unfortunately, it doesn´t resolve my problem, can´t help me
Person
Name1, Name2, id (PK)
SC
number account, id (FK)
CC
number account, id (FK)
INV
number account, id (FK)
LOANS
number account, id (FK)

but when i have to search loans, and there isn´t data of loans, I don´t receive nothing, in this case i want to display a message

Jan 16 '09 #5

ck9663

@esteban

Try this:

Or something like that....I did not test the code, but it should give you ideas on how to do it.

Happy coding!

-- CK

Jan 20 '09 #6

esteban

it is ok, but you have to remember that I need the number account of course if exist the data, in other case the message, other friend told me to use this expression
if exists(select account from customer where customerid=@id)
message
else
another message
but even i need the number account and not a message

Jan 20 '09 #7

ck9663

Then return the [number account] column

However, [number account] should be a string.

Happy coding!

-- CK

Jan 21 '09 #8

esteban

it could be, but I followed your indication in my query and when i don´t have a account, in the response I don´t get nothing, only shows the name of column but nothing more

declare @typeAccount varchar(30)
set @typeAccount='CA'
select case when cu.customer is null then 'NO CA' else cast(ca.account as varchar(10)) end as ACCOUNT
from customer cu
left join ca ca on ca.customer = ca.customer
where ca.customer = @id

I have tried another solution, is it ok?

declare @valor, @exist varchar(15)
select @valor=count(CA.account), @exist=CA.account from ca CA where CA.customer = @id group by CA.account
IF(@valor > 0)
select @exist as ACCOUNT
ELSE
select 'NO CA' as ACCOUNT

Jan 21 '09 #9

ck9663

Are you saying the ID exists in both table but the number account is blank?

Then this should work:


Happy Coding!

-- CK

Jan 21 '09 #10