iif statements... then the WHERE clause? help!

[kella101]

Hi,
here is my query which runs within the VB code...

queryString2 = ""
queryString2 = queryString2 & " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No],"
queryString2 = queryString2 & " Custom2.Customer_Name AS [Customer Name],"
queryString2 = queryString2 & " part.id AS Part,"
queryString2 = queryString2 & " price_factor.use_fixed_price AS [Use_fixed],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.price,price_list.price) AS [Base Price],"
queryString2 = queryString2 & " iif(Use_fixed =1,price_factor.fixed_price_factor,price_factor.factor) AS [Factor],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.min_quantity,price_list.min_quantity) AS [Price Break],"
queryString2 = queryString2 & " part_select.quantity AS [Quantity Required],"
queryString2 = queryString2 & " part.unit_id As [Unit],"
queryString2 = queryString2 & " iif(Use_fixed =1"
queryString2 = queryString2 & " ,(price_factor.fixed_price_factor*(fixed_price_list.price*FixedCurrencyConUKP.sterling_exchange_rate))"
queryString2 = queryString2 & " ,(price_factor.factor *(price_list.price*CurrencyConUKP.sterling_exchange_rate))) AS Price"
queryString2 = queryString2 & " FROM part_select, part, fixed_price_list, price_list, price_factor, Custom2, CurrencyConUKP, FixedCurrencyConUKP"
queryString2 = queryString2 & " WHERE part.id = part_select.part"
queryString2 = queryString2 & " AND price_list.part_id = part.id"
queryString2 = queryString2 & " AND fixed_price_list.part_id = part.id"
queryString2 = queryString2 & " AND Custom2.JDE_Price_Group=price_factor.customer_group"
queryString2 = queryString2 & " AND price_factor.part_group=part." & PriceGroup
queryString2 = queryString2 & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'"
queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND part.id = " & "'" & select_part_rst!part & "'"
queryString2 = queryString2 & " AND part_select.quantity = " & select_part_rst!quantity
queryString2 = queryString2 & " ;"

It all works pretty much fine, however, the problem occurs because the iif statement tells the code to use either the fixed_price_list table or the price_list table depending on the value of Use_fixed. the iif works fine, its the WHERE clause. because the iif statements tell the query to look at one table or the other, the WHERE clause includes statements from both tables e.g.

queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "

but some parts dont have information within the fixed_price_list table, and thus are not being found in the results due to the WHERE clause. can anyone help?? i tried using a left join but it didnt work how i wanted. in a perfect world i would be able to include a iif statement in the where clause e.g.

queryString2 = queryString2 & " iif Use_fixed =1, AND price_list.min_quantity <= part_select.quantity, AND fixed_price_list.min_quantity <= part_select.quantity) "

can anyone help?

 

[jzwp22]

It appears that you have two separate tables with part data. All part data should be in 1 table. You can add a field that identifies whether the part has a fixed price or not. Your query should become easier to write at that point. The other option is to combine the part data using a separate UNION query and then use that in the query you are trying to create.

 

[kella101]

Hi, i already have the field which defines if it is fixed or not. it is the where clause which won't work properly. even if all the information was in the same table it wouldnt work correctly, as the where clause would still need to look at either one column or the other column depending on if it was fixed or not.

 

[jzwp22]

I guess I really meant to ask why you don't have the prices all in 1 table. You have 2 separate tables for prices: price_list and fixed_price_list. If you could provide your table structure or a zipped copy of the DB with any sensitive data removed, we might be better able to see how things are related and then maybe we can resolve the issue.

 

[kella101]

Hi, i cant send you the data, they come from various ODBC databases and else where. and they are in seperate tables because of the complexity of what gets done to the information. e.g. a part number gets referenced and a customer gets referenced, each of these have codes, then these codes are cross referenced in another table, and from here you get either it needs to use fixed or variable pricing, from this it also gets the factor which is required. then if uses either the fixed or variable pricing tables and gets the price and multiplies it by the factor from the previous. this is just a small part of it.

Also even if the information was all in one table, the where clause would still need to be on either one column or another column dependin on the result of the iif statements, therefore would b in the exact same situation.

 

[jzwp22]

I tried to reconstruct your table structure based on the fields and tables you have listed in your query. I am guessing, but I think you need to nest some subqueries together in order to get this query to work. Without knowing the details of the structure and the relationships, I cannot be of much help.