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.
