Headache from a Very Simple SQL Query

jhecker · September 14, 2022, 6:31pm

Scenario: I have been asked to obtain the latest UnitPrice for the PartNum of a given LaborDtl Transaction. LaborDtl does not have a PartNum, so I join it up to JobHead using .Company and .JobNum as the links.

First things first, lets get the LaborDtl records, narrowing it down to a specific day and employee for debugging purposes. I’ve already set up the JOIN for the JobHead table and grabbed the PartNum from it as well.

SELECT 
  LaborDtl.Company AS LaborDtl_Company, 
  LaborDtl.EmployeeNum AS LaborDtl_EmployeeNum, 
  LaborDtl.JobNum AS LaborDtl_JobNum, 
  JobHead.PartNum AS JobHead_PartNum 
FROM 
  Erp.LaborDtl AS LaborDtl 
  INNER JOIN Erp.JobHead AS JobHead ON LaborDtl.Company = JobHead.Company 
  AND LaborDtl.JobNum = JobHead.JobNum 
WHERE 
  (
    LaborDtl.ClockInDate = '01/03/2022' 
    AND LaborDtl.EmployeeNum = 'jamf'
  )

Fairly straight forward so far. The query returns 4 rows of data:

Company	EmpNum	JobNum	PartNum
SA	JAMF	1842529	4567890
SA	jamf	1842379-CP	C123456
SA	JAMF	1842379-CP	C123456
SA	JAMF	1842566	1357924

That data is accurate, so time to look into the InvcDtl table to get the most recent sale price:

SELECT 
  TOP (1) InvcDtl.Company AS InvcDtl_Company, 
  InvcDtl.UnitPrice AS InvcDtl_UnitPrice, 
  InvcDtl.PartNum AS InvcDtl_PartNum, 
  InvcDtl.ShipDate AS InvcDtl_ShipDate 
FROM 
  Erp.InvcDtl AS InvcDtl 
WHERE 
  InvcDtl.PartNum = 'C123456' 
ORDER BY 
  InvcDtl.ShipDate DESC

That returns:

Company	UnitPrice	PartNum	ShipDate
SA	3.61000	C123456	7/15/2022

Great! So far, so good! Time to combine them:

WITH ldQuery AS (
  SELECT 
    LaborDtl.Company AS LaborDtl_Company, 
    LaborDtl.EmployeeNum AS LaborDtl_EmployeeNum, 
    LaborDtl.JobNum AS LaborDtl_JobNum, 
    JobHead.PartNum AS JobHead_PartNum 
  FROM 
    Erp.LaborDtl AS LaborDtl 
    LEFT OUTER JOIN Erp.JobHead AS JobHead ON LaborDtl.Company = JobHead.Company 
    AND LaborDtl.JobNum = JobHead.JobNum 
  WHERE 
    (
      LaborDtl.ClockInDate = '01/03/2022' 
      AND LaborDtl.EmployeeNum = 'jamf'
    )
), 
idQuery AS (
  SELECT 
    TOP (1) InvcDtl.Company AS InvcDtl_Company, 
    InvcDtl.UnitPrice AS InvcDtl_UnitPrice, 
    InvcDtl.PartNum AS InvcDtl_PartNum, 
    InvcDtl.ShipDate AS InvcDtl_ShipDate 
  FROM 
    Erp.InvcDtl AS InvcDtl 
  ORDER BY 
    InvcDtl.ShipDate DESC
) 
SELECT 
  * 
FROM 
  ldQuery 
  INNER JOIN idQuery ON ldQuery.LaborDtl_Company = idQuery.InvcDtl_Company 
  AND ldQuery.JobHead_PartNum = idQuery.InvcDtl_PartNum

That returns a rather disappointing result of:

Company	EmployeeNum	JobNum	PartNum	Company	UnitPrice	PartNum	ShipDate
SA	JAMF	1842529	4567890	(null)	(null)	(null)	(null)
SA	jamf	1842379-CP	C123456	(null)	(null)	(null)	(null)
SA	JAMF	1842379-CP	C123456	(null)	(null)	(null)	(null)
SA	JAMF	1842566	1357924	(null)	(null)	(null)	(null)

I tried other variations of the query, such as performing a join on the subquery itself:

