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

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
  • viewed: 1557
Share:
9 Replies
ck9663
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
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 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:

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 2GB
@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
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 2GB
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
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 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
  11.  
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.