Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...Sub Query HelpSub Query Help
Previous
 
Next
New Post
9/24/2010 3:47 PM
 

I am having trouble with a query that returns data for a staff contact list on a DNN reports page. The query that works fine is below:

   SELECT Cast(HR_master.GivenName+''+HR_master.Surname as varchar(50)) as Name, PhoneNumber1 as Phone, PhoneNumber4 as Extension, PhoneNumber3 as Cellular FROM HR_Master Where HR_master.EmployStatus = '01'

The problem is that in the PhoneNumber3 field, we have some work cell numbers and some personal cell numbers. We only want the work ones to show, so we enter each personal one in the DB like this:  555-1212(personal)
I need to filter those to only display work ones and leave the personal ones blank (not showing).

I thought I could do it with a sub query. Here is what I tried.

     SELECT Cast(HR_master.GivenName+''+HR_master.Surname as varchar(50)) as Name, PhoneNumber1 as Phone, PhoneNumber4 as Extension, (SELECT PhoneNumber3 FROM HR_master WHERE PhoneNumber3 not like '%(personal)') AS Cellular FROM HR_Master Where HR_master.EmployStatus = '01'

But I get an error that the query returned more than 1 result. Can anyone see how I could restructure this for the correct result? I have not been able to find an example of a sub query that does this kind of thing.

THX!

 
New Post
9/24/2010 7:40 PM
 
Why not to the WHERE clause in the original statement , AND PhoneNumber3 not like '%Personal%' instead of doing a subquery.

Chris Hammond
Former DNN Corp Employee, MVP, Core Team Member, Trustee
Christoc.com Software Solutions DotNetNuke Module Development, Upgrades and consulting.
dnnCHAT.com a chat room for DotNetNuke discussions
 
New Post
9/27/2010 11:03 AM
 
Chris,
Thanks for replying. I tried that option and the entire record gets filtered out (for each one that has "personal"). I need it to not display the field data if it matches the criteria, but still show the rest of the record.
I may have to enter the personal cell phone numbers in a different field in order to make this work.

MP
 
New Post
9/27/2010 4:43 PM
 
SUCCESS!
I got it figured it out. For those interested, I used a case statement.

'Cellular' = CASE
WHEN PhoneNumber3 LIKE '%personal%' THEN 'None'
ELSE PhoneNumber3
END

So, the whole query looks like this.

SELECT Cast(HR_master.GivenName+''+HR_master.Surname as varchar(50)) as Name, PhoneNumber1 as Phone, PhoneNumber4 as Extension, 'Cellular' = CASE
WHEN PhoneNumber3 LIKE '%personal%' THEN 'None'
ELSE PhoneNumber3
END FROM HR_Master Where HR_master.EmployStatus = '01'


This checks the data from that field and if it contains 'personal' then it displays as 'None'. Otherwise, it shows the number.
Thank god for google. It could have taken a month to figure that out the old way (reading big books).

MP
 
New Post
9/27/2010 5:03 PM
 
Sorry, I misunderstood, I thought you wanted to show only those that weren't personal, not just hide that data.

Chris Hammond
Former DNN Corp Employee, MVP, Core Team Member, Trustee
Christoc.com Software Solutions DotNetNuke Module Development, Upgrades and consulting.
dnnCHAT.com a chat room for DotNetNuke discussions
 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...Sub Query HelpSub Query Help


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out