LEFT OUTER JOIN (SELECT TOP (1) InvcDtl.Company.....

And I get the same results. Is there something very obvious that I am overlooking with this query? I’ve been staring at it all day, so my eyes are semi-glazed over at this point.

And I have tried this as a BAQ as well, with the same results.

jkane · September 14, 2022, 6:52pm

@NateS this is a perfect query to use a CTE for.

@jhecker sorry for hijacking your question, I’m going to answer it, but I also want to add onto Nate’s post. I Still Don't Understand CTEs

So, this is what I would do.

Create your BAQ and make SubQuery1 a CTE. I would group on all the fields to get rid of any duplicate lines. You will end up with an in memory table of:

Company	EmpNum	JobNum	PartNum
SA	JAMF	1842529	4567890
SA	JAMF	1842379-CP	C123456
SA	JAMF	1842566	1357924

Now add SubQuery2 to your BAQ as a CTE and you will have a second in memory table of:

Company	UnitPrice	PartNum	ShipDate
SA	3.61000	C123456	7/15/2022

Now, create SubQuery3 as TopLevel and add both SubQuery1 and SubQuery2 as your tables in Phrase Build and join them. Select your Display Fields and you should return:

Company	EmployeeNum	JobNum	PartNum	Company	UnitPrice	PartNum	ShipDate
SA	JAMF	1842379-CP	C123456	SA	3.61	C123456	7/15/2022

utaylor · September 14, 2022, 6:56pm

Lovely!

MikeGross · September 15, 2022, 3:29pm

Great segway @jkane !

jhecker · September 16, 2022, 3:37pm

@jkane - In my initial post, I did have it as a CTE, but I ran through it again from scratch, following your instructions to the “T”.

So, breaking it down:

Step 1 - Create the CTE for LaborDtl as ldQuery, and also create the TopLevel Query

with [ldQuery] as 
(select 
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate]
from Erp.LaborDtl as LaborDtl
inner join Erp.JobHead as JobHead on 
	LaborDtl.Company = JobHead.Company
	and LaborDtl.JobNum = JobHead.JobNum)

select 
	[ldQuery].[LaborDtl_ClockInDate] as [LaborDtl_ClockInDate],
	[ldQuery].[LaborDtl_EmployeeNum] as [LaborDtl_EmployeeNum],
	[ldQuery].[JobHead_PartNum] as [JobHead_PartNum],
	[ldQuery].[LaborDtl_JobNum] as [LaborDtl_JobNum]
from  ldQuery  as ldQuery
group by [ldQuery].[LaborDtl_ClockInDate],
	[ldQuery].[LaborDtl_EmployeeNum],
	[ldQuery].[JobHead_PartNum],
	[ldQuery].[LaborDtl_JobNum]

I also created the TopLevel Query as well, grabbing everything from ldQuery.
I copied the query to SSMS and ran it, and it finishes in 13 seconds with 1.7 million results. When I run it in the BAQ Designer, it takes 52 seconds and returns 1.7 million results as well. Why it takes 4x longer as a BAQ is beyond me, but at least it returns data.

Step 2 - Create the CTE for InvcDtl as idQuery
I think that this is where something falls apart. I can create this query on its own in SSMS, and it works great. But, the moment I drop it into the BAQ as a CTE, it fails. This is the query on it’s own:

SELECT TOP (1)
        [InvcDtl].[UnitPrice] AS [InvcDtl_UnitPrice],
        [InvcDtl].[PartNum]   AS [InvcDtl_PartNum],
        [InvcDtl].[ShipDate]  AS [InvcDtl_ShipDate]
FROM
        Erp.InvcDtl AS InvcDtl
WHERE
        InvcDtl.PartNum = 'C373801'

ORDER BY
        InvcDtl.ShipDate DESC

It works great, returns the single line of data as expected. But, when I drop it into the BAQ as a CTE, it is a no-go.

WITH [ldQuery]
AS (   SELECT
               [LaborDtl].[EmployeeNum] AS [LaborDtl_EmployeeNum],
               [LaborDtl].[JobNum] AS [LaborDtl_JobNum],
               [JobHead].[PartNum] AS [JobHead_PartNum],
               [LaborDtl].[ClockInDate] AS [LaborDtl_ClockInDate]
       FROM
               Erp.LaborDtl AS LaborDtl
           INNER JOIN
             Erp.JobHead AS JobHead
                 ON LaborDtl.Company = JobHead.Company
                 AND LaborDtl.JobNum = JobHead.JobNum
       WHERE
               (LaborDtl.ClockInDate = @Date)
       GROUP BY
               [LaborDtl].[EmployeeNum],
               [LaborDtl].[JobNum],
               [JobHead].[PartNum],
               [LaborDtl].[ClockInDate]),
[idQuery]
AS (   SELECT TOP (1)
               [InvcDtl].[UnitPrice] AS [InvcDtl_UnitPrice],
               [InvcDtl].[PartNum] AS [InvcDtl_PartNum],
               [InvcDtl].[ShipDate] AS [InvcDtl_ShipDate]
       FROM
               Erp.InvcDtl AS InvcDtl
       ORDER BY
               InvcDtl.ShipDate DESC)
SELECT
        [ldQuery].[LaborDtl_ClockInDate] AS [LaborDtl_ClockInDate],
        [ldQuery].[LaborDtl_EmployeeNum] AS [LaborDtl_EmployeeNum],
        [ldQuery].[JobHead_PartNum]      AS [JobHead_PartNum],
        [ldQuery].[LaborDtl_JobNum]      AS [LaborDtl_JobNum],
        [idQuery].[InvcDtl_PartNum]      AS [InvcDtl_PartNum],
        [idQuery].[InvcDtl_ShipDate]     AS [InvcDtl_ShipDate],
        [idQuery].[InvcDtl_UnitPrice]    AS [InvcDtl_UnitPrice]
