Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hide Selected Coumns from displaying

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-03-05 : 11:15:52
Hi All
I have a union query which retreives information from two differant requests compares them and displays YES,NO,NA based on the comparison in a third row.

Below is my SQL query

SELECT CAST(RR.reqestno AS VARCHAR(MAX)) reqestno,
CAST(RR.receiveddate AS VARCHAR(MAX)) AS receiveddate,
CAST(RR.rptcomments AS VARCHAR(MAX)) AS rptcomments,
CAST(RR.reportfrequency AS VARCHAR(MAX)) AS reportfrequency,
CAST(RR.schedule AS VARCHAR(MAX)) AS schedule,
CAST(RR.schedulebasedon AS VARCHAR(MAX)) AS schedulebasedon,
CAST(RR.clmsrvdtfrom AS VARCHAR(MAX)) AS clmsrvdtfrom,
CAST(RR.clmsrvdtthru AS VARCHAR(MAX)) AS clmsrvdtthru,
CAST(RR.clmpddtfrom AS VARCHAR(MAX)) AS clmpddtfrom,
CAST(RR.clmpddtthru AS VARCHAR(MAX)) AS clmpddtthru,
CAST(RR.customer AS VARCHAR(MAX)) AS customer
from REPORT_REQUEST RR
where RR.reqestno = xxxxxx
UNION ALL
SELECT CAST(RR.reqestno AS VARCHAR(MAX)) reqestno,
CAST(RR.receiveddate AS VARCHAR(MAX)) AS receiveddate,
CAST(RR.rptcomments AS VARCHAR(MAX)) AS rptcomments,
CAST(RR.reportfrequency AS VARCHAR(MAX)) AS reportfrequency,
CAST(RR.schedule AS VARCHAR(MAX)) AS schedule,
CAST(RR.schedulebasedon AS VARCHAR(MAX)) AS schedulebasedon,
CAST(RR.clmsrvdtfrom AS VARCHAR(MAX)) AS clmsrvdtfrom,
CAST(RR.clmsrvdtthru AS VARCHAR(MAX)) AS clmsrvdtthru,
CAST(RR.clmpddtfrom AS VARCHAR(MAX)) AS clmpddtfrom,
CAST(RR.clmpddtthru AS VARCHAR(MAX)) AS clmpddtthru,
CAST(RR.customer AS VARCHAR(MAX)) AS customer
from REPORT_REQUEST RR
where RR.reqestno = yyyyyy
UNION ALL
SELECT
CASE WHEN A.reqestno = B.reqestno THEN 'Yes' ELSE 'No' END as reqestno,
CASE WHEN A.receiveddate = B.receiveddate THEN 'Yes' ELSE 'No' END as receiveddate,
CASE WHEN A.rptcomments is null and B.rptcomments is null then 'NA' WHEN A.rptcomments = B.rptcomments THEN 'Yes' ELSE 'No' END rptcomments,
CASE WHEN A.reportfrequency = B.reportfrequency THEN 'Yes' ELSE 'No' END as reportfrequency,
CASE WHEN A.schedule = 'NONE' and B.schedule = 'NONE' THEN 'NA'WHEN A.schedule = B.schedule THEN 'Yes' ELSE 'No' END as schedule,
CASE WHEN A.schedulebasedon = 'NONE' and B.schedulebasedon = 'NONE' THEN 'NA' WHEN A.schedulebasedon = B.schedulebasedon THEN 'Yes' ELSE 'No' END as schedulebasedon,
CASE WHEN A.clmsrvdtfrom is null and B.clmsrvdtfrom is Null Then 'NA' WHEN A.clmsrvdtfrom = B.clmsrvdtfrom THEN 'Yes' ELSE 'No' END as clmsrvdtfrom,
CASE WHEN A.clmsrvdtthru is null and B.clmsrvdtthru is null then 'NA' WHEN A.clmsrvdtthru = B.clmsrvdtthru THEN 'Yes' ELSE 'No' END as clmsrvdtthru,
CASE WHEN A.clmpddtfrom is Null and B.clmpddtfrom is null then 'NA' WHEN A.clmpddtfrom = B.clmpddtfrom THEN 'Yes' ELSE 'No' END as clmpddtfrom,
CASE WHEN A.clmpddtthru is null and B.clmpddtthru is null then 'NA' WHEN A.clmpddtthru = B.clmpddtthru THEN 'Yes' ELSE 'No' END as clmpddtthru,
CASE WHEN A.customer = B.customer THEN 'Yes' ELSE 'No' END as customer

FROM
(SELECT reqestno,receiveddate,rptcomments,reportfrequency,schedule,schedulebasedon,clmsrvdtfrom,
clmsrvdtthru,clmpddtfrom,clmpddtthru,customer FROM REPORT_REQUEST WHERE reqestno = xxxxxx) AS A
INNER JOIN
(SELECT reqestno,receiveddate,rptcomments,reportfrequency,schedule,schedulebasedon,clmsrvdtfrom,
clmsrvdtthru,clmpddtfrom,clmpddtthru,customer FROM REPORT_REQUEST WHERE reqestno = yyyyyy) AS B
ON 1 = 1


