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
483 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…

726 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