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

[ck9663]

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

-- CK

[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

[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

[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

[ck9663]

@esteban

Try this:

select person.id, name1, name2,
case when sc.id is null then 'NO SC ' else 'WITH SC' end,
case when cc.id is null then 'NO CC ' else 'WITH SC' end,
case when inv.id is null then 'NO INV ' else 'WITH INV ' end,
case when loans.id is null then 'NO LOANS ' else 'WITH LOANS' end
from persons
left join sc on sc.id = persons.id
left join cc on cc.id = person.id
left join inv on inv.id = person.id
left join loans on loans.id = person.id
 

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

Happy coding!

-- CK

[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

[ck9663]

Then return the [number account] column

 
select person.id, name1, name2,
case when sc.id is null then 'NO SC ' else [number account] end as scaccount,
case when cc.id is null then 'NO CC ' else [number account] end as ccaccount,
case when inv.id is null then 'NO INV ' else [number account] end as invaccount,
case when loans.id is null then 'NO LOANS ' else [number account] end as loanaccount
from persons
left join sc on sc.id = persons.id
left join cc on cc.id = person.id
left join inv on inv.id = person.id
left join loans on loans.id = person.id
 
 

However, [number account] should be a string.

Happy coding!

-- CK

[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

[ck9663]

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

Then this should work:

select person.id, name1, name2,
case when sc.id is null or sc.[number account] is null or sc.[number account] = '' then 'NO SC ' else [number account] end as scaccount,
case when cc.id is null or cc.[number account] is null or cc.[number account] = '' then 'NO CC ' else [number account] end as ccaccount,
case when inv.id is null or inv.[number account] is null or inv.[number account] = '' then 'NO INV ' else [number account] end as invaccount,
case when loans.id is null or loans.[number account] is null or loans.[number account] = ''  then 'NO LOANS ' else [number account] end as loanaccount
from persons
left join sc on sc.id = persons.id
left join cc on cc.id = person.id
left join inv on inv.id = person.id
left join loans on loans.id = person.id
 

Happy Coding!

-- CK