Now though I am doing this in sql, this will impact how I get the answers in webfocus. I will explain this the best way I know how:

Here are 2 tables:

  
SPRIDEN: (EACH PIDM HAS ONLY ONE ENTRY IN TABLE)

SPRIDEN_PIDM     SPRIDEN_ID          SPRIDEN_LAST_NAME    SPRIDEN_FIRST_NAME     SPRIDEN_CHANGE_IND
0000             123456789           DOE                  JOHN
0001             123456788           DOE                  JANE
0002             123456787           DOE                  JIMMY

RPRAWRD: (EACH PIDM CAN HAVE MULTIPLE ENTRIES IN TABLE)

RPRAWRD_PIDM     RPRAWRD_FUND_CODE   RPRAWRD_ACCEPT_AMT   RPRAWRD_PAID_AMT  RPRAWRD_AID_YEAR
0000             FPELLG              1250                 750               20062007
0000             FSUBST              2000                 2000              20062007
0000             FPERKI              3000                 2500              20062007
0000             FUNSUB              6000                 0                 20062007
0001             FPELLG              1250                 1250              20062007
0001             FSUBST              2500                 2500              20062007
0002             FPELLG              1250                 750               20062007
0002             FSUBST              2000                 2000              20062007
0002             FPERKI              3000                 2500              20062007

GOAL:
What I am trying to do in SQL is pull the people who don't have the 'FPERKI' FUND_CODE (I want their id, last_name, first_name).

MY CODE:

SELECT DISTINCT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME
FROM SPRIDEN, RPRAWRD
WHERE SPRIDEN_CHANGE_IND IS NULL
AND SPRIDEN_PIDM = RPRAWRD_PIDM
AND RPRAWRD_PAID_AMT > 0
AND RPRAWRD_FUND_CODE <> 'FPERKI'
AND RPRAWRD_AID_YEAR = '20062007'
ORDER BY SPRIDEN_ID

MY PROBLEM:
When I run this report, I still get people who have the FPERKI fund_code. If I run it the opposite way (by changing the '<>' to '=' in the 6th line of code), it only gets the people with FPERKI. But when I run it with '<>' instead of '=', I still get people with the FPERKI. NOTE: THE PEOPLE RETURNED WITH FPERKI ALSO HAVE OTHER FUND_CODES. Now, i know that if I were selecting the field RPRAWRD_FUND_CODE (with their id and name fields), then it should give me a line by line list and only exclude the fund_codes with FPERKI, regardless of the ID, last or first name, so that someone with multiple fund_codes along with FPERKI should still show up.....HOWEVER, I am only selecting their id and name information, in essence doing a group by (if you will). So even if it has multiple fund_codes as well as FPERKI, it should eleminate anyone with FPERKI.

I don't get this and it baffles me to no end. Am I wrong about this?

Hi Tom.

Using NOT doesn't work for me. It gives me an error:

ORA-00920: invalid relational operator


I should have mentioned this before, but we use Oracle here. Maybe that is why.

Brian,

There is a lot of danger in using implicit JOINS in SQL. We have many documented instances on multiple SQL RDBMS platforms that give less than accurate results. We always use Explicit Joins;

SELECT DISTINCT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME
FROM SPRIDEN
INNER JOIN RPRAWRD
ON 
SPRIDEN_PIDM = RPRAWRD_PIDM
WHERE SPRIDEN_CHANGE_IND IS NULL
AND RPRAWRD_PAID_AMT > 0
AND RPRAWRD_FUND_CODE NOT IN ( 'FPERKI' )
AND RPRAWRD_AID_YEAR = '20062007'
ORDER BY SPRIDEN_ID  

Hope this helps,

Kevin

Hi Kevin.

This didn't work either...I tried your code, but it still returns people who have the 'FPERKI' fund_code. In fact, it returns the same number of results that my previous example returned.

I mean, either way looks like it should work correctly based on the select fields....but it still puzzles me.

Brian,

