Views: 4.9K
Replies: 1
Archived
|
SQL Join, Union, MergeHi there,
I'm searching for a smart way to fill a view with values from a table but I cannot find the correct JOIN or UNION :-( I know the exact outcome but I cannot figure how to write this efficiently. --- I wrote a Table for some machine-settings: CREATE TABLE "Settings" ("ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "PosSollSt3" INTEGER NOT NULL , "PosSollSt3Vk" INTEGER NOT NULL , "PosKurzSt3" INTEGER NOT NULL , "PosKurzSt3Vk" INTEGER NOT NULL) Depending on the article proportions I want the calculate the positions where the machine needs to go. These Positions can be calculated using some properties of the article. My Idea was to add a Table where I can insert the corresponing formulas. CREATE TABLE "Formel" ("ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "Text" TEXT NOT NULL); INSERT INTO "Formel" VALUES(1,'(@1+@1)*0.99'); INSERT INTO "Formel" VALUES(2,'(@1+@2)*0.99'); INSERT INTO "Formel" VALUES(3,'(@1+@3)*0.99'); INSERT INTO "Formel" VALUES(4,'(@1+@4)*0.99'); INSERT INTO "Formel" VALUES(5,'(@1+@5)*0.99'); ... since I only have three types of articles, I inserted the links to the formulas into the table: INSERT INTO "Settings" VALUES(1, 1, 2, 3, 4); INSERT INTO "Settings" VALUES(2, 5, 6, 7, 8); INSERT INTO "Settings" VALUES(3, 9,10,11,12); so beginning at Index 4, the INTs are used for the calculated positions of real articles. I can use C# to read line by line and value by value and replace the numbers with the texts of he formulas. Outcome: "PosSollSt3" , "PosSollSt3Vk" , "PosKurzSt3" , "PosKurzSt3Vk" '(@1+@1)*0.99', '(@1+@2)*0.99', '(@1+@3)*0.99', '(@1+@4)*0.99' '(@1+@5)*0.99', '(@1+@6)*0.99', '(@1+@7)*0.99', '(@1+@8)*0.99' '(@1+@9)*0.99', '(@1+@10)*0.99', '(@1+@11)*0.99', '(@1+@12)*0.99' --- What select would you use to create a view? select "Formel.Text", "Formel.Text", "Formel.Text", "Formel.Text", from settings "JOIN each Number" ON settings.field = Formel.ID --- I tried all kinds of methods but nothing leads to a working result :-( Hope someone can help Thanks Collin Collin Sale, Jul 07, 2016
|
|
Reply 1Hello,
I suppose you have found your solution by that time. There are at least 2 ways to do that. One is using PIVOT, but I will suggest a simpler way - multiple JOIN. In Settings you have in one record 4 different references to Formulas - right? Then you need to JOIN the Formula for each key, so 4 times, only with different alias: select FPosSollSt3.Text, FPosSollSt3Vk.Text, FPosKurzSt3.Text, FPosKurzSt3Vk.Text from Settings S JOIN Formel as FPosSollSt3 ON S.PosSollSt3 = FPosSollSt3.ID JOIN Formel as FPosSollSt3Vk ON S.PosSollSt3Vk = FPosSollSt3Vk.ID JOIN Formel as FPosKurzSt3 ON S.PosKurzSt3 = FPosKurzSt3.ID JOIN Formel as FPosKurzSt3Vk ON S.PosKurzSt3Vk = FPosKurzSt3Vk.ID MP Maciej Puchalski, Oct 06, 2016
|