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
462 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Read about achieving the basic levels of HRIS security in the workplace.
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…

920 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