IDs 0000 and 0002 also have records that do not have FPERKI, so your statement will retrieve those records. In your SELECT you will get the Information for those individuals based on those records. Rather than Joining the tables, perhaps use a sub-select.

SELECT DISTINCT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME
FROM SPRIDEN
, RPRAWRD
WHERE SPRIDEN_CHANGE_IND IS NULL
AND SPRIDEN_ID NOT IN (SELECT RPRAWRD_PIDM AS SPRIDEN_ID
                       FROM RPRAWRD
                       WHERE
                       RPRAWRD_PAID_AMT > 0
                       AND RPRAWRD_FUND_CODE = 'FPERKI'
                       AND RPRAWRD_AID_YEAR = '20062007')
ORDER BY SPRIDEN_ID

Keep me posted,

Kevin

Hi Kevin.

I am certain that your code that you put out will work....as I have done that already. I guess my point is in understanding why the previous code doesn't work properly, when it should. You say my statement will retrieve those records from 0000 and 0002 that have other fund_codes, however I am not sure that is correct. Let me explain with another visual.

If my select statement looked like this:

SELECT DISTINCT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, RPRAWRD_FUND_CODE <<<----ADDED FUND_CODE
FROM SPRIDEN, RPRAWRD
WHERE SPRIDEN_CHANGE_IND IS NULL
AND SPRIDEN_PIDM = RPRAWRD_PIDM
AND RPRAWRD_PAID_AMT > 0
AND RPRAWRD_FUND_CODE <> 'FPERKI'
AND RPRAWRD_AID_YEAR = '20062007'
ORDER BY SPRIDEN_ID

Then I would expect my OUTPUT to look like this:

SPRIDEN_ID          SPRIDEN_LAST_NAME    SPRIDEN_FIRST_NAME     RPRAWRD_FUND_CODE
123456789           DOE                  JOHN                   FPELLG
123456789           DOE                  JOHN                   FSUBST
123456788           DOE                  JANE                   FPELLG
123456788           DOE                  JANE                   FSUBST
123456787           DOE                  JIMMY                  FPELLG
123456787           DOE                  JIMMY                  FSUBST

HOWEVER, If I just remove the RPRAWRD_FUND_CODE out of the selection criteria on the first line of my code, then I would expect my results to look like this:

SPRIDEN_ID          SPRIDEN_LAST_NAME    SPRIDEN_FIRST_NAME     
123456788           DOE                  JANE

BECAUSE Jane is the only one who doesn't have the FPERKI in her fund_codes and I am not listing the fund_codes in the report.

Do you see why I am puzzled? Or am I completely wrong about what the output should look like?

If you remove the RPRAWRD_FUND_CODE out of the selection criteria on the first line of code, then you should expect the results to look like this:


SPRIDEN_ID SPRIDEN_LAST_NAME SPRIDEN_FIRST_NAME
123456789 DOE JOHN
123456788 DOE JANE
123456787 DOE JIMMY

The select distinct is by row. John and Jimmy had rows without a RPRAWRD_FUND_CODE of "FPERKI". So even though you excluded the rows with RPRAWRD_FUND_CODE of "FPERKI", they will show up in the results.

Does this have to be done with SQL code? There is a way to do this in WebFOCUS code.

If you don't want to use SQL this is one of the areas where I have fun, student financial aid. You can pull two hold files and use match or use a join and eliminate records that way with selection. I have the opposite needs recently, Do they have this fund and also this fund or this fund.

To be honest I don't use SQL passthrough much at all.

quote:

Originally posted by Glenda:
If you remove the RPRAWRD_FUND_CODE out of the selection criteria on the first line of code, then you should expect the results to look like this:

SPRIDEN_ID          SPRIDEN_LAST_NAME    SPRIDEN_FIRST_NAME     
123456789           DOE                  JOHN  
123456788           DOE                  JANE
123456787           DOE                  JIMMY

The select distinct is by row. John and Jimmy had rows without a RPRAWRD_FUND_CODE of "FPERKI". So even though you excluded the rows with RPRAWRD_FUND_CODE of "FPERKI", they will show up in the results.

Does this have to be done with SQL code? There is a way to do this in WebFOCUS code.

So if what you say is true (the by row part), then why would the reverse work as I say this should work:

THIS CODE:

SELECT DISTINCT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME
FROM SPRIDEN, RPRAWRD
WHERE SPRIDEN_CHANGE_IND IS NULL
AND SPRIDEN_PIDM = RPRAWRD_PIDM
AND RPRAWRD_PAID_AMT > 0
AND RPRAWRD_FUND_CODE = 'FPERKI'  <<<<< I CHANGED THIS TO "="
AND RPRAWRD_AID_YEAR = '20062007'
ORDER BY SPRIDEN_ID

RETURNS THIS OUTPUT:

SPRIDEN_ID          SPRIDEN_LAST_NAME    SPRIDEN_FIRST_NAME     
123456789           DOE                  JOHN  
123456787           DOE                  JIMMY

Thus by changing the "=" to "<>" should return the following:

SPRIDEN_ID          SPRIDEN_LAST_NAME    SPRIDEN_FIRST_NAME     
123456788           DOE                  JANE  

To me they are just opposites, and should follow the same logic. But what you are saying is that they don't follow the same logic as I have shown above?

Leah,

My intent isn't to use SQL passthrough. I normally think in SQL, and then transpose it to Webfocus code. I kinda see Webfocus as a powerful front end to SQL since it uses SQL in the back end. I know it can do many things that SQL can't do, but it is easier for me to think in SQL and then make it work in Webfocus code.

Brian,

KevinG's solution for the SQL statement looks the best for an SQL output. Which in the WebFOCUS world would mean using two hold files and match logic to accomplish the same thing.

Good luck.

Brian,

In SQL with a one to many relationship involved, = is not necessarily the opposite of <>. As Glenda says, SQL Joins occur on a row by row basis, then your comparison occurs. That's why the subselct works...you are identifying the rows to eliminate without joining 2 tables in a one to many relationship, then you are eliminating those rows from the main select that also appear in the sub-select.

kevin

See if this code works.

JOIN CLEAR *

JOIN
SPRIDEN_PIDM IN SPRIDEN TO ALL RPRAWRD_PIDM IN RPRAWRD AS J1
END

DEFINE FILE SPRIDEN
FUND/I1 = DECODE RPRAWRD_FUND_CODE(FPERKI 1
ELSE 2);
TABLE FILE SPRIDEN
PRINT SPRIDEN_ID
SPRIDEN_LAST_NAME
SPRIDEN_FIRST_NAME
RPRAWRD_FUND_CODE
COMPUTE PULL/A1 = IF SPRIDEN_ID NE LAST SPRIDEN_ID AND FUND_CODE NE 1 THEN 'Y' ELSE
IF SPRIDEN_ID NE LAST SPRIDEN_ID AND FUND_CODE EQ 2 THEN 'N' ELSE LAST PULL;
BY SPRIDEN_ID NOPRINT
BY FUND NOPRINT
WHERE RPRAWRD_PAID_AMT GT 0
WHERE RPRAWRD_AID_YEAR EQ '20062007'
WHERE TOTAL PULL EQ 'Y'
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

quote:

JOIN CLEAR *

JOIN
SPRIDEN_PIDM IN SPRIDEN TO ALL RPRAWRD_PIDM IN RPRAWRD AS J1
END

