467,908 Members | 1,223 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,908 developers. It's quick & easy.

Return string in query

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
  • viewed: 1557
9 Replies
Expert 2GB
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
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

I want to optimize my query, and exist tables different for every account
Jan 16 '09 #3
Expert 2GB
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
sometime like it:

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
Name1, Name2, id (PK)
number account, id (FK)
number account, id (FK)
number account, id (FK)
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
Expert 2GB

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
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
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)
another message
but even i need the number account and not a message
Jan 20 '09 #7
Expert 2GB
Then return the [number account] column

Expand|Select|Wrap|Line Numbers
  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
However, [number account] should be a string.

Happy coding!

-- CK
Jan 21 '09 #8
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
select 'NO CA' as ACCOUNT
Jan 21 '09 #9
Expert 2GB
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
Happy Coding!

-- CK
Jan 21 '09 #10

Post your reply

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

Similar topics

4 posts views Thread by David Walker | last post: by
3 posts views Thread by Phil Powell | last post: by
8 posts views Thread by Adrian Parker | last post: by
4 posts views Thread by Bob Bedford | last post: by
25 posts views Thread by wd | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.