Hi All:

I am trying to use 4 columns that pull data of various diagnosis codes. Before I had the limit to 4 different kinds of diagnosis codes. Now I need to show each column with information from only 1 diagnosis code per column. For instance I need diagnosis code "V08" for each column for the calendar year of 2007. If I do just one column at a time I get data. When I pull from all 4 at the same time. No data. How can I word this to show the data needed from each column?



TABLE FILE OI_CHARGES
PRINT
'MPI.MPI.DISPLAY_NM'
'OI_CHARGES.OI_CHARGES.BILLED_AMT'
'OI_CHARGES.OI_CHARGES.OPEN_AMT'
'MPI_CURRENT_ADDR.MPI_CURRENT_ADDR.ADDRESS_1'
'MPI_CURRENT_ADDR.MPI_CURRENT_ADDR.ADDRESS_2'
'MPI_CURRENT_ADDR.MPI_CURRENT_ADDR.CITY'
'MPI_CURRENT_ADDR.MPI_CURRENT_ADDR.STATE_CD'
'MPI_CURRENT_ADDR.MPI_CURRENT_ADDR.ZIP_CD'
'MPI_CURRENT_ADDR.MPI_CURRENT_ADDR.PHN_NBR'
'OI_CHARGES.OI_CHARGES.SERV_LOCATION'
'OI_CHARGES.OI_CHARGES.AGC_CD'
'OI_CHARGES.OI_CHARGES.PAT_MPI_NBR'
'OI_CHARGES.OI_CHARGES.PAT_NBR'
'OI_CHARGES.OI_CHARGES.DT_OF_SERVICE'
'MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAGNOSIS_CODE'
'MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAGNOSIS_DESC'
'MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_2'
'MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_3'
'MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_4'
'MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.MISC_TEXT'
'MPI_INSURANCE.MPI_INSURANCE.CARRIER_NBR'
'ER_CARR_ASSIGN.ER_CARR_ASSIGN.CARRIER_ID_DESC'

WHERE MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAGNOSIS_CODE EQ 'V08' AND
MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_2 EQ 'V08' AND
MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_3 EQ 'V08' AND
MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_4 EQ 'V08';

WHERE OI_CHARGES.OI_CHARGES.SERV_LOCATION EQ 'FC';
WHERE ( OI_CHARGES.OI_CHARGES.DT_OF_SERVICE GE 20070101 ) AND ( OI_CHARGES.OI_CHARGES.DT_OF_SERVICE LE 20071231 );
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

Logle,

Are you sure you want an AND here and not an OR?

WHERE MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAGNOSIS_CODE EQ 'V08' AND
MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_2 EQ 'V08' AND
MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_3 EQ 'V08' AND
MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_4 EQ 'V08';

versus

WHERE (MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAGNOSIS_CODE EQ 'V08') OR
(MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_2 EQ 'V08') OR
(MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_3 EQ 'V08') OR
(MPI_PATIENT_VISIT.MPI_PATIENT_VISIT.DIAG_CODE_4 EQ 'V08');

Fernando

I actually tried the "Or" as well and it does pull it but then it allows data to fill in the other columns where "VO8" shows up but; For instance if Column One had a V08 diagnosi code on one date but in col 3-4 on the same date they had diff diagnosis codes applied that data is showing to. I want the data to show only if the diagnosis code is V08. I guess a statement that would say if V08 then true else blank how can I do this?? or can I?

I am thinking since the columns all contain diagnosis codes from various dates my best bet is to do the report export and edit it down outside of web focus in excel. Otherwise the columns will conflict with one another since on any given date they can contain diff diagnosis codes for the same date.

Not necessary to use excel. You already came up with the solution - just a matter of getting the syntax. DEFINE is what you're looking for.

DEFINE COLUMN1/format= IF DIAGNOSIS_CODE EQ 'V08' THEN DIAGNOSIS_CODE ELSE
IF DIAG_CODE_2 EQ 'V08' THEN DIAG_CODE_2 ELSE
IF DIAG_CODE_3 EQ 'V08' THEN DIAG_CODE_3 ELSE
IF DIAG_CODE_4 EQ 'V08' THEN DIAG_CODE_4 ELSE ' ';

This way, if the code exists in any of the 4 fields, it is moved into the new field COLUMN1. Otherwise, it's blank.

then if you include the WHERE phrase suggested by Fernando, you only get those records where V08 exists in one of those fields. You could also say WHERE COLUMN1 NE ' ', but that is not very efficient because you would be selecting on a defined field instead of a real field in the database.

I added the Define field as you said. I also preceded it with my Define File name. I tried changeing the Define Column1/format to GenDiagnosis/A8 in addition to running it as you put it. My new column does not show up.

I am new to this so I know that in Column1 this represents my field name and format is the web focus format (which is confusing to me) the actual field for Diagnosis code is a Char (10);

Can you tell me exactly how to put this in my code and then in the Print section don't I need to put my new field name in? I have tried several different ways and either I get errors or the report prints with no new field. Thanks for the help!

DEFINE FILE OI_CHARGES

DEFINE COLUMN1/format= IF DIAGNOSIS_CODE EQ 'V08' THEN DIAGNOSIS_CODE ELSE
IF DIAG_CODE_2 EQ 'V08' THEN DIAG_CODE_2 ELSE
IF DIAG_CODE_3 EQ 'V08' THEN DIAG_CODE_3 ELSE
IF DIAG_CODE_4 EQ 'V08' THEN DIAG_CODE_4 ELSE ' ';

END

Okay I did it!! I used this code and it added it to my report! I'm so happy! My format needed to reflect the same number as the data in the database instead of using A8 (which I saw on other examples) I needed to use A10 which is what my data field uses! Thanks!!!!

DEFINE FILE OI_CHARGES

COMDIAGNOSIS/A10 = IF DIAGNOSIS_CODE EQ 'V08' THEN DIAGNOSIS_CODE ELSE
IF DIAG_CODE_2 EQ 'V08' THEN DIAG_CODE_2 ELSE
IF DIAG_CODE_3 EQ 'V08' THEN DIAG_CODE_3 ELSE
IF DIAG_CODE_4 EQ 'V08' THEN DIAG_CODE_4 ELSE ' ';



END

Good job! And because you figured it out yourself, you're probably going to remember it better than if someone has just given you the code to plug in. And thus, we learn...