Ambigious Column Name U_Date

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 16, 2021 at 5:16 pm

  #3964469

  ALter PROCEDURE [dbo].[test]

  @frDate date,

  @toDate date,

  @VCode nvarchar(15) = null,

  @VName nvarchar(100) = null,

  @CCode nvarchar(15) = null,

  @CName nvarchar(100) = null,

  @CGroup nvarchar(15) = null

  as

  begin

  DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''

  SET @sSQL =

  'SELECT

  T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",Max(Datename("mm",T0.U_Date)),Max(Year(T0.U_Date)),

  T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName),

  Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date",

  (SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = ''IN'' ) as "Place",

  (Select sum(Quantity) from PCH1 T where T.Docentry = T1.U_APDE and T.ItemCode = T1.U_ItemCode and T.U_BPCode = T1.U_CustCode) as "A/P Qty"

  ,Max(T5.U_CustGrp)

  FROM ORLS T0

  inner join RLS1 T1 on T0.Docentry = T1.DocEntry

  Left Join OPCH T4 on T4.DocEntry = T1.U_APDE

  Left join Ocrd T5 on t5.CardCode in (t1.U_VendCode,t1.U_CustCode) '

  SET @Where = @Where + ' T0.U_DATE BETWEEN ''2021-03-01'' and ''2021-03-31'' '

  IF @vCode is not null

  SET @Where = @Where + 'AND T1.U_VendCode = @VCode '

  IF @vName is not null

  SET @Where = @Where + 'AND T1.U_VendName = @VName '

  IF @CCode is not null

  SET @Where = @Where + 'AND T1.U_CustCode = @CCode '

  IF @CName is not null

  SET @Where = @Where + 'AND T1.U_CustName = @CName '

  IF @CGroup is not null

  SET @Where = @Where + 'AND T5.U_CustGrp = @CName '

  IF LEN(@Where) > 0

  SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3) +

  N' group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode order by T1.U_VendName,T1.U_CustName'

  EXEC sp_executesql @sSQL,

  N'@_frDate date,@_toDate date,@_VCode nvarchar(15),@_VName nvarchar(100), @_CCode nvarchar(15),@_CName nvarchar(100),@_CGroup nvarchar(15) ',

  @_frDate = @frDate,@_todate=@toDate,@_VCode = @VCode, @_VName = @VName, @_CCode = @CCode, @_CName = @CName,@_CGroup = @CGroup

  • This topic was modified 2 years, 11 months ago by  jagjitsingh.

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  December 16, 2021 at 5:23 pm

  #3964472

  As usual, you have provided a wedge of code and not asked a question. Please try not to make people play 'Guess the question' when you are posting.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ratbak

  Ten Centuries

  Points: 1358

  More actions

  December 16, 2021 at 5:37 pm

  #3964480

  Appears to be a variation on https://www.sqlservercentral.com/forums/topic/procedure-expects-parameter

  How to post code problems

   

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  December 16, 2021 at 6:37 pm

  #3964502

  jagjitsingh wrote:

  ALter PROCEDURE [dbo].[test] @frDate date, @toDate date, @VCode nvarchar(15) = null, @VName nvarchar(100) = null, @CCode nvarchar(15) = null, @CName nvarchar(100) = null, @CGroup nvarchar(15) = null as begin DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = '' SET @sSQL = 'SELECT T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",Max(Datename("mm",T0.U_Date)),Max(Year(T0.U_Date)), T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName), Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date", (SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = ''IN'' ) as "Place", (Select sum(Quantity) from PCH1 T where T.Docentry = T1.U_APDE and T.ItemCode = T1.U_ItemCode and T.U_BPCode = T1.U_CustCode) as "A/P Qty" ,Max(T5.U_CustGrp) FROM ORLS T0 inner join RLS1 T1 on T0.Docentry = T1.DocEntry Left Join OPCH T4 on T4.DocEntry = T1.U_APDE Left join Ocrd T5 on t5.CardCode in (t1.U_VendCode,t1.U_CustCode) '

  SET @Where = @Where + ' T0.U_DATE BETWEEN ''2021-03-01'' and ''2021-03-31'' ' IF @vCode is not null SET @Where = @Where + 'AND T1.U_VendCode = @VCode ' IF @vName is not null SET @Where = @Where + 'AND T1.U_VendName = @VName ' IF @CCode is not null SET @Where = @Where + 'AND T1.U_CustCode = @CCode ' IF @CName is not null SET @Where = @Where + 'AND T1.U_CustName = @CName ' IF @CGroup is not null SET @Where = @Where + 'AND T5.U_CustGrp = @CName ' IF LEN(@Where) > 0 SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3) + N' group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode order by T1.U_VendName,T1.U_CustName'

  EXEC sp_executesql @sSQL, N'@_frDate date,@_toDate date,@_VCode nvarchar(15),@_VName nvarchar(100), @_CCode nvarchar(15),@_CName nvarchar(100),@_CGroup nvarchar(15) ', @_frDate = @frDate,@_todate=@toDate,@_VCode = @VCode, @_VName = @VName, @_CCode = @CCode, @_CName = @CName,@_CGroup = @CGroup

  Print the dynamic SQL and troubleshoot that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 17, 2021 at 1:09 am

  #3964611

  Hi

  When i print above sql it shows below issues

  In where Clasue i have given T0,U_Date but it is showing U_Date

  Secondly it is giving error Must declare scalar variable @frDate

  SELECT

  T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",Max(Datename("mm",T0.U_Date)),Max(Year(T0.U_Date)),

  T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName),

  Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date",

  (SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = 'IN' ) as "Place",

  (Select sum(Quantity) from PCH1 T where T.Docentry = T1.U_APDE and T.ItemCode = T1.U_ItemCode and T.U_BPCode = T1.U_CustCode) as "A/P Qty"

  ,Max(T5.U_CustGrp)

  FROM [@IND_ORLS] T0

  inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry

  Left Join OPCH T4 on T4.DocEntry = T1.U_APDE

  Left join Ocrd T5 on t5.CardCode in (t1.U_VendCode,t1.U_CustCode) WHERE U_Date BETWEEN @frDate and @toDate group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode order by T1.U_VendName,T1.U_CustName

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  December 17, 2021 at 1:23 am

  #3964614

  So, do you see anything at all in the printed SQL that could be causing a problem?  Like maybe in the FROM clause? and the JOIN table that follows that?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 17, 2021 at 1:37 am

  #3964621

  Hi Jeff

  When i print dynamic sql it shows U_Date in where clause whereas i have given T0.U_Date. i have made bold where it gives error

  SELECT

  T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",Max(Datename("mm",T0.U_Date)),Max(Year(T0.U_Date)),

  T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName),

  Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date",

  (SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = 'IN' ) as "Place",

  (Select sum(Quantity) from PCH1 T where T.Docentry = T1.U_APDE and T.ItemCode = T1.U_ItemCode and T.U_BPCode = T1.U_CustCode) as "A/P Qty"

  ,Max(T5.U_CustGrp)

  FROM [@IND_ORLS] T0

  inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry

  Left Join OPCH T4 on T4.DocEntry = T1.U_APDE

  Left join Ocrd T5 on t5.CardCode in (t1.U_VendCode,t1.U_CustCode) WHERE U_Date between @_frDate and @_toDate AND T5.U_CustGrp = @_CGroup group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode order by T1.U_VendName,T1.U_CustName

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 17, 2021 at 1:48 am

  #3964622

  Hi Jeff

  Since i have not used T0.U_Date that's why i think it is giving error but it should not work like this

  How i can resolve it

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  December 17, 2021 at 2:16 am

  #3964625

  jagjitsingh wrote:

  How i can resolve it

  Thanks

  Your findings don't appear to match your code where you say...

  SET @Where = @Where + ' T0.U_DATE BETWEEN ''2021-03-01'' and ''2021-03-31'' '

  I don't have all of your code that you're working with so I can't duplicate your issues.

  As I stated before, one of your FROM clauses also appears to have an issue... see the @ signs?

  FROM [@IND_ORLS] T0
  inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry

  You're just going to have to keep picking at it one error at a time because there's really not enough here for me to go on.  Maybe someone else can see something else but I wouldn't do a thing until you fix the FROM with the @sign tables in them.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ratbak

  Ten Centuries

  Points: 1358

  More actions

  December 17, 2021 at 2:26 pm

  #3964797

  And the issue with from and join names was pointed out here also: https://www.sqlservercentral.com/forums/topic/procedure-expects-parameter#post-3964459

  This appears to be one of at least four different posts about the same query. Are you not understanding the responses? If so, it would help you and us if you respond in the original post, not keep creating new variations.

   

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  December 17, 2021 at 4:35 pm

  #3964872

  @jagjitsingh,

  When I run into problems like the one's you're having, I go back to the basics.

  The first step is to get out of the dynamic SQL world and get some regular code to work properly first.  Then, you can work on making it dynamic and you'll be much more successful.

  In fact... I lied a bit.  I don't run into problems like the one's you're having because I always start with working code first.  Then I convert it to dynamic SQL one parameter at a time.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)