Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using SQL how to get only certain rows from a query to appear on the report under if else statements

Posted on 2006-07-14
6
Medium Priority
?
499 Views
Last Modified: 2008-02-26
Hi,

I'm trying to build a custom report which uses SQL.  I've created a dervied column called - BAR_B_INV.U_DOS_GREATER_345.  The results in that column either say 'NOT AGE' or 'AGE'

In my custom report under the display section for that column, I only want that row to print on my report if the value in that column = 'AGE'.

I tried the following statement, but I get a syntax error - IF BAR_B_INV.U_DOS_GREATER_345='NOT AGE' THEN BLANK ELSE BAR_B_INV.U_DOS_GREATER_345

Here is an example of the data:

PAT_NM             SER_DT      CHG_AMT      INV_NUM              BAR_B_INV.U_DOS_GREATER_345
MOORE,CATHERINE       1/23/2006          107          8000658      AGE
TEST,RICHARD       5/4/2006            42          8000360      NOT AGE
TEST,RICHARD       3/31/2006            42          8000361      NOT AGE

I would only want the first row to print on my report as this has the value of 'AGE' in that column.  I would not want the other 2 rows to print.

How can this be done?

Thanks,
Christine
0
Comment
Question by:moorecm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 17109100
Which database are you using?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17109203
make it:

case when BAR_B_INV.U_DOS_GREATER_345='AGE'
         then BAR_B_INV.U_DOS_GREATER_345'
         else ' ' end
0
 
LVL 12

Expert Comment

by:wstuph
ID: 17109223
If you want to remove the line then you have to put that into the where clause.  How exactly to do that depends on the database.  Please post the DB and the query.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:moorecm
ID: 17110096
Hi Wstuph,

The system is called IDX (it's a medical practice mgmt system)  The queries are written in sql in the DBMS utility.  I believe the IDX system runs on Mumps programming.

Here is my query:

SELECT                                  
PAT_NM,                                
BAR_B_INV.SER_DT,                      
INV_BAL,                                
INV_NUM,                                
BAR_B_INV.U_DOS_GREATER_345            
FROM                                    
BAR_B_INV,                              
REG_B_PAT                              
WHERE                                  
INV_BAL > 0.01 AND                      
BAR_B_INV.FSC__2 IN ('101','105','106')
ORDER BY                                
BAR_B_INV.U_DOS_GREATER_345            
;                        
0
 
LVL 12

Accepted Solution

by:
wstuph earned 1000 total points
ID: 17110482
Try this instead

SELECT                                  
PAT_NM, BAR_B_INV.SER_DT, INV_BAL, INV_NUM, BAR_B_INV.U_DOS_GREATER_345
FROM BAR_B_INV, REG_B_PAT                              
WHERE INV_BAL > 0.01
AND BAR_B_INV.FSC__2 IN ('101','105','106')
and BAR_B_INV.U_DOS_GREATER_345 = 'AGE'
ORDER BY BAR_B_INV.U_DOS_GREATER_345;                        
0
 

Author Comment

by:moorecm
ID: 17110783
Thanks.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question