Re: SQL Question

Adam,

It can be done, but doing so would be usual. Normally, you'd have a
UI that display's customer name allowing the user to select it. But
the UI would actually pass customer number to the report function.

For it to work the way you want, you'd have to ensure that customer
name was unique.

To do what you want, simply do an inner join to the table containing
the customer name & number (you don't give the name, but I'll assume
CustomerMaster) then change your where to something like so:
WHERE "CustomerMaster"."CUSTOMERNAME"=@CUSTNAME

Another option would be a subselect:
WHERE "CustomerBudget"."CUSTNMBR"= (Select CustomerName from CustomerMaster

where customername= @custname)

On the i, the query engine would most likely rewrite the subselect
version of the statement into the inner join version. I don't know if
MS SQL server does similar rewrites.

Charles


On Fri, Sep 18, 2009 at 9:12 AM, Adam West <adamster@xxxxxxxxx> wrote:

HI I have a SQL that I have to modify.

Custnmbr is in the table Customerbudget and this is the parameter, but we only have customer name as the parameter, in the Reporting System. I need to have a join that will take the Customername from the user and locate the correct number keeping this SQL working correctly as it is, I  need to have the user enter the Customer name and lookup if you will the Custnmbr, and let the SQL process as it is.: (  I suffer a bit from good old use Chain mentality).

SELECT   Sum("SOP10200"."XTNDPRCE") XTNDPRCE, Sum("SOP10200"."QUANTITY") QUANTITY,
DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0) ID, "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
"IV00101"."ITEMDESC",AVG(CustomerBudget.BudgetAmount) as Budget
FROM   (((("dbo"."SOP10100" "SOP10100" INNER JOIN "dbo"."SOP10200" "SOP10200"
 ON "SOP10100"."SOPNUMBE"="SOP10200"."SOPNUMBE")
INNER JOIN
 "dbo"."CustomerBudget" "CustomerBudget"     ON "SOP10100"."CUSTNMBR"="CustomerBudget"."CUSTNMBR")
 LEFT OUTER JOIN "dbo"."SOP10106" "SOP10106"
  ON ("SOP10100"."SOPTYPE"="SOP10106"."SOPTYPE")
AND ("SOP10100"."SOPNUMBE"="SOP10106"."SOPNUMBE"))
INNER JOIN "dbo"."IV00101" "IV00101"
 ON "SOP10200"."ITEMNMBR"="IV00101"."ITEMNMBR")
INNER JOIN "dbo"."IV40600" "IV40600"
 ON ("IV00101"."ITMGEDSC"="IV40600"."USCATVAL")
 AND ("CustomerBudget"."USCATVAL"="IV40600"."USCATVAL")

WHERE  "CustomerBudget"."CUSTNMBR"=@CUSTNMBR
AND "SOP10100"."SOPTYPE"=1 and (SOP10100.DOCDATE between @FromDate and @ToDate)
group by DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0), "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
"IV00101"."ITEMDESC"
Order by  iv40600.UserCatLongDescr, iv00101.ITEMDESC



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.