# Query help: two sets of transaction groups, two sets of naming conventions, two cases in where clause **URL:** \ **Category:** Ignition\ **Created:** [January 11, 2022, 5:01pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889 "2022-01-11T17:01:41Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 5:01pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/1 "2022-01-11T17:01:41Z")
Mainly, in a where clause, how do you use two cases correctly for the same thing from two different spots? Spot1 the table on hand spot2 the table you join one set of transactions gets the machine Id as a number. Machines 1-18 for example. So I use inner join with a table of names to get the line names. This one gets me my rejects numbers production numbers are null in these entries one set of transactions just gets the line names. This one gets me my production numbers. rejects are null in these entries my code is something like: ```auto Select subq.Line,year_month, rejects, subq.highOutfeed-subq.lowOutfeed as Outputs from( Select min(t_stamp) as earliest, Format(t_stamp,'yyyy-MMM') as year_month, min(Outfeed) as lowOutfeed, max(Outfeed) as highOutfeed, LineNamesTable.Line from Tablewithrejects inner join LineNamesTable on LineNamesTable.Machine=Tablewithrejects.Machine Where (t_stamp>DATEADD(month, -6,EOMONTH( GETDATE()))) and (:machine=-1 or LineNamesTable.Line = Case when :machine=1 then 'machine-1' when :machine=2 then 'machine-2' else null end or Tablewithrejects.Line=Case when :machine=1 then 'machine1' when :machine=2 then 'machine2' else null end) group by Format(t_stamp, 'yyyy-MMM'), LineNamesTable.Line, Tablewithrejects.Line ) subq order by subq.Line, earliest ``` so as I wrote this I see that I have to resolve this line for subq. The query is not throwing an error. I get the data from the rejects, but not the production. ---
**Author:** ![Charis\_Grubb](https://avatars.discourse-cdn.com/v4/letter/c/d2c977/32.png) [@Charis\_Grubb](https://forum.inductiveautomation.com/u/Charis_Grubb)\ **Post date:** [January 11, 2022, 6:00pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/2 "2022-01-11T18:00:46Z")
Not sure if I’m fully understanding, but what about trying Where (t\_stamp\>DATEADD(month, -6,EOMONTH( GETDATE()))) and (:machine=-1 or LineNamesTable.Line = ‘machine-’ + cast(machine as varchar(1)) or Tablewithrejects.Line= ‘machine’ + cast(machine as varchar(1)) group by Format(t\_stamp, ‘yyyy-MMM’), LineNamesTable.Line, Tablewithrejects.Line ) subq ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 6:07pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/3 "2022-01-11T18:07:41Z")
I figured out a critical issue in my where clause, I have code \> 1100, but when I collect my production information, my codes are below 1100, so I need some way to both isolate to those codes to tally my rejects, but still pick up the production values * * * Charis, that is a good idea. Unfortunately, I had presented generic names, but in reality the names are more categorical in nature. I would have used your recommendation for sure. * * * I am not sure my cases are written correctly. When I say “else null end” is that correct for each? or is one ending and then the other is skipped? And is there some subquery way to get the production values from rows with codes under 1100, but not count the code events from the codes under 1100? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 6:22pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/4 "2022-01-11T18:22:57Z")
I think I figured out a simple solution. I can just make another transaction group on a timer, and collect the production data with whatever code I want assigned to it. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [January 11, 2022, 6:33pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/5 "2022-01-11T18:33:34Z")
What DB are you using? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 6:43pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/6 "2022-01-11T18:43:40Z")
SQL if I understand the question correctly ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 11, 2022, 6:59pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/7 "2022-01-11T18:59:20Z")
Guess not. “SQL” is generic. It is the language used to tell standand databases what you want. All of Ignition’s database drivers are “SQL”. A specific database would be something like: - PostgreSQL 11 - MySQL 5.7 - MariaDB 10 - Microsoft SQL Server 2016 - Oracle 13i Brand and version. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 7:28pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/8 "2022-01-11T19:28:50Z")
I made a new set of transaction groups. When I say “else null end” is that correct for each? or is one ending and then the other is skipped? I am still not reading these other codes in my query. I added or Code=9000 to my where clause I expected to see the values show, but they don’t ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [January 11, 2022, 7:46pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/9 "2022-01-11T19:46:36Z")
@zacharyw.larson see @pturmel above post for clarification. Well it is generally all the “same” thing, they do have differences and methods of doing stuff can vary. If you let us know specifically which one you are using, it can better help guide an answer, best practice, or relevant docs how to properly code it. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 7:53pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/10 "2022-01-11T19:53:32Z")
in ms sql: When I say “else null end” is that correct for each? or is one ending and then the other is skipped? I am still not reading these other codes in my query. I added or Code=9000 to my where clause I expected to see the values show, but they don’t * * * I put left join and also full join both show me the production values, but the lines just say null. I had expected my new transaction names to work. Not sure what happened. Maybe I can tinker with it. I am going to modify my table with the names to include both subsets (with dash and without dash) and the number for the machines. Maybe then I can get it to display the name from that table for both conversions. I don’t know that I can be helped much directly on the issue I am having with the naming, but at least if you could tell me the correct ms sql syntax for two cases with an or in the where clause, I would appreciate it. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 7:59pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/11 "2022-01-11T19:59:53Z")
is this okay syntax in MS SQL for two cases or’d in the where clause? I don’t know the specific version. Whatever version W3schools is using has been working so far. ```auto where (:machine=-1 or LineNamesTable.Line = Case when :machine=1 then 'machine-1' when :machine=2 then 'machine-2' else null end or Tablewithrejects.Line=Case when :machine=1 then 'machine1' when :machine=2 then 'machine2' else null end) ``` ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [January 11, 2022, 8:06pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/12 "2022-01-11T20:06:33Z")
@zacharyw.larson this is the link to the official MS SQL Documentation [https://docs.microsoft.com/en-us/sql/t-sql/language-elements/control-of-flow?view=sql-server-ver15](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/control-of-flow?view=sql-server-ver15) ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 9:04pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/13 "2022-01-11T21:04:34Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/a/ca12590a8eda700357ab9761799a7da08ea66568.png) so close yet so far ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [January 11, 2022, 9:04pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/14 "2022-01-11T21:04:46Z")
@zacharyw.larson Check out this post off site [https://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause](https://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause) scoll at least 7-8 comments down. And you should see a pretty good example. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 9:11pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/15 "2022-01-11T21:11:12Z")
That looks like the answer is in that. Thanks I am trying to figure out how to perform that. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [January 11, 2022, 9:13pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/16 "2022-01-11T21:13:07Z")
@zacharyw.larson no problem. I’m too busy right now to type it up, but will try later. Hopefully someone can post a more full example sooner. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 11, 2022, 9:49pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/17 "2022-01-11T21:49:40Z")
The issue has changed. The cases are working. I have the data collected into my table, but they have different names. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/a/ca12590a8eda700357ab9761799a7da08ea66568.png) One is from my conversion table from number to table name. One is from the table on hand. And this is more convoluted because one set even splits a machine in half from the PLC. to visualize the issue ```auto column1 column2 b-3 | b3 c 3 part1 | c3 c 3 part2 | c3 row examples rejects | NumberName | ConvertedName | Name | Infeed | Outfeed null | null | null | c3 | 10 | 8 1 | 00012 | C 3 part1 | null | null | null 1 | 00014 | C 3 part2 | null | null | null What I want: Rejects | Name | Infeed | Outfeed 2 | c3 | 10 | 8 ``` I think I need to make another conversion table. I like half the names in the one set, and half the names in the other set. I will work on this tomorrow. I like the names with the dashes, but I don’t want the machines by parts so I need to total those which means converting in tables and grouping by the converted name I think. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 11, 2022, 11:30pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/18 "2022-01-11T23:30:25Z")
You should consider not using names for joining. Just make a lookup table that has a numeric machine ID and the various static information about it, like its name(s). Use the numeric machine ID in all the of the tables that actually record your data. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 12, 2022, 3:19pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/20 "2022-01-12T15:19:12Z")
agreed, the best way is to have avoided this mess to begin with haha Is it possible to do two different joins with one lookup table matching two different columns to get one set of names? I am attempting it. I think I have two parts to one machine from the plc though, and I don’t think it will tally them. Maybe it will if I group it right. I am not sure. edit: I found a resource > **[How to Join the Same Table Twice](https://learnsql.com/blog/how-to-join-same-table-twice/)** > > Learn when and why you need to join the same table twice in SQL, including when you should use self joins. This was really helpful for situations of joining multiple times to the same table and it has some other unusual joins. yet, when they double join, they select two different columns, each from a different relabel of the lookup table. Maybe I do that, then a coalesce/concat? Or maybe I need a select case * * * it finally hit me I had half my table correct for rejects half my table correct for production I just need to make two subquery tables, and join them on the date format column and the converted names ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 12, 2022, 7:29pm UTC](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889/21 "2022-01-12T19:29:52Z")
com.microsoft.sqlserver.jdbc.SQLServerException: The index 40 is out of range. I keep getting this error. I even deleted the query, and put in the previous versions that were working, yet I get this error. [Next page](https://forum.inductiveautomation.com/t/query-help-two-sets-of-transaction-groups-two-sets-of-naming-conventions-two-cases-in-where-clause/54889.md?page=2)