DEFINE FILE SPRIDEN
FUND/I1 = DECODE RPRAWRD_FUND_CODE(FPERKI 1
ELSE 2);
TABLE FILE SPRIDEN
PRINT SPRIDEN_ID
SPRIDEN_LAST_NAME
SPRIDEN_FIRST_NAME
RPRAWRD_FUND_CODE
COMPUTE PULL/A1 = IF SPRIDEN_ID NE LAST SPRIDEN_ID AND FUND_CODE NE 1 THEN 'Y' ELSE
IF SPRIDEN_ID NE LAST SPRIDEN_ID AND FUND_CODE EQ 2 THEN 'N' ELSE LAST PULL;
BY SPRIDEN_ID NOPRINT
BY FUND NOPRINT
WHERE RPRAWRD_PAID_AMT GT 0
WHERE RPRAWRD_AID_YEAR EQ '20062007'
WHERE TOTAL PULL EQ 'Y'
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

JOIN CLEAR *

JOIN
SPRIDEN_PIDM IN SPRIDEN TO ALL RPRAWRD_PIDM IN RPRAWRD AS J1
END

DEFINE FILE SPRIDEN
FUND/I1 = DECODE RPRAWRD_FUND_CODE(FPERKI 1
ELSE 2);
END <====== Left this out.
TABLE FILE SPRIDEN
PRINT SPRIDEN_ID
SPRIDEN_LAST_NAME
SPRIDEN_FIRST_NAME
RPRAWRD_FUND_CODE
COMPUTE PULL/A1 = IF SPRIDEN_ID NE LAST SPRIDEN_ID AND FUND_CODE NE 1 THEN 'Y' ELSE
IF SPRIDEN_ID NE LAST SPRIDEN_ID AND FUND_CODE EQ 2 THEN 'N' ELSE LAST PULL;
BY SPRIDEN_ID NOPRINT
BY FUND NOPRINT
WHERE RPRAWRD_PAID_AMT GT 0
WHERE RPRAWRD_AID_YEAR EQ '20062007'
WHERE TOTAL PULL EQ 'Y'
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

Glenda your code works well. I like the decode. I don't use many of those.

KevinG
I think I understand now. They seemed like opposites to me...and in reality, to get the numbers I am looking for, I would probably use matching kinda like what you have created...or maybe a modification of Glenda's code.

Brian

Brian,

Absolutely, you could use MATCH logic, or Glendas FOCUS code. It depends on where you have the most power/resources. We do SQL passthru almost exclusively because of the efficiency. Bringing all the results from the SQL tables back into WebFOCUS for additional processing very rarely gives the best performance or utilization of resources. We believe in letting SQL and WF do what each is best at. RDBMS are great at crunching data and delivering result sets, but are very weak in presenting those results. Also, with SQL passthru we have greater control of what is being sent to the RDBMS. FOCUS generated SQL is rather generic in nature. We use WF to dynamically build SQL statements based on user/interface inputs and to format for presentation the SQL query results. By using both products in tandem, you can get the best of both worlds.

Kevin

That's great! And suprising....but I guess since it's Banner Finance, it's not soo suprising. I am one of the few people really using webfocus outside of banner finance.

Thanks for the contact info.

Again, I'm liking Glenda's track... but I'd maybe try this if you just want to list the ids/names of people who don't have that particular fund:

JOIN CLEAR *

JOIN
SPRIDEN_PIDM IN SPRIDEN TO ALL RPRAWRD_PIDM IN RPRAWRD AS J1
END

DEFINE FILE SPRIDEN
FUND_COUNT/I1 = IF RPRAWRD_FUND_CODE EQ 'FPERKI' THEN 1 ELSE 0;
END

TABLE FILE SPRIDEN
SUM FUND_COUNT

BY SPRIDEN_ID
BY SPRIDEN_LAST_NAME
BY SPRIDEN_FIRST_NAME

-* Anyone who has a count of 1 has FPERKI so extract only
-* the others
WHERE TOTAL FUND_COUNT NE 1 ;

WHERE RPRAWRD_PAID_AMT GT 0 ;
WHERE RPRAWRD_AID_YEAR EQ '20062007';

ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

Use the IF-THEN-ELSE instead of the DECODE because it MAY be translated to SQL (The R7 iWay Data Adpater manual has a couple of translation examples with IF-THEN-ELSE).