SQL OR

Hi Experts,

I have the below query, in which I would like to exclude ALL bookings that meet ANY of the criteria in my WHERE clause.  So, exclude any record that has a null booking date, or null booking time, or null trip type.  All fields do not need to be null to exclude that row.  I need to exclude rows if ANY is null, that's why I'm using OR instead of AND... However, by my jpeg, you'll see that I'm still getting nulls.  Why is this?

SELECT fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, 
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookDate] AS DATE),120) AS [Booking_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookTime] AS TIME),114) AS [Booking_Time], 
	
	   fldAgent, fldBookLoc, fldPickupLoc,
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupDate] AS DATE),120) AS [Pickup_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupTime] AS TIME),114) AS [Pickup_Time],
	   
	   fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment,
	   
	/*QUANTITIES*/
	  	 fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty,
		((fldFamQty * 4) + fldAdultQty + fldSenQty + fldStudQty + fldChildQty) AS [total_pax],

	/*RATES*/
	  	 fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate,
	  	 
	/*COSTS*/
		(fldFamQty		* fldFamRate)	AS [fam_cost],
		(fldAdultQty	* fldAdultRate) AS [ad_cost],
		(fldSenQty		* fldSenRate)	AS [sn_cost],
		(fldStudQty		* fldStudRate)	AS [st_cost],
		(fldChildQty	* fldChildRate) AS [ch_cost],
		(fldInfQty		* fldInfRate)	AS [inf_cost],
		
		(
			(fldFamQty		* fldFamRate)	+
			(fldAdultQty	* fldAdultRate) +
			(fldSenQty		* fldSenRate)	+
			(fldStudQty		* fldStudRate)	+
			(fldChildQty	* fldChildRate) +
			(fldInfQty		* fldInfRate)	
		
		) AS [total_cost]
		
	
FROM tblReservations 
WHERE (fldTripType IS NOT NULL) OR (fldBookTime IS NOT NULL) OR (fldBookTime IS NOT NULL) 
	  OR (fldAgent IS NOT NULL)
ORDER BY fldReservID

Open in new window


ThanksResults
APD TorontoAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
You want to exclude rows where any of the those columns are NULL, so you want to select the rows where all of the columns are not NULL.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
You want to use AND in this filter.  And check to see that you're referencing the correct columns.  One appears twice.

WHERE (fldTripType IS NOT NULL) 
  AND (fldBookTime IS NOT NULL) 
  AND (fldBookTime IS NOT NULL) 
  AND (fldAgent IS NOT NULL)

Open in new window


Good Luck,
Kent
0
 
APD TorontoAuthor Commented:
Wouldn't AND need to meet ALL? So, wouldn't AND exclude rows where ALL are null?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Mark ElySenior Coldfusion DeveloperCommented:
You have fldBookTime in the where clause twice....  Also no need for the () in the where clause as it is written.  

SELECT fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, 
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookDate] AS DATE),120) AS [Booking_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookTime] AS TIME),114) AS [Booking_Time], 
	
	   fldAgent, fldBookLoc, fldPickupLoc,
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupDate] AS DATE),120) AS [Pickup_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupTime] AS TIME),114) AS [Pickup_Time],
	   
	   fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment,
	   
	/*QUANTITIES*/
	  	 fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty,
		((fldFamQty * 4) + fldAdultQty + fldSenQty + fldStudQty + fldChildQty) AS [total_pax],

	/*RATES*/
	  	 fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate,
	  	 
	/*COSTS*/
		(fldFamQty		* fldFamRate)	AS [fam_cost],
		(fldAdultQty	* fldAdultRate) AS [ad_cost],
		(fldSenQty		* fldSenRate)	AS [sn_cost],
		(fldStudQty		* fldStudRate)	AS [st_cost],
		(fldChildQty	* fldChildRate) AS [ch_cost],
		(fldInfQty		* fldInfRate)	AS [inf_cost],
		
		(
			(fldFamQty		* fldFamRate)	+
			(fldAdultQty	* fldAdultRate) +
			(fldSenQty		* fldSenRate)	+
			(fldStudQty		* fldStudRate)	+
			(fldChildQty	* fldChildRate) +
			(fldInfQty		* fldInfRate)	
		
		) AS [total_cost]
		
	
FROM tblReservations 
WHERE fldTripType IS NOT NULL AND
	  fldBookTime IS NOT NULL AND
	  fldAgent IS NOT NULL
ORDER BY fldReservID

Open in new window

0
 
APD TorontoAuthor Commented:
It has been a long day :)
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Yeah.  It's usually not called "Friday" though.   :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.