Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
ROW_NUMBER |
Sat, Apr 10 2021 12:05 PM | Permanent Link |
Anthony | I was trying to use the TMS Software KanBanDatabaseAdaptor but it requires the column to be presented an integer while the category field in my database is stored as a string datatype.
I found https://www.sqltutorial.org/sql-window-functions/sql-row_number/ but cant find it implemented in EDB I was then thinking of generating the unique integer for each unique category and then performing a join to feed into the KanBanBoard Is there an alternative solution to on the fly convert item1 categoryA item2 categoryA item3 categoryB item4 categoryD to item1 1 item2 1 item3 2 item4 3 |
Sun, Apr 11 2021 2:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Anthony
You could use a COMPUTED or GENERATED column. I would regard COMPUTED as on-the-fly but GENERATED is "real" data. Roy Lambert |
Sun, Apr 11 2021 3:45 AM | Permanent Link |
Anthony | Thanks for the reply Roy
The output would be from a query filtering an existing table which I have modified the example below. Although I assume I could insert into a memory table? item1 categoryA true item2 categoryA true item3 categoryB true item4 categoryC false item5 categoryD true to item1 1 item2 1 item3 2 item5 3 |
Sun, Apr 11 2021 4:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Anthony
It doesn't matter if its a query or not. COMPUTED & GENERATED columns behave almost as any other column - think of them as SQL's equivalent to Delphi's Calculated type as set in the OnCalcFields event. When creating the table simply specify wether is computed or generated and enter the necessary sql to do the work. As an example in one of my apps I have a phone number and I have a computed column for the STD code. The SQL to extract the STD code from the company's switchboard phone number is SUBSTR(_Switchboard,1,POS(' ' IN _Switchboard)-1) and the column is set up as "_cSTDCode" VARCHAR(5) COLLATE "ANSI_CI" COMPUTED ALWAYS AS SUBSTR(_Switchboard,1,POS(' ' IN _Switchboard)-1) This means I can use a standard Delphi table filter or SQL to extract all companies who's switchboard is say '01482' For you a guess at the sql would be CASE WHEN fld = 'categoryA' THEN 1 WHEN fld = 'categoryA' THEN 2 WHEN fld = 'categoryC THEN 3 WHEN fld = 'categoryD' THEN 4 ELSE 0 END Roy Lambert |
Sun, Apr 11 2021 7:12 AM | Permanent Link |
Anthony | I appreciate your feedback Roy, I not sure I explained the original problem clearly.
I have an table where the category can be added/renamed by the end user and trying to display this on the TMS KanBan boards in a column for each category. Only active activities need to be displayed and the "category" column needs a sequential index integer 0 biased without gaps so if I create a memory table and insert the results into that with a generated category column that might be a solution, i'll give it a try. Also, your example of telephone numbers I have a similar application but do not perform lookups on the STD code although something that I might look into but with the increase in VOIP systems i'm finding that the 44 prefix to the inbound caller ID is preventing number matching so you examples have give some possible solutions to that issue also. Thanks again Anthony |
Sun, Apr 11 2021 8:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Anthony
I'm probably still not understanding properly. I'm not sure your example is helping me You could try select runsum(1) -1, * from table group by __rowid This will give you a row counter starting with 0 BUT it will not do what you seem to want in your example, however it does seem to do what I think SQL ROW_NUMBER is doing. You might get away with select __rowid-1, * from table or may need a different group by or order by statement Roy Lambert |
Tue, Apr 13 2021 12:08 PM | Permanent Link |
Adam Brett Orixa Systems | Hi Anthony,
When I saw the TMS Kanban board I liked it, but chose to implement it myself as I found the TMS implementation a bit heavy handed. I use an "ID" (Integer) field in the Kanban table, with a look-up to the user-defined (name) values. CREATE TABLE KanbanLookup (ID INTEGER GENERATED ... , Name VARCHAR) CREATE TABLE KanbanTable (PRIMARYKEY INTEGER, KanbanOrderID INTEGER, Name VARCHAR ... CONSTRAINT KanbanOrderID FOREIGN KEY ("KanbanOrderID") REFERENCES KanbanLookup ("ID)" ) All that is then needed is a mechanism in the UI to map the stored ID onto the lookup Name. There are a number of ways of doing this, such as Lookup Combo Boxes. It can even be useful to show the name using something like: CAST(ID) + ': ' + Name so that the "Name" entries are ordered. Adam |
Tue, Apr 13 2021 2:03 PM | Permanent Link |
Anthony | Roy,
Thanks, I was able to use the runsum(1)-1 to generate a query to produce the required results. Adam, Interesting as after generating the correct result set I connected the TMS KanBan DatabaseAdaptor and can see the data at design time but get an access violation at runtime when I enable the DatabaseAdaptor with and edbQuery or edbTable but no issue with a FireDac connection. Did you implement the entire KanBan UI or just the database adaptor, I suggested to TMS to add a feature to remember the item order |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |