I am trying to create a query that gives a count of times a part/SN combo has been on a Job.
We repair equipment for customers and have been asked that if a unit has come in for repair more than 2 times to junk the unit. We create a unique Sales Order and Job each time it comes in, so I figured I would count the previous jobs instead of trying to count this against the SNTran table.
I was asked to be able to run this by all open jobs, so I have a query that is only pulling in Open Jobs. I am then creating a subquery pulling in the JobHead table again and linking it to the Top Level query by Company, PartNum, ShortChar02(where we store the SN). I am then creating a Calculated field and inputting count ( * ) (following an example in the ICE Tools User Guide). In the expression editor, it says Syntax OK; however when I go to the Analyze tab and click Analyze, I get an “Incorrect syntax near ‘(’” error message.
Even if I replace * with a field from JobHead, I receive the same error message.
I know the logic in the joins to pull the right records is spotty, but I can fix that later. I’ve checked the Group By boxes in the subquery that I’m using for my joins.
What am I missing? I feel like this should be simple so any assistance is appreciated.
The basic idea is that the calculated field has to be an integer type, and then you group by all non-calculated fields that you are displaying.
Yeah I think you could use any field inside the count(). I try to be specific, but I think it’s honestly irrelevant–it’s not a “count unique entries”; it just counts rows.
Interesting so in 10.2.400 I can use it in a Sub-Query, I couldnt in 10.2.300 even COUNT(*) works in 10.2.400. Maybe it was something in 10.2.300
Anyways works now, so perhaps a bug previously (I know I had it in 10.2.300.2x) or its one of those Bugs that only show up when you have few more tables + few more calculated fields.
But in 10.2.300.9 works fine hm.
O well try nvarchar if integer doesnt work for you.
Whew had me worried for a moment. Until I got to the bottom of this bost. I just went through this today. I needed to create a list from the partbin, that excluded any rows that had a count of lotnumber greater than 1. I was pretty sure it worked. On 10.2.400.16, so maybe you are right.
Thanks for nvarchar suggestions, but that isn’t working either. I’ve included screens below in case someone sees something I missed. I’m experiencing the same results in 10.2.200.31 and 10.2.500.2.
SELECT (SELECT count(*) FROM table WHERe...) as YourCalculatedColumn
It is proven that JOINs are usually faster and sometimes SQL will even convert your SUB-SELECT when it can, but if you get into a more complex scenario where you are getting tangled in Sub-Queries, GROUPS, etc… sometimes Sub-Selects work easier. FYI thought Id share something.
The best time they work is when you need to get let’s say The Last Approved Revision nothing easier than a SubSelect and then Order By ApprovedDate DESC, but when not using Aggregate like count(*) then you must make sure you set your SubQuery2 to return only 1 row, 1 column.
Good lesson learnt from this post, and it’s the simple stuff we forget or just do things because we have been doing it so long, we don’t event think about it.
Note to self , don’t use TSQL reserved words as field names for calculated fields
Any idea how to get total number of rows returned? Lets say if I do a filter of dates. Query returned 120 rows which shows under “Query Execution Messages”. I want to know how many rows returned in that filter/list.
With your partition by something that’s the same for all the rows.
This will count all of the rows, and add this onto a column. You can hide this in the grid and show it on the tracker at view only, and it won’t change since it’s the same for all of the rows.
works now, so perhaps a bug previously (I know I had it in 10.2.300.2x) or its one of those Bugs that only show up when you have few more tables + few more calculated fields.
