I am creating an application which uses logic similar to J.D.Edwards (for those of you are familiar with its wildcarding data structure).
Basically, a customer purchases a particular house, with a particular elevation, in a particular community. This data is stored in a Customer table. From the customer's selection (community, plan, elevation) criteria, I need to create a unique list of options from data stored in an optionmaster table.
A customer's data looks similar to this in the customer table:
CustomerID
CommunityID
Community Name
PlanID
Plan Name
ElevationID
ElevationName
1234567
7
Hickory Hills
25
Allen
3
C
The Optionmaster table is structured like this (there are actually about 1000 records):
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
0
+
0
+
0
+
100
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optional Dormer
0
+
25
Allen
2
B
50
9125
Optional Tub
8
Smithville
0
+
0
+
800
9125
Optional Kitchen
0
+
0
Lori
0
+
2500
First. Based on the customer's table I need to first select all options = to their community, as well as those available to communities everywhere (+). In some cases the same option is maintained for both at both levels. In that case the community specific option must be selected.
To do this I think I need an if else statement to select those records:
In this case, if CommID =7, select record, else select 0. This eliminates the other Smithville community and pulls the correct option # 1234.
The result would look like this:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
9125
Optinal Kitchen
0
+
0
Lori
0
+
2500
Second, from this dataset, I need to select all options equal to the plan ID, and those which apply to all plans (+). So, the Lori Plan data goes away.
Something like, If the plan ID = 25, select record, else 0. the result would be:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
Third and finally, based on that dataset, I would have to select any elevation specific options for that plan or options for all elevations of that plan (+).
Something like this: If the Elevation ID = 2, select record, else. 0. Note the elevation B data goes away.
The final result would be for this customer is:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
Any assistance anyone can provide in coming up with a sql statement to do this would be appreciated.
Something like, If the plan ID = 25, select record, else 0. the result would be:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
why did ooption #4567 stay here. the rest i follow.
|||4567 Stays because it is available in all communites (signified by the +). At the community level, a customer can choose options available (+) AND CommID 7 "Hickory Hills" options. In situations were there is are two options, as in 1234, the community specific one is chosen.
This same pattern goes on 2 more times.
Basically, it starts general, and gets specific.
|||Just one suggestion:?there?is?no?PK?in the Optionmaster table, which is useful for filtering rows in that table.?Not?a?good?table?desing. For performance consideration, I also suggest you add a PK:ALTER TABLE OptionMaster ADD UniqueID INT PRIMARY KEY IDENTITY(1,1)
And for your last step, I can't understand why 9101 goes away, as this row has ElevationID=2, which should be returned according to your rule. Anyways, try some query as following:
DECLARE @.PlanID int,@.ElevationID int
SELECT @.PlanID=25,@.ElevationID=2
SELECT o.*
FROM Optionmaster o ,Customers c
WHERE
(o.CommID=c.CommunityID
OR (o.CommID=0
AND NOT EXISTS (SELECT 1 FROM Optionmaster
WHERE Option#=o.Option#
AND CommID=c.CommunityID)))
AND (o.PlanID=@.PlanID OR (o.PlanID=0 AND o.PlanName ='+'))
AND (o.ElevationID=@.ElevationID OR (o.ElevationID=0 AND o.ElevationName='+'))|||
Thanks for the reply. There is actually a PK but I didn't show it.
Actually, I solved it a few minutes ago.
What I found out was the actual JD Edwards Option Master table has a column with a value (1-9 -- 1 being most specific, 9 being less specific). So all options are stored in 1 of 9 ways: (this is how it can be structured in their system).
So, basically, I when I run a query on all the following data:
Where the community = "Bed0000', Phase = "Two", Plan = "Shilo" and Elevation ="A", I get 4 records
I created a demo form to input the parameters. Then, I created a query to first select the relevant option data according to JDE's 9 levels. This gave me all the possible records. Then, I filtered the results based on the MIN value of Level (grouped on the option #). This gave me the specific records.
There's probably a better way of doing this, but I can't figure one out. I'd like to have it as a stored procedure but whenever I tried I kept running into problems declaring the variables. I don't know how much that will impact performance. I know it would be better to run on the server...I'll figure it out eventually, I'm still a bit new to SQL2005.
Here's what it looks like.
<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb"Inherits="_Default" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="DSSpecificOptionData" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT F44H401.ocoplvl, F44H401.ochbmcus, F44H401.occphase, F44H401.ochbplan, F44H401.ochbelev, F44H401.ocooption, F44H401.ocdlo1, F44H401.occspr FROM F44H401 INNER JOIN [Find_Lowest_Level] ON F44H401.ocoplvl = [Find_Lowest_Level].[Level] AND F44H401.ocooption = [Find_Lowest_Level].ocooption WHERE (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev ='+') AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+')"> <SelectParameters> <asp:ControlParameter ControlID="TBComm" Name="ochbmcus" PropertyName="Text" /> <asp:ControlParameter ControlID="TBPlan" Name="ochbplan" PropertyName="Text" /> <asp:ControlParameter ControlID="TBElev" Name="ochbelev" PropertyName="Text" /> <asp:ControlParameter ControlID="TBPhas" Name="occphase" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource> <br /> <table style="position: static"> <tr> <td style="width: 100px"> Community "bed0000"</td> <td style="width: 100px"> <asp:TextBox ID="TBComm" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Phase "two"</td> <td style="width: 100px"> <asp:TextBox ID="TBPhas" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Plan "shilo"</td> <td style="width: 100px"> <asp:TextBox ID="TBPlan" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Elev "a"</td> <td style="width: 100px"> <asp:TextBox ID="TBElev" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> <asp:Button ID="Button1" runat="server" Style="position: static" Text="Search" /></td> <td style="width: 100px"> </td> </tr> </table> <br /> <span style="color: #0000ff">Results with most specific records:</span><br /> <asp:DataList ID="DataList1" runat="server" DataSourceID="DSSpecificOptionData" Style="position: static"> <ItemTemplate> <table style="position: static"> <tr> <td style="width: 100px"> <asp:Label ID="ocoplvlLabel" runat="server" Style="position: static" Text='<%# Eval("ocoplvl") %>' Width="19px"></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbmcusLabel" runat="server" Style="position: static" Text='<%# Eval("ochbmcus") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="occphaseLabel" runat="server" Style="position: static" Text='<%# Eval("occphase") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbplanLabel" runat="server" Style="position: static" Text='<%# Eval("ochbplan") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbelevLabel" runat="server" Style="position: static" Text='<%# Eval("ochbelev") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ocooptionLabel" runat="server" Style="position: static" Text='<%# Eval("ocooption") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ocdlo1Label" runat="server" Style="position: static" Text='<%# Eval("ocdlo1") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="occsprLabel" runat="server" Style="position: static" Text='<%# Eval("occspr") %>'></asp:Label></td> </tr> </table> </ItemTemplate> <HeaderTemplate> <table style="position: static"> <tr> <td style="width: 100px"> ocoplvl: </td> <td style="width: 100px"> ochbmcus:</td> <td style="width: 100px"> occphase:</td> <td style="width: 100px"> ochbplan: </td> <td style="width: 100px"> ochbelev:</td> <td style="width: 100px"> ocooption:</td> <td style="width: 100px"> ocdlo1:</td> <td style="width: 100px"> occspr:</td> </tr> </table> </HeaderTemplate> </asp:DataList></div> <span style="color: #0000ff"><br /> </span> <br /> </form></body></html> |||Oh that's great, I'm glad to see you've solved the problem. Really thank you very much for sharing your experience with us!Cheers,sql
No comments:
Post a Comment