Query help: two sets of transaction groups, two sets of naming conventions, two cases in where clause

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:

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.

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

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?

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.

What DB are you using?

SQL if I understand the question correctly

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.

1 Like

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

@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.

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.

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.

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)

@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

image

so close
yet so far

@zacharyw.larson Check out this post off site
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.

That looks like the answer is in that. Thanks

I am trying to figure out how to perform that.

@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.

The issue has changed. The cases are working. I have the data collected into my table, but they have different names.

image

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

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.

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.

2 Likes

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

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

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.