Expression with Where... Possible?

[boostbyslinky]

I'm using an expression to concatenate results within a query (three text fields), but having, relatively recently, learnt of the "unsuitability" of the LAST function, I'm aware that I cannot trust the output of this particular part of the query..

The Expression is currently this :

Fault_Description: Last([dbo_ReportData].[Fault Desc 1]) & " " & Last([dbo_ReportData].[fault desc 2]) & " " & Last([dbo_ReportData].[fault desc 3])

What I would like to do is add in a WHERE clause to tie down the 3 "Fault Desc" fields to those that match the Max Job No for the docket in question..

Note :
Docket = Lifespan call reference
Job No = Unique Job Identifier

The full "SQL" code for the query at present is below

SELECT DISTINCT dbo_ReportData.DOCKET, dbo_ReportData.site_search, Priority_Grouping.Category, Priority_Grouping.Priority, Prod_ShortCode.ShortCode, Prod_ShortCode.HighLevelGroup, dbo_ReportData.Description, Count(dbo_ReportData.[Job No]) AS [CountOfJob No], Sum(dbo_ReportData.[Response Time]) AS [SumOfResponse Time], Sum(dbo_ReportData.[Fix Time]) AS [SumOfFix Time], Min(dbo_ReportData.[Received Date]) AS [MinOfReceived Date], Max(dbo_ReportData.[Completion Date]) AS [MaxOfCompletion Date], Last([dbo_ReportData].[Fault Desc 1]) & " " & Last([dbo_ReportData].[fault desc 2]) & " " & Last([dbo_ReportData].[fault desc 3]) AS Fault_Description, Last([dbo_ReportData].[Work Done 1]) & " " & Last([dbo_ReportData].[Work Done 2]) AS Work_Done
FROM Prod_ShortCode INNER JOIN (Priority_Grouping INNER JOIN dbo_ReportData ON Priority_Grouping.Priority = dbo_ReportData.Priority) ON Prod_ShortCode.Description = dbo_ReportData.Description
WHERE (((dbo_ReportData.[Log Num])=1 Or (dbo_ReportData.[Log Num])=2 Or (dbo_ReportData.[Log Num])=3 Or (dbo_ReportData.[Log Num])=5 Or (dbo_ReportData.[Log Num])=6 Or (dbo_ReportData.[Log Num])=12 Or (dbo_ReportData.[Log Num])=24 Or (dbo_ReportData.[Log Num])=36) AND ((dbo_ReportData.[Completion Date]) Between [Enter Start Date] And [Enter End Date]) AND ((dbo_ReportData.[Site Group]) Like [Enter Site Group]))
GROUP BY dbo_ReportData.DOCKET, dbo_ReportData.site_search, Priority_Grouping.Category, Priority_Grouping.Priority, Prod_ShortCode.ShortCode, Prod_ShortCode.HighLevelGroup, dbo_ReportData.Description
ORDER BY Count(dbo_ReportData.[Job No]) DESC;

Anyone got any thoughts?

TIA..

 

[Guus2005]

I assume that the Last() function is pseudo code or a function you have written.

You could use the DMax function instead of the Last() function.
Mind you that domain functions as DMax will slow the query down. Search for TLookup on the forum for a faster equivalent.

Better yet is to join the table with the requested information.

HTH

BTW I: using both Distinct and Group By in the same query is silly. Skip the Distinct statement.
BTW II:Use Where dbo_ReportData.[Log Num] in (1,2,3,12,23) instead of the Or... variant.

 

[boostbyslinky]

Nope, LAST is a built in function, albeit very unreliable without exact sorting (difficult with the dataset involved)..

So, back to the subject in question (that I've run into again today), is it possible to use a WHERE clause in an expression within a query?

 

[Guus2005]

Okay, Last is a built in function. But why would you use it when the outcome is unpredictable and thus unreliable?

Now i know why i didn't know the function, i never use it.

In your case i'd use the domain functions DMax, DLast, etc to get the desired result. Each domain function consists of three parts, the field(s) to be retrieved, the table or query name and the where clause.

So, yes it is possible to use a where clause in an expression within a query.

Enjoy!

 

[DCrake]

(D)Last is the method you use to get the last record thown into a bucket (the table) now this does not mean that it is at the top of the pile. Imagine having 9 red balls and one white one. Throw the 9 red ones in first then throw the white one in.

Now close you eyes and reach down and pull out the first ball you touch. Can you guarrantee that this will be white every time? I doubt it.

Now if you added the time you threw in the ball and first organised them by time you could always pick the white one out as the newest one would be at the top.

Do you get my point?

 

[boostbyslinky]

If only we recorded the time that the ball was thrown in.. Unfortunately the records in the table are batch updated/replaced by an overnight process..

To give a little background, the output is from an informix database to which "public" access is not allowed... Therefore the records are exported overnight and imported into tables within a SQL database... If (oh I wish!) we could allow sufficient access to the informix tables, or (better still) replace the solution, then I think I would have a slightly easier job!

I know that the long term solution for me would be to skill up in SQL, but I don't particularly want to learn this on a production server that hosts other business critical applications..

 

[DCrake]

So if records are entered in in batches how will you ever identify which was the last one added. If that is what you want.

 

[boostbyslinky]

The data is output from our helpdesk system, so, if you don't mind me writing a query in SQL/English

Docket = Lifetime Call Reference (numeric)
Job No = Reference for each interaction with the call (numeric)

For each docket, select max of Job No, select Fault Description(1 & 2) AND Work Done (1, 2 & 3) in relation to Max of Job No

(if that makes sense)..

Hopefully that explains my wish to use a Where expression with the concatenation that is being carried out by the expression

 

[Brianwarnock]

I would be thinking of multiple queries here I think.
1 to find the max job no per docket
2 join back to get other info

Brian