asked on SQL, Coldfusion - Exclude all rows with specfic value if one of those rows contains...
Hi,
I have a SQL query (below) in which I want to exclude rows based on the following conditions:
If any row with column SCSTULINK= 800 has the value NULL in column SCEXCLATTEND, then exclude ALL rows with SCSTULINK = 800 in results.
Basically, the way I have the SQL query setup now, it's excluding results that have a NULL value in the scexclattend field, but I need it to exclude all results if one of the results fulfills the parameters stated above (which it's not doing now).
Oh, and I'm using an Oracle DB, Coldfusion MX 7.
Thank you very much.
I have a SQL query (below) in which I want to exclude rows based on the following conditions:
If any row with column SCSTULINK= 800 has the value NULL in column SCEXCLATTEND, then exclude ALL rows with SCSTULINK = 800 in results.
Basically, the way I have the SQL query setup now, it's excluding results that have a NULL value in the scexclattend field, but I need it to exclude all results if one of the results fulfills the parameters stated above (which it's not doing now).
Oh, and I'm using an Oracle DB, Coldfusion MX 7.
Thank you very much.
SELECT Distinct
SCTCHNUM,
a.SCSCHOOLNUM,
a.sctchnum,
b.SCEXCLATTEND
c.SCSTULINK
FROM SASI.PS_SCATCH a,
SASI.PS_SCAMST b,
SASI.PS_SCACLS c
WHERE a.SCSCHOOLNUM = 601
AND c.scstulink = 873
AND a.SCSCHOOLNUM = b.SCSCHOOLNUM
AND a.sctchnum = 179
AND a.SCTCHNUM = b.SCTCHNUM
AND b.SCSCHOOLNUM = c.SCSCHOOLNUM
AND b.SCEXCLATTEND is not null
AND c.SCSCHOOLNUM = d.SCSCHOOLNUM
AND c.SCSTULINK = d.SCSTULINK
DATA RESULTS
SCSCHOOLNUM SCSTULINK SCTCHNUM SCEXCLATTEND
601 800 11 Y
601 800 11 NULL
601 780 11 Y
So ALL rows containing SCSTULINK = 800 need to be excluded b/c 1 of the rows contains NULL in the SCEXCLATTEND field
Oracle DatabaseColdFusion LanguageSQL
Last Comment
wkolasa
The results listed by you does not reflect the query accurately. It would help if you list the whole query here as the table declaration for alias d is not included above.
ASKER
My apologies... use this instead:
SELECT Distinct
SCSCHOOLNUM,
SCTCHNUM,
SCEXCLATTEND,
SCSTULINK
FROM SCATCH
WHERE SCSCHOOLNUM = 601
AND scstulink = 873
AND sctchnum = 179
DATA RESULTS
SCSCHOOLNUM SCSTULINK SCTCHNUM SCEXCLATTEND
601 800 11 Y
601 800 11 NULL
601 780 11 Y
So ALL rows containing SCSTULINK = 800 need to be excluded b/c 1 of the rows contains NULL in the SCEXCLATTEND field
ASKER
This is laid out better:
SELECT Distinct
SCSCHOOLNUM,
SCTCHNUM,
SCEXCLATTEND,
SCSTULINK
FROM SCATCH
WHERE SCSCHOOLNUM = 601
AND scstulink = 873
AND sctchnum = 179
DATA RESULTS
SCSCHOOLNUM SCSTULINK SCTCHNUM SCEXCLATTEND
601 800 11 Y
601 800 11 NULL
601 780 11 Y
So ALL rows containing SCSTULINK = 800 need to be excluded b/c 1 of the rows contains NULL in the SCEXCLATTEND field
I think you need a sub-query to first find the values of SCSTULINK that have a NULL in column: SCEXCLATTEND, then exclude those SCSTULINK values from the main query. Or, do you only exclude them if SCSTULINK = 800?
I have no idea though what limitations (or features?) ColdFusion adds to your ability to write SQL statements. Does it allow you (or help you) to include a sub-query if you wish?
I have no idea though what limitations (or features?) ColdFusion adds to your ability to write SQL statements. Does it allow you (or help you) to include a sub-query if you wish?
ASKER
Yes, I can sub-query with CF. It's an interesting idea... I'm going to give it some thought.
To answer other question: No, not only it SCSTULINK = 800. There can be a bunch of rows with the same SCSTULINK, and 0, 1, or more times where that SCSTULINK (let's say it's 800 for example) has the NULL value at SCEXCLATTEND. If that happens, then I need to exclude ALL SCSTULINK that = 800, even if they don't have a NULL value on SCEXCLATTEND
To answer other question: No, not only it SCSTULINK = 800. There can be a bunch of rows with the same SCSTULINK, and 0, 1, or more times where that SCSTULINK (let's say it's 800 for example) has the NULL value at SCEXCLATTEND. If that happens, then I need to exclude ALL SCSTULINK that = 800, even if they don't have a NULL value on SCEXCLATTEND
You can do a query of queries in coldfusion. So using your initial SQL statement to put everything into a recordset. Then check this RS to see if SCSTULINK = 800 and the value is NULL in column SCEXCLATTEND (simple cfif while looping through the original query). If it finds a record which means the IF statement, then break out and do a query of queries to exclude all records with 800 in SCSTULINK .
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I just noticed Tomarse111's suggestions, Yes, that may work in ColdFusion, but from a performance perspective it is *MUCH* better if the database can do the filtering directly and only return the desired records to the application server. Otherwise the DB server, network and application server are burdened with processing lots of rows that will be filtered out later. (I'm assuming that ColdFusion runs on a server other than the DB server, but I realize I'm making a guess about that.)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you everyone. I'm posting a mega-easy one in just about a minute if anyone wants a quick extra 500 points. Thank you again!