Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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

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
moorecm
Asked:
moorecm
1 Solution
 
aprestoCommented:
Which database are you using?
0
 
jrb1Commented:
make it:

case when BAR_B_INV.U_DOS_GREATER_345='AGE'
         then BAR_B_INV.U_DOS_GREATER_345'
         else ' ' end
0
 
wstuphCommented:
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
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.

 
moorecmAuthor Commented:
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
 
wstuphCommented:
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
 
moorecmAuthor Commented:
Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now