By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Return string in query

P: 7
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
Share this Question
Share on Google+
9 Replies


ck9663
Expert 2.5K+
P: 2,878
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

P: 7
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
Expert 2.5K+
P: 2,878
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

P: 7
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
Expert 2.5K+
P: 2,878
@esteban

Try this:

Expand|Select|Wrap|Line Numbers
  1. select person.id, name1, name2,
  2. case when sc.id is null then 'NO SC ' else 'WITH SC' end,
  3. case when cc.id is null then 'NO CC ' else 'WITH SC' end,
  4. case when inv.id is null then 'NO INV ' else 'WITH INV ' end,
  5. case when loans.id is null then 'NO LOANS ' else 'WITH LOANS' end
  6. from persons
  7. left join sc on sc.id = persons.id
  8. left join cc on cc.id = person.id
  9. left join inv on inv.id = person.id
  10. left join loans on loans.id = person.id
  11.  
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

P: 7
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
Expert 2.5K+
P: 2,878
Then return the [number account] column

Expand|Select|Wrap|Line Numbers
  1.  
  2. select person.id, name1, name2,
  3. case when sc.id is null then 'NO SC ' else [number account] end as scaccount,
  4. case when cc.id is null then 'NO CC ' else [number account] end as ccaccount,
  5. case when inv.id is null then 'NO INV ' else [number account] end as invaccount,
  6. case when loans.id is null then 'NO LOANS ' else [number account] end as loanaccount
  7. from persons
  8. left join sc on sc.id = persons.id
  9. left join cc on cc.id = person.id
  10. left join inv on inv.id = person.id
  11. left join loans on loans.id = person.id
  12.  
  13.  
However, [number account] should be a string.

Happy coding!

-- CK
Jan 21 '09 #8

P: 7
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
Expert 2.5K+
P: 2,878
Are you saying the ID exists in both table but the number account is blank?

Then this should work:


Expand|Select|Wrap|Line Numbers
  1. select person.id, name1, name2,
  2. 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,
  3. 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,
  4. 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,
  5. 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
  6. from persons
  7. left join sc on sc.id = persons.id
  8. left join cc on cc.id = person.id
  9. left join inv on inv.id = person.id
  10. left join loans on loans.id = person.id
  11.  
Happy Coding!

-- CK
Jan 21 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.