Question
How should i edit the above query to make it display only columns that have only a value of 'NO' after comparison and in some way hide the other columns whereever comparison returns YES or NA



Sample Results
requestno receiveddate rptcomments reportfrequency schedule schedulebasedon clmsrvdtfrom clmsrvdtthru clmpddtfrom clmpddtthru customer
300431 Nov 29 2012 10:28AM Recurring Calendar Year May 1 2012 12:00AM Oct 31 2012 12:00AM Jan 1 1900 12:00AM Jan 1 1900 12:00AM LIFTONE, LLC
223681 Jan 18 2011 9:49AM Please clone from 2010 request. T. Rowe is "sliced business" with UHC and Carefirst. They are moving to a Datza Warehouse to gain a full picture of their population. Data Warehousing: UHC to submit via FTP monthly medical claims to Thompson Reuters Recurring Calendar Year Jan 1 1900 12:00AM Jan 1 1900 12:00AM Jan 1 2011 12:00AM Jan 31 2011 12:00AM T. ROWE PRICE GROUP, INC.
No No No Yes NA Yes No No No No No


Need help please
Thanks in advance

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-05 : 11:36:01
Strange logic, First of all I think there would be no way to get yes from

CASE WHEN A.reqestno = B.reqestno THEN 'Yes' ELSE 'No' END as reqestno,

When you're joining the two sets with two different requestno and then joining them

"reqestno = xxxxxx" when joins with the record "reqestno = yyyyyy"

Any way if you think your query is working correctly per business logic then what you can do is to apply an outer select e.g.
SELECT * FROM
(
your query
)A
Where A.ColumnName = 'No' [and A.columnName2='no' And ...]

Cheers
MIK
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-03-05 : 11:56:09
The Requestno is never going to come as yes as always provide 2 unique request numbers.

I was not able to make your help work as we dont know what columns are going to be NO after comparison
thanks

quote:
Originally posted by MIK_2008

Strange logic, First of all I think there would be no way to get yes from

CASE WHEN A.reqestno = B.reqestno THEN 'Yes' ELSE 'No' END as reqestno,

When you're joining the two sets with two different requestno and then joining them

"reqestno = xxxxxx" when joins with the record "reqestno = yyyyyy"

Any way if you think your query is working correctly per business logic then what you can do is to apply an outer select e.g.
SELECT * FROM
(
your query
)A
Where A.ColumnName = 'No' [and A.columnName2='no' And ...]

Cheers
MIK

Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-03-05 : 14:58:38
I'm still looking for help on my initial query


thanks
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-06 : 04:41:16
As I mentioned: assuming your query is correct per your requirements, then use and outer select on that whole query along with required "Where condition(s)" to have the desired output. For example

SELECT * FROM
(
Paste here the complete SQL query you mentioned on the top
) A
WHERE receiveddate='YES' AND rptcomments='YES' -- Assuming that you want to have only the records having ReceiveDate and RptComments as Yes. If you want to include any other column you can simply use AND/OR operator as per your requirements in this Where condition

However, I would suggest that you come up with sample data in the form of INSERT Statements and the desired output you wanted to have based on that given sample data. That way the SQL team would be able to help you exactly as you wanted to have ..
Thanks!

Cheers
MIK
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-03-06 : 09:08:15
Hi
I would like to see only the columns that have returned as NO in a particular record

My present query returns all columns for a record after comparing and i want to return only the columns that have 'NO' in the third






quote:
Originally posted by MIK_2008

As I mentioned: assuming your query is correct per your requirements, then use and outer select on that whole query along with required "Where condition(s)" to have the desired output. For example

SELECT * FROM
(
Paste here the complete SQL query you mentioned on the top
) A
WHERE receiveddate='YES' AND rptcomments='YES' -- Assuming that you want to have only the records having ReceiveDate and RptComments as Yes. If you want to include any other column you can simply use AND/OR operator as per your requirements in this Where condition

However, I would suggest that you come up with sample data in the form of INSERT Statements and the desired output you wanted to have based on that given sample data. That way the SQL team would be able to help you exactly as you wanted to have ..
Thanks!

Cheers
MIK

Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-06 : 10:02:02
Sorry put No in the conditions instead of Yes.

Cheers
MIK
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-06 : 12:57:01
The short answer is NO, you cannot hide columns based on a condition. However, you coulld do some really bad/funky Dynamic SQL that would determine which columns have a "Yes" value and then ommit them from the select list. But, I'd think that ignoring them or having your presentaion layer handle that would be better.
Go to Top of Page
   

- Advertisement -