moorecm
asked on
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_34 5. 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_34 5='NOT AGE' THEN BLANK ELSE BAR_B_INV.U_DOS_GREATER_34 5
Here is an example of the data:
PAT_NM SER_DT CHG_AMT INV_NUM BAR_B_INV.U_DOS_GREATER_34 5
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
I'm trying to build a custom report which uses SQL. I've created a dervied column called - BAR_B_INV.U_DOS_GREATER_34
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_34
Here is an example of the data:
PAT_NM SER_DT CHG_AMT INV_NUM BAR_B_INV.U_DOS_GREATER_34
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
Which database are you using?
make it:
case when BAR_B_INV.U_DOS_GREATER_34 5='AGE'
then BAR_B_INV.U_DOS_GREATER_34 5'
else ' ' end
case when BAR_B_INV.U_DOS_GREATER_34
then BAR_B_INV.U_DOS_GREATER_34
else ' ' end
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.
ASKER
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_34 5
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_34 5
;
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_34
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_34
;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.