Question No value given for one or more required parameters on join

pblevins10

New member
Joined
Dec 14, 2009
Messages
3
Programming Experience
3-5
:confused:Hello All,

I have the following SQL query that runs against an Excel datasource and I'm getting the infamous "No value given for one or more required parameters" because of a join. It's erroring on the where t1.[trnVendorName] = v.[Vendor] part. It is acting like it can't see the outside query. Any thoughts? This is finding the median of a value by the way.

Thanks in advance.
Code:

Dim strSubmittalRatesSQL = "select distinct v.[Vendor]," & _
"(select MAX([rate1]) FROM (" & _
"select top 50 percent ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 as [rate1] " & _
"from [Sikorsky_Submittal$] t1 " & _
"where t1.[trnVendorName] = v.[Vendor] " & _
"and t1.[POAvgOrigBillRateST] is not null " & _
"order by ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 asc " & _
") h1) + " & _
"(select MIN([rate2]) FROM ( " & _
"select top 50 percent ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 as [rate2] " & _
"from [Sikorsky_Submittal$] t2 " & _
"where t2.[trnVendorName] = v.[Vendor] " & _
"and t2.[POAvgOrigBillRateST] is not null " & _
"order by ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 desc " & _
") h2) " & _
"as [SubmittalRates1] " & _
"from [Sikorsky_Vendors$] v"
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
It wont see the outer table inside the subquery. You'll need to join the table twice, something like:-

Code:
"from [Sikorsky_Submittal$] t1 INNER JOIN [Sikorsky_Vendors$] sv ON t1.[trnVendorName] = sv.[Vendor]"
 

pblevins10

New member
Joined
Dec 14, 2009
Messages
3
Programming Experience
3-5
I'll try that InertiaM, not sure it will work for what I need in this situation. I will let you know. I may have to rethink my logic.

Thanks!

Paul
 

pblevins10

New member
Joined
Dec 14, 2009
Messages
3
Programming Experience
3-5
It wont see the outer table inside the subquery. You'll need to join the table twice, something like:-

Code:
"from [Sikorsky_Submittal$] t1 INNER JOIN [Sikorsky_Vendors$] sv ON t1.[trnVendorName] = sv.[Vendor]"

Yeah unfortunately this is giving me the same value for each Vendor. I wonder why it can't see the outer query?
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
What a hideous query! I've reformatted it to give some kind of nesting:

Code:
select distinct 
  v.[Vendor], 
  (select 
     MAX([rate1]) 
   FROM 
     (select 
        top 50 percent ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 as [rate1]  
      from 
        [Sikorsky_Submittal$] t1  
      where 
        t1.[trnVendorName] = v.[Vendor] and 
        t1.[POAvgOrigBillRateST] is not null  
      order by 
        ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 asc  
    ) h1
  ) +  
  (select MIN([rate2]) 
   FROM 
   (select top 50 percent ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 as [rate2]  
		from 
		  [Sikorsky_Submittal$] t2  
		where 
		  t2.[trnVendorName] = v.[Vendor] and 
		  t2.[POAvgOrigBillRateST] is not null  
    order by 
      ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 desc  
  ) h2
) as [SubmittalRates1]  
from 
  [Sikorsky_Vendors$] v
But still.. what's this query actually supposed to do?

Why do you make the DB select a huge number of records, order them, take the top 50 percent, then take the maximum - surely if you had the numbers 1 to 10, ordered 10 to 1, then took the top 50, so they are 10 to 5, then took the max you get 10.. but you might as well have just said "select Max(number) from list_of_numbers_1_to_10" to get your 10 - what was all the top 50% for?

Chucking in a distinct on a query is something I've often felt is a sign of a badly qritten query' it's turned out duplicate rows at the end and the developer just threw a distinct in to get rid of them instead of working out why they were there in the first place

It looks like you want, per vendor, the min plus max submittal rates

A query like this would be better:

Code:
SELECT
  [vendor],
  rate1 + rate2 as [SubmittalRates1]
FROM
  (
    SELECT 
      [vendor] 
    FROM 
      [Sikorsky_Vendors$] 
    GROUP BY vendor
  ) vends
  LEFT OUTER JOIN
  (
    SELECT 
      [vendor],
      MAX((([MaxSubmittaltrnBillRateST]-[POAvgOrigBillRateST])/[POAvgOrigBillRateST])*100) as rate1  
      MIN((([MaxSubmittaltrnBillRateST]-[POAvgOrigBillRateST])/[POAvgOrigBillRateST])*100) as rate2 
    FROM
      [Sikorsky_Submittal$]
    WHERE
      [POAvgOrigBillRateST] is not null
    GROUP BY
      [vendor]
  ) subs 
  ON
    vends.[vendor] = subs.[vendor]
Note; I've guessed at what youre trying to do, so this may not be quite right, but here we get the DB to prepare in advance a list of the min and max rates per vendor, and join them to distinct vendors - these 2 subqueries thus relate 1:1. Because we only scan the submittal rates table once, looking for the mins and maxes, it should perform better. Bad db implementations will run a query in the select list once for each row in the results, which could have massive performance implications - avoid putting queries in the select list. Prepare your data in blocks and join it together

I'd also suggest that you use the Resources in your project, to store the SQL rather than pasting it into the code with all the " & _ at the end
Open Project properties, open resources, make a new string resource, paste the query in.. Makes it must easier to design
 
Top Bottom