Trouble with inner subqueries

ERP 10
#1

i have an inner sub query bringing in a complete table including:
job number operation code order number

all i want to do is count the number of times each operation code appears on a job associated with each order (each in a separate column on the top level
order number op1 count op2 count op3 count etc…

i can do this my using a separate subquery for each operation and using the same tables with a filter for each operation

for the life of me, i cannot figure out how to just group the table i already have and count them all at one go…
order number op1 count op2 count op3 count etc,
if op=‘a’ if op=‘b’ if op=‘c’
is this making sense?

#2

It’s a PIVOT. The ICE Tools user guide explains it well.

Only thing is, you have to know the column names ahead of time. It’s not as intuitive as Excel.

#3

If I understand correctly, you can group by the job and order number.
Then make 3 calculated fields… one for each op.
within each calculated field, you’d write a statement like this:

sum(case when subquery_tableName.OpCode = 'op1' then 1 else 0 end)

then you’d repeat this for each Operation replacing op1 with op2, op3, etc.

1 Like
#4

ok, that seems to work. the pivot functionality is new to me. appreciate it. the user guide actually was very descriptive this time.

so how would i add a new query to count the number of times each op linked to each order is complete?

tried adding a new subquery/pivot (identical basically) that was summing a calculated field (similar to dr dan) but that didn’t seem to work…

#5

said true and false were invalid column names

#6

“… tried adding a new subquery/pivot… true and false were invalid”
Wonder if you can show an example of the end view you’re looking for?
Also, how many opcodes are involved?

“…know the column names ahead of time. (E10 Pivots) …not as intuitive as Excel.”
Pivots are nice when I’m doing something simple. As complexity increases, I start looking at alternate approaches.

#8

End result is I want to show summarized job status per order. Each order has one row per shipdate showing order number, customer name, shipdate, op status (See below)

The pivot I have is working fine (thanks @JasonMcD !) counting the number of times “op1” shows up on jobs linked to each order number per required date

Now I just need the number of times that operation is complete, then I can divide that by the number of times the op is present and I will have a 3 part indicator:

X=1=”complete”

1>X>0 = “in process”

X=0=”start”

I appears you can only aggregate 1 “data type” (count(op.code) for example) per table

So I thought “ link another pivot with an ordernum to ordernum “matching from both” link type”

However, there is some complication giving a “invalid column name: false” error. I think it has something to do with the Boolean value of OpComplete, but I tried caseing it to a decimal of 1 or 0, so maybe you just can’t use 2 pivots in one query?

I even tried knocking the second pivot down a step by linking it to a subquery instead and bring in the subquery to the first (working) pivot, but same error

first second
pivot pivot on TOP
order # some customer shipdate “op1 count” “op1 complete count” calc_status
>>>>9 abc. Inc 9/1/2021 12 6 in process
aggregate (count(JobOper_OpCode) FOR JobOper_OpCode IN “constantlist” (op codes) aggregate(sum(calc_Op_Comp)(true=1,false=0) FOR JobOper_OpCode IN 'constantlist" (op codes)
produces error
#9

I know this isn’t any help, but I think that’s either more complex than anything I have ever needed, or I probably gave up and did many subqueries instead.

I think you have all the knowledge needed; it’s just trial and error at this point.

#10

yea, at least the first pivot will save me from half the work of “many queries” it just seems it should be fair to marry two pivots through the common “ordernum” field

slogging through CTE at the moment, maybe a dead end…

#11

ok. i figured it out. CTE was a dead end (i think, i had an epiphany half way through it and bailed)

it appears you cannot use two separate pivot tables in one query (far far FAR be it from me to verify or explain…lol)

however, you CAN pivot the same table multiple times (at least twice that i know of anyway)

so by adding a second pivot of the same subquery and adding a criteria for only complete operations, i am able to count how many of op1 there are for all jobs linked to an order and how many are complete. Thus, catapulting me forward to whatever my NEXT dilemma ends up being.

probably just telling everyone what they already know, but i find it irritating when forum topics end with “figured it out, thanks.” it’d be a shame if i were “that guy”

thanks again for the help!!!

2 Likes