I think it is more complicated than I stated. All 3 or 2 or 1 of the following fields b2.OV_ID1, b2.OV_ID2, and b2.OV_ID3 can be null. There can also be many rows in the basket. The rows in the basket are purchased products and the 3 fields I refer to are possible options (size, color, etc.) for the products.
If one or more of the 3 fields is null, the DBMS doesn't return a matched row. Maybe the best thing for me to do is have a default option that is titled "no option". When an item is added to the basket, I should check for "null" options and set them to the default "no option" before the DBMS search. That way all 3 fields would have a match. These are extra steps I would like to avoid if the search could somehow ignore the null fields. I didn't quit understand Bill's suggestion. If I understand it correctly, I would have to do a loop of some sort and build the conditional search for each row in the basket instead of "viewing" all the rows at one time. I guess I was wondering how the "Incl. Empty" option in the "criteria" of a "search" action could be related to a DBMS. I understand this option (if set to false) to not include a criteria if null. Maybe I don't understand it correctly. Steve Fogelson -----Original Message----- From: Bill Downall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 18, 2003 12:41 PM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: DBMS Action Steve, You can build your complete SQL in variables in a results action, modifying the SELECT list, the FROM list, and the WHERE conditions according to some IF conditions. Of course, you will have to SELECT the ov_id3 column from the appropriate BASKET row first, to set up your if conditions. Then, if you have the SQL you want in @@local $StevesQuery, you can, in your directDBMS action, just put <@VAR local$StevesQuery encoding="none"> Bill On Mon, 17 Feb 2003 23:00:10 -0600, Fogelson, Steve wrote: >In the following DBMS action, b2.OV_ID3 may be null. Is there a way in the >WHERE clause to not include "AND (o5.OV_ID = b2.OV_ID3)" if "b2.OV_ID3 is >null". > >If so, would it also be neccessary to not include "o5.OV_Name as `Option3`" >in the SELECT clause? > >If so, how would I eliminate this? > >Thanks > >Steve Fogelson >Internet Commerce Solutions > >SELECT p1.P_ID, > p1.P_SKU, > p1.P_Part_Number, > p1.P_Name, > p1.P_Price1 as `Price`, > b2.B_Quantity, > b2.B_ItemNumber, > p1.V_ID, > b2.OV_ID1, > o3.OV_Name as `Option1`, > b2.OV_ID2, > o4.OV_Name as `Option2`, > b2.OV_ID3, > o5.OV_Name as `Option3` > > FROM Products p1, Basket b2, OptionValue o3, OptionValue o4, OptionValue o5 > WHERE (b2.B_ShopperID = <@var user$U_ID>) > AND (p1.P_Disable Is Null) > AND (b2.P_ID = p1.P_ID) > AND (o3.OV_ID = b2.OV_ID1) > AND (o4.OV_ID = b2.OV_ID2) > AND (o5.OV_ID = b2.OV_ID3) > ORDER BY b2.B_ItemNumber ASC >_________________________________________________________ _______________ >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
