So here is my dilemma.
This code that I have written produces three options
The first is a VB calender start date and end date for filtering.
The second is a dynamically filled drop down which selects the department or defaults to all departments.
The second is a dynamically filled drop down which defaults to add 'Cells' which is just a two letter code.
To create the default the <All depts> and <All Cells> options I add the first record to the dynamic selection of the records with the <all xxx> row.
At this point a table contains <all depts> and one for all cells followed by all the department codes (or cell codes)
This is then sent to the VB format
Which creates the drop downs.
If the defaults are left selected the report returns everything as expected.
As you can see when I select the department everything works as planned too ...
Here I select BLD as the dept
And it works fine but...
But if I reverse the options and want to find all departments with the cell reference it fails and returns all departments.(I cut this photo short to save space)
Finally if I select a department AND a cell reference it returns the correct result.
If I try to add brackets to force order of precedence visual studio simply automatically removes it with no error message.
I don't understand why it would try to pre-empt me this way and automatically correct my code. I've never had an interpreter or compiler do that to me in any language before.
To explain dbo.wfn_GetSimp leDate is a short date function.
Where the line (PurchaseOrders .DC_001_TXT <> '') appears it is added to correct the fact that these fields do not appear in all records. It's used to strip non modified records.
So the functionality of the WHERE filter is
Get the start and end dates form the user
Filter out non test records
Default of dept selected or changed?
Default of Cell selected or changed?
I have to be doing something really stupid or
this compiler works in a way that i do not fully understand. If I were to create this code in any other language it would work.
This code that I have written produces three options
The first is a VB calender start date and end date for filtering.
The second is a dynamically filled drop down which selects the department or defaults to all departments.
The second is a dynamically filled drop down which defaults to add 'Cells' which is just a two letter code.
To create the default the <All depts> and <All Cells> options I add the first record to the dynamic selection of the records with the <all xxx> row.
At this point a table contains <all depts> and one for all cells followed by all the department codes (or cell codes)
This is then sent to the VB format
Which creates the drop downs.
If the defaults are left selected the report returns everything as expected.
As you can see when I select the department everything works as planned too ...
Here I select BLD as the dept
And it works fine but...
But if I reverse the options and want to find all departments with the cell reference it fails and returns all departments.(I cut this photo short to save space)
Finally if I select a department AND a cell reference it returns the correct result.
If I try to add brackets to force order of precedence visual studio simply automatically removes it with no error message.
I don't understand why it would try to pre-empt me this way and automatically correct my code. I've never had an interpreter or compiler do that to me in any language before.
To explain dbo.wfn_GetSimp leDate is a short date function.
Where the line (PurchaseOrders .DC_001_TXT <> '') appears it is added to correct the fact that these fields do not appear in all records. It's used to strip non modified records.
So the functionality of the WHERE filter is
Get the start and end dates form the user
Filter out non test records
Default of dept selected or changed?
Default of Cell selected or changed?
Code:
SELECT PurchaseOrders.DC_001_TXT AS Department, PurchaseOrders.DC_002_TXT AS Cell, PurchaseOrders.DC_003_TXT AS Reference,
PurchaseOrders.DC_004_TXT AS FixUse, dbo.wfn_GetUDFNvarchar(N'Element', N'PurchaseOrders', PurchaseOrders.PurchaseOrder) AS Element,
Suppliers.SupplierName, Suppliers.SupplierId, PurchaseOrderItems.Quantity, PurchaseOrderItems.ItemDescription, PurchaseOrderItems.ItemValue,
PurchaseOrderItems.Price, PurchaseOrderItems.ItemNumber, PurchaseOrders.PurchaseOrderId
FROM PurchaseOrders INNER JOIN
Suppliers ON PurchaseOrders.Supplier = Suppliers.Supplier INNER JOIN
PurchaseOrderItems ON PurchaseOrders.PurchaseOrder = PurchaseOrderItems.PurchaseOrder
WHERE (dbo.wfn_GetSimpleDate(PurchaseOrders.CreatedDate) BETWEEN @DateStart AND @DateEnd) AND (PurchaseOrders.DC_001_TXT <> '') AND (@Department = ' <All Depts>') OR (PurchaseOrders.DC_001_TXT = @Department)
AND (@Cell = ' <All Cells>') OR (PurchaseOrders.DC_002_TXT = @Cell)
ORDER BY PurchaseOrders.PurchaseOrderId
this compiler works in a way that i do not fully understand. If I were to create this code in any other language it would work.
Comment