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
451 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
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 250 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now