FROM
        ldQuery AS ldQuery
    INNER JOIN
      idQuery AS idQuery
        ON ldQuery.JobHead_PartNum = idQuery.InvcDtl_PartNum

That query returns 0 results. I’ve again tried different join types to no avail. I am getting so fed up with what should be a simple query. I have worked on this for hours a day, all week, and can’t seem to get it right.

I have attached the BAQ if anyone is interested in possibly pinpointing what exactly is wrong.

BAQ_Headache.baq (38.3 KB)

timshuwy · September 16, 2022, 4:08pm

did you define the CTE query to be the first query that runs in the BAQ?

Olga · September 16, 2022, 4:12pm

With this query:

select top (1)
[InvcDtl].[UnitPrice] as [InvcDtl_UnitPrice],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[InvcDtl].[ShipDate] as [InvcDtl_ShipDate]

from Erp.InvcDtl as [InvcDtl]
order by InvcDtl.ShipDate Desc

you select exactly one single record from the whole table. It does not change regardless all other subqueries you added. I think it is not what you want, you want it to be 1 record per some other criterion.
You don’t need CTE, you need correlated subquery

MikeGross · September 16, 2022, 5:14pm

I was going to say the same thing as @Olga . I would have thought you’d want something like

   select unitprice, partnum and max(ship date) from invcdtl group by Partnum

in order to create a dataset that would match each of the partnums returned from the labordtl query. I’ve not worked out the SQL but I think this is what you may need.

jkane · September 16, 2022, 5:16pm

Here see if this helps.
BAQ_Headache_2.baq (39.6 KB)

I took the subquery you had out of ldQuery. I also created a Parameter for Part Number.

jhecker · September 16, 2022, 7:00pm

@Olga - Using that exactly as it is (IE: what you created) works great. However, if I take out the Part parameter, it returns no results. The only parameters I will be passing (once completed) will be a start and end date, which is why I tried to remove Part from the equation.

I tried a different-ish approach on this as well:

with [id] as 
(select 
	[InvcDtl].[PartNum] as [InvcDtl_PartNum],
	[InvcDtl].[UnitPrice] as [InvcDtl_UnitPrice],
	[InvcDtl].[ShipDate] as [InvcDtl_ShipDate],
	(ROW_NUMBER() OVER (PARTITION BY InvcDtl.PartNum ORDER BY InvcDtl.ShipDate DESC)) as [Calculated_rn]
from Erp.InvcDtl as InvcDtl
group by [InvcDtl].[PartNum],
	[InvcDtl].[UnitPrice],
	[InvcDtl].[ShipDate])
 ,[ld] as 
(select 
	[LaborDtl].[Company] as [LaborDtl_Company],
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum]
from Erp.LaborDtl as LaborDtl
inner join Erp.JobHead as JobHead on 
	LaborDtl.Company = JobHead.Company
	and LaborDtl.JobNum = JobHead.JobNum
group by [LaborDtl].[Company],
	[LaborDtl].[EmployeeNum],
	[LaborDtl].[ClockInDate],
	[LaborDtl].[JobNum],
	[JobHead].[PartNum])

select 
	[ld].[JobHead_PartNum] as [JobHead_PartNum],
	[ld].[LaborDtl_ClockInDate] as [LaborDtl_ClockInDate],
	[ld].[LaborDtl_Company] as [LaborDtl_Company],
	[ld].[LaborDtl_EmployeeNum] as [LaborDtl_EmployeeNum],
	[ld].[LaborDtl_JobNum] as [LaborDtl_JobNum],
	[id].[Calculated_rn] as [Calculated_rn],
	[id].[InvcDtl_PartNum] as [InvcDtl_PartNum],
	[id].[InvcDtl_ShipDate] as [InvcDtl_ShipDate],
	[id].[InvcDtl_UnitPrice] as [InvcDtl_UnitPrice]
from  id  as id
inner join  ld  as ld on 
	id.InvcDtl_PartNum = ld.JobHead_PartNum
where (id.Calculated_rn = 1)
 and (ld.LaborDtl_ClockInDate = '01/03/2022')

This works perfectly from what I can tell. And when I run it in SSMS it takes less than 3 seconds.
As a BAQ, however, it takes almost 2 minutes

Olga · September 16, 2022, 7:46pm

BAQ adds additional security rules on base of your query, as disclaimer on the 1st page suggests.

If you want to avoid them alltogether, use external BAQ.

For the query itself, I think that you are wasting resources by selecting amd grouping all records in the 2 tables here’:
select
[LaborDtl].[Company] as [LaborDtl_Company],
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum]
from Erp.LaborDtl as LaborDtl
inner join Erp.JobHead as JobHead on
LaborDtl.Company = JobHead.Company
and LaborDtl.JobNum = JobHead.JobNum
group by [LaborDtl].[Company],
[LaborDtl].[EmployeeNum],
[LaborDtl].[ClockInDate],
[LaborDtl].[JobNum],
[JobHead].[PartNum])

and then just use only those where LaborDtl_ClockInDate = '01/03/2022
May be you add date parameter and use it in both subqqueries?