"Greg" wrote:

> I'm trying to write the following stored proc.  The key is that I have a
> field that I'm creating that will be returned in the resultset for binding to
> a report.  This field, DueDate, has conditional logic to determine its
> calculation.  I'm getting the following error:
>
> Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue, Line
> 28
> Incorrect syntax near the keyword 'AS'.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>
> It doesn't seem to like how I'm trying to assign the result of the DATEADD
> function.  Any help or guidance is sincerely appreciated.  Thanks in advance.
>
> Greg
>
> CREATE PROCEDURE dbo.ir_ReportApprovalPastDue 
>
> @DateComp DateTime
>
> AS
>
> DECLARE
> @DayNum int
>
> SELECT
>     A.Status,
>     A.Cost,
>     A.Invoice_no,
>     A.Price_Sold,
>     IE.First_Payment,
>     IM.RFI_No,
>     IM.Customer_No,
>     IM.Invoice_Date,
>     IM.Status,
>     IM.Salesperson,
>     IM.Sales_Code,
>     M.LastName,
>     M.FirstName,
>     M.Company,
>     CASE
>         WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> IE.First_Payment) = 0) THEN
>             DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
>         ELSE IE.First_Payment AS DueDate
>     END
> FROM
>     Active_Inventory AS A,
>     Inv_Extra_Data AS IE,
>     Inv_Master AS IM,
>     ML_HCC AS M
> WHERE
>     A.Status = 'Approval' AND
>     A.Invoice_no != 0 AND
>     IM.Status = 'Appr' AND
>     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> GO
>
>
>

"Greg" wrote:

> I'm trying to write the following stored proc.  The key is that I have a
> field that I'm creating that will be returned in the resultset for binding to
> a report.  This field, DueDate, has conditional logic to determine its
> calculation.  I'm getting the following error:
>
> Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue, Line
> 28
> Incorrect syntax near the keyword 'AS'.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>
> It doesn't seem to like how I'm trying to assign the result of the DATEADD
> function.  Any help or guidance is sincerely appreciated.  Thanks in advance.
>
> Greg
>
> CREATE PROCEDURE dbo.ir_ReportApprovalPastDue 
>
> @DateComp DateTime
>
> AS
>
> DECLARE
> @DayNum int
>
> SELECT
>     A.Status,
>     A.Cost,
>     A.Invoice_no,
>     A.Price_Sold,
>     IE.First_Payment,
>     IM.RFI_No,
>     IM.Customer_No,
>     IM.Invoice_Date,
>     IM.Status,
>     IM.Salesperson,
>     IM.Sales_Code,
>     M.LastName,
>     M.FirstName,
>     M.Company,
>     CASE
>         WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> IE.First_Payment) = 0) THEN
>             DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
>         ELSE IE.First_Payment AS DueDate
>     END
> FROM
>     Active_Inventory AS A,
>     Inv_Extra_Data AS IE,
>     Inv_Master AS IM,
>     ML_HCC AS M
> WHERE
>     A.Status = 'Approval' AND
>     A.Invoice_no != 0 AND
>     IM.Status = 'Appr' AND
>     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> GO
>
>
>

"Greg" wrote:

> I'm trying to write the following stored proc.  The key is that I have a
> field that I'm creating that will be returned in the resultset for binding
> to
> a report.  This field, DueDate, has conditional logic to determine its
> calculation.  I'm getting the following error:
>
> Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue,
> Line
> 28
> Incorrect syntax near the keyword 'AS'.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>
> It doesn't seem to like how I'm trying to assign the result of the DATEADD
> function.  Any help or guidance is sincerely appreciated.  Thanks in
> advance.
>
> Greg
>
> CREATE PROCEDURE dbo.ir_ReportApprovalPastDue
>
> @DateComp DateTime
>
> AS
>
> DECLARE
> @DayNum int
>
> SELECT
> A.Status,
> A.Cost,
> A.Invoice_no,
> A.Price_Sold,
> IE.First_Payment,
> IM.RFI_No,
> IM.Customer_No,
> IM.Invoice_Date,
> IM.Status,
> IM.Salesperson,
> IM.Sales_Code,
> M.LastName,
> M.FirstName,
> M.Company,
> CASE
> WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> IE.First_Payment) = 0) THEN
> DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
> ELSE IE.First_Payment AS DueDate
> END
> FROM
> Active_Inventory AS A,
> Inv_Extra_Data AS IE,
> Inv_Master AS IM,
> ML_HCC AS M
> WHERE
> A.Status = 'Approval' AND
> A.Invoice_no != 0 AND
> IM.Status = 'Appr' AND
> DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> GO
>
>
>

"Tom Moreau" wrote:

> You cannot refer to a column that was computed in your SELECT list, except
> in the ORDER BY clause.  You can use a derived table, though.  That said,
> why are you converting a datetime to an int and then to a datetime:
>
> DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
>
>
>
> --
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada
> www.pinpub.com
> ..
> "Greg" <greg_bu***@hotmail.com.net> wrote in message
> news:3F69720B-75F2-4BE3-ABDF-67D5F6CB71D2@microsoft.com...
> Thanks for the help guys!  I made the changes you suggested and it passes
> the
> syntax check; but, I'm now getting the following error:
>
> Server: Msg 207, Level 16, State 3, Procedure ir_ReportApprovalPastDueTest3,
> Line 10
> Invalid column name 'DueDate'.
>
> What am I doing wrong?  Any help or guidance is much appreciated.  Thanks in
> advance.
>
> CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
>
> @DateComp DateTime
>
> AS
>
> DECLARE
> @DayNum int
>
> SELECT
> A.Status,
> A.Cost,
> A.Invoice_no,
> A.Price_Sold,
> IE.First_Payment,
> IM.RFI_No,
> IM.Customer_No,
> IM.Invoice_Date,
> IM.Status,
> IM.Salesperson,
> IM.Sales_Code,
> M.LastName,
> M.FirstName,
> M.Company,
> CASE
> WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> GETDATE(),IE.First_Payment) = 0) THEN
> DATEADD(dd, 30, IM.Invoice_Date)
> ELSE IE.First_Payment
> END AS DueDate
>
> FROM
> Active_Inventory AS A,
> Inv_Extra_Data AS IE,
> Inv_Master AS IM,
> ML_HCC AS M
> WHERE
> A.Status = 'Approval' AND
> A.Invoice_no != 0 AND
> IM.Status = 'Appr' AND
> DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> GO
>
>
>
>
> "Greg" wrote:
>
> > I'm trying to write the following stored proc.  The key is that I have a
> > field that I'm creating that will be returned in the resultset for binding
> > to
> > a report.  This field, DueDate, has conditional logic to determine its
> > calculation.  I'm getting the following error:
> >
> > Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue,
> > Line
> > 28
> > Incorrect syntax near the keyword 'AS'.
> > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> >
> > It doesn't seem to like how I'm trying to assign the result of the DATEADD
> > function.  Any help or guidance is sincerely appreciated.  Thanks in
> > advance.
> >
> > Greg
> >
> > CREATE PROCEDURE dbo.ir_ReportApprovalPastDue
> >
> > @DateComp DateTime
> >
> > AS
> >
> > DECLARE
> > @DayNum int
> >
> > SELECT
> > A.Status,
> > A.Cost,
> > A.Invoice_no,
> > A.Price_Sold,
> > IE.First_Payment,
> > IM.RFI_No,
> > IM.Customer_No,
> > IM.Invoice_Date,
> > IM.Status,
> > IM.Salesperson,
> > IM.Sales_Code,
> > M.LastName,
> > M.FirstName,
> > M.Company,
> > CASE
> > WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> > IE.First_Payment) = 0) THEN
> > DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
> > ELSE IE.First_Payment AS DueDate
> > END
> > FROM
> > Active_Inventory AS A,
> > Inv_Extra_Data AS IE,
> > Inv_Master AS IM,
> > ML_HCC AS M
> > WHERE
> > A.Status = 'Approval' AND
> > A.Invoice_no != 0 AND
> > IM.Status = 'Appr' AND
> > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > GO
> >
> >
> >
>
>

"Tom Moreau" wrote:

> You cannot refer to a column that was computed in your SELECT list, except
> in the ORDER BY clause.  You can use a derived table, though.  That said,
> why are you converting a datetime to an int and then to a datetime:
>
> DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
>
>
>
> --
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada
> www.pinpub.com
> ..
> "Greg" <greg_bu***@hotmail.com.net> wrote in message
> news:3F69720B-75F2-4BE3-ABDF-67D5F6CB71D2@microsoft.com...
> Thanks for the help guys!  I made the changes you suggested and it passes
> the
> syntax check; but, I'm now getting the following error:
>
> Server: Msg 207, Level 16, State 3, Procedure
> ir_ReportApprovalPastDueTest3,
> Line 10
> Invalid column name 'DueDate'.
>
> What am I doing wrong?  Any help or guidance is much appreciated.  Thanks
> in
> advance.
>
> CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
>
> @DateComp DateTime
>
> AS
>
> DECLARE
> @DayNum int
>
> SELECT
> A.Status,
> A.Cost,
> A.Invoice_no,
> A.Price_Sold,
> IE.First_Payment,
> IM.RFI_No,
> IM.Customer_No,
> IM.Invoice_Date,
> IM.Status,
> IM.Salesperson,
> IM.Sales_Code,
> M.LastName,
> M.FirstName,
> M.Company,
> CASE
> WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> GETDATE(),IE.First_Payment) = 0) THEN
> DATEADD(dd, 30, IM.Invoice_Date)
> ELSE IE.First_Payment
> END AS DueDate
>
> FROM
> Active_Inventory AS A,
> Inv_Extra_Data AS IE,
> Inv_Master AS IM,
> ML_HCC AS M
> WHERE
> A.Status = 'Approval' AND
> A.Invoice_no != 0 AND
> IM.Status = 'Appr' AND
> DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> GO
>
>
>
>
> "Greg" wrote:
>
> > I'm trying to write the following stored proc.  The key is that I have a
> > field that I'm creating that will be returned in the resultset for
> > binding
> > to
> > a report.  This field, DueDate, has conditional logic to determine its
> > calculation.  I'm getting the following error:
> >
> > Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue,
> > Line
> > 28
> > Incorrect syntax near the keyword 'AS'.
> > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> >
> > It doesn't seem to like how I'm trying to assign the result of the
> > DATEADD
> > function.  Any help or guidance is sincerely appreciated.  Thanks in
> > advance.
> >
> > Greg
> >
> > CREATE PROCEDURE dbo.ir_ReportApprovalPastDue
> >
> > @DateComp DateTime
> >
> > AS
> >
> > DECLARE
> > @DayNum int
> >
> > SELECT
> > A.Status,
> > A.Cost,
> > A.Invoice_no,
> > A.Price_Sold,
> > IE.First_Payment,
> > IM.RFI_No,
> > IM.Customer_No,
> > IM.Invoice_Date,
> > IM.Status,
> > IM.Salesperson,
> > IM.Sales_Code,
> > M.LastName,
> > M.FirstName,
> > M.Company,
> > CASE
> > WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> > IE.First_Payment) = 0) THEN
> > DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
> > ELSE IE.First_Payment AS DueDate
> > END
> > FROM
> > Active_Inventory AS A,
> > Inv_Extra_Data AS IE,
> > Inv_Master AS IM,
> > ML_HCC AS M
> > WHERE
> > A.Status = 'Approval' AND
> > A.Invoice_no != 0 AND
> > IM.Status = 'Appr' AND
> > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > GO
> >
> >
> >
>
>

"Tom Moreau" wrote:

> Try:
>
> SELECT
>     *
> FROM
> (
> SELECT
> A.Status,
> A.Cost,
> A.Invoice_no,
> A.Price_Sold,
> IE.First_Payment,
> IM.RFI_No,
> IM.Customer_No,
> IM.Invoice_Date,
> IM.Status,
> IM.Salesperson,
> IM.Sales_Code,
> M.LastName,
> M.FirstName,
> M.Company,
> CASE
> WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> GETDATE(),IE.First_Payment) = 0) THEN
> DATEADD(dd, 30, IM.Invoice_Date)
> ELSE IE.First_Payment
> END AS DueDate
>
> FROM
> Active_Inventory AS A,
> Inv_Extra_Data AS IE,
> Inv_Master AS IM,
> ML_HCC AS M
> WHERE
> A.Status = 'Approval' AND
> A.Invoice_no != 0 AND
> IM.Status = 'Appr'
> ) as x
> WHERE
>     DueDate <= whatever
>
> --
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada
> www.pinpub.com
> ..
> "Greg" <greg_bu***@hotmail.com.net> wrote in message
> news:A5D52D2A-981C-4AFF-8B6B-E100620B030C@microsoft.com...
> Thanks for the tip.  How would I do that with the derived table?  Also, I'm
> not sure why I did what I did with the Convert function -- I should probably
> just simplify it down.  Thanks in advance for any help you can provide.
>
> "Tom Moreau" wrote:
>
> > You cannot refer to a column that was computed in your SELECT list, except
> > in the ORDER BY clause.  You can use a derived table, though.  That said,
> > why are you converting a datetime to an int and then to a datetime:
> >
> > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> >
> >
> >
> > --
> >    Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON   Canada
> > www.pinpub.com
> > ..
> > "Greg" <greg_bu***@hotmail.com.net> wrote in message
> > news:3F69720B-75F2-4BE3-ABDF-67D5F6CB71D2@microsoft.com...
> > Thanks for the help guys!  I made the changes you suggested and it passes
> > the
> > syntax check; but, I'm now getting the following error:
> >
> > Server: Msg 207, Level 16, State 3, Procedure
> > ir_ReportApprovalPastDueTest3,
> > Line 10
> > Invalid column name 'DueDate'.
> >
> > What am I doing wrong?  Any help or guidance is much appreciated.  Thanks
> > in
> > advance.
> >
> > CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
> >
> > @DateComp DateTime
> >
> > AS
> >
> > DECLARE
> > @DayNum int
> >
> > SELECT
> > A.Status,
> > A.Cost,
> > A.Invoice_no,
> > A.Price_Sold,
> > IE.First_Payment,
> > IM.RFI_No,
> > IM.Customer_No,
> > IM.Invoice_Date,
> > IM.Status,
> > IM.Salesperson,
> > IM.Sales_Code,
> > M.LastName,
> > M.FirstName,
> > M.Company,
> > CASE
> > WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> > GETDATE(),IE.First_Payment) = 0) THEN
> > DATEADD(dd, 30, IM.Invoice_Date)
> > ELSE IE.First_Payment
> > END AS DueDate
> >
> > FROM
> > Active_Inventory AS A,
> > Inv_Extra_Data AS IE,
> > Inv_Master AS IM,
> > ML_HCC AS M
> > WHERE
> > A.Status = 'Approval' AND
> > A.Invoice_no != 0 AND
> > IM.Status = 'Appr' AND
> > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > GO
> >
> >
> >
> >
> > "Greg" wrote:
> >
> > > I'm trying to write the following stored proc.  The key is that I have a
> > > field that I'm creating that will be returned in the resultset for
> > > binding
> > > to
> > > a report.  This field, DueDate, has conditional logic to determine its
> > > calculation.  I'm getting the following error:
> > >
> > > Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue,
> > > Line
> > > 28
> > > Incorrect syntax near the keyword 'AS'.
> > > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> > >
> > > It doesn't seem to like how I'm trying to assign the result of the
> > > DATEADD
> > > function.  Any help or guidance is sincerely appreciated.  Thanks in
> > > advance.
> > >
> > > Greg
> > >
> > > CREATE PROCEDURE dbo.ir_ReportApprovalPastDue
> > >
> > > @DateComp DateTime
> > >
> > > AS
> > >
> > > DECLARE
> > > @DayNum int
> > >
> > > SELECT
> > > A.Status,
> > > A.Cost,
> > > A.Invoice_no,
> > > A.Price_Sold,
> > > IE.First_Payment,
> > > IM.RFI_No,
> > > IM.Customer_No,
> > > IM.Invoice_Date,
> > > IM.Status,
> > > IM.Salesperson,
> > > IM.Sales_Code,
> > > M.LastName,
> > > M.FirstName,
> > > M.Company,
> > > CASE
> > > WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> > > IE.First_Payment) = 0) THEN
> > > DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
> > > ELSE IE.First_Payment AS DueDate
> > > END
> > > FROM
> > > Active_Inventory AS A,
> > > Inv_Extra_Data AS IE,
> > > Inv_Master AS IM,
> > > ML_HCC AS M
> > > WHERE
> > > A.Status = 'Approval' AND
> > > A.Invoice_no != 0 AND
> > > IM.Status = 'Appr' AND
> > > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > > GO
> > >
> > >
> > >
> >
> >
>
>

"Greg" <greg_bu***@hotmail.com.net> wrote in message
news:5CDC9766-189B-4038-9F0B-6701B4B912E4@microsoft.com...
> Tom,
>
> Thanks for the tip.  I tried the query you sent me and it and it gave back
> the following error when I attempted to execute it:
>
> Server: Msg 8156, Level 16, State 1, Line 1
> The column 'Status' was specified multiple times for 'x'.
>
> Here's the SQL I attempted to execute:
>
> SELECT
>    *
> FROM
> (
> SELECT
> A.Status,
> A.Cost,
> A.Invoice_no,
> A.Price_Sold,
> IE.First_Payment,
> IM.RFI_No,
> IM.Customer_No,
> IM.Invoice_Date,
> IM.Status,
> IM.Salesperson,
> IM.Sales_Code,
> M.LastName,
> M.FirstName,
> M.Company,
> CASE
> WHEN (IE.First_Payment IS NOT NULL) OR
> (DATEDIFF(dd,GETDATE(),IE.First_Payment) = 0) THEN
> DATEADD(dd, 30, IM.Invoice_Date)
> ELSE IE.First_Payment
> END AS DueDate
>
> FROM
> Active_Inventory AS A,
> Inv_Extra_Data AS IE,
> Inv_Master AS IM,
> ML_HCC AS M
> WHERE
> A.Status = 'Approval' AND
> A.Invoice_no != 0 AND
> IM.Status = 'Appr'
> ) AS x
> WHERE
>    DueDate <= '1/1/2005'
>
> Any ideas?  I'm fairly new at this more advanced SQL and appreciate your
> help.  Thanks.
>
> "Tom Moreau" wrote:
>
>> Try:
>>
>> SELECT
>>     *
>> FROM
>> (
>> SELECT
>> A.Status,
>> A.Cost,
>> A.Invoice_no,
>> A.Price_Sold,
>> IE.First_Payment,
>> IM.RFI_No,
>> IM.Customer_No,
>> IM.Invoice_Date,
>> IM.Status,
>> IM.Salesperson,
>> IM.Sales_Code,
>> M.LastName,
>> M.FirstName,
>> M.Company,
>> CASE
>> WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
>> GETDATE(),IE.First_Payment) = 0) THEN
>> DATEADD(dd, 30, IM.Invoice_Date)
>> ELSE IE.First_Payment
>> END AS DueDate
>>
>> FROM
>> Active_Inventory AS A,
>> Inv_Extra_Data AS IE,
>> Inv_Master AS IM,
>> ML_HCC AS M
>> WHERE
>> A.Status = 'Approval' AND
>> A.Invoice_no != 0 AND
>> IM.Status = 'Appr'
>> ) as x
>> WHERE
>>     DueDate <= whatever
>>
>> --
>>    Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON   Canada
>> www.pinpub.com
>> ..
>> "Greg" <greg_bu***@hotmail.com.net> wrote in message
>> news:A5D52D2A-981C-4AFF-8B6B-E100620B030C@microsoft.com...
>> Thanks for the tip.  How would I do that with the derived table?  Also,
>> I'm
>> not sure why I did what I did with the Convert function -- I should
>> probably
>> just simplify it down.  Thanks in advance for any help you can provide.
>>
>> "Tom Moreau" wrote:
>>
>> > You cannot refer to a column that was computed in your SELECT list,
>> > except
>> > in the ORDER BY clause.  You can use a derived table, though.  That
>> > said,
>> > why are you converting a datetime to an int and then to a datetime:
>> >
>> > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
>> >
>> >
>> >
>> > --
>> >    Tom
>> >
>> > ----------------------------------------------------
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Columnist, SQL Server Professional
>> > Toronto, ON   Canada
>> > www.pinpub.com
>> > ..
>> > "Greg" <greg_bu***@hotmail.com.net> wrote in message
>> > news:3F69720B-75F2-4BE3-ABDF-67D5F6CB71D2@microsoft.com...
>> > Thanks for the help guys!  I made the changes you suggested and it
>> > passes
>> > the
>> > syntax check; but, I'm now getting the following error:
>> >
>> > Server: Msg 207, Level 16, State 3, Procedure
>> > ir_ReportApprovalPastDueTest3,
>> > Line 10
>> > Invalid column name 'DueDate'.
>> >
>> > What am I doing wrong?  Any help or guidance is much appreciated.
>> > Thanks
>> > in
>> > advance.
>> >
>> > CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
>> >
>> > @DateComp DateTime
>> >
>> > AS
>> >
>> > DECLARE
>> > @DayNum int
>> >
>> > SELECT
>> > A.Status,
>> > A.Cost,
>> > A.Invoice_no,
>> > A.Price_Sold,
>> > IE.First_Payment,
>> > IM.RFI_No,
>> > IM.Customer_No,
>> > IM.Invoice_Date,
>> > IM.Status,
>> > IM.Salesperson,
>> > IM.Sales_Code,
>> > M.LastName,
>> > M.FirstName,
>> > M.Company,
>> > CASE
>> > WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
>> > GETDATE(),IE.First_Payment) = 0) THEN
>> > DATEADD(dd, 30, IM.Invoice_Date)
>> > ELSE IE.First_Payment
>> > END AS DueDate
>> >
>> > FROM
>> > Active_Inventory AS A,
>> > Inv_Extra_Data AS IE,
>> > Inv_Master AS IM,
>> > ML_HCC AS M
>> > WHERE
>> > A.Status = 'Approval' AND
>> > A.Invoice_no != 0 AND
>> > IM.Status = 'Appr' AND
>> > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
>> > GO
>> >
>> >
>> >
>> >
>> > "Greg" wrote:
>> >
>> > > I'm trying to write the following stored proc.  The key is that I
>> > > have a
>> > > field that I'm creating that will be returned in the resultset for
>> > > binding
>> > > to
>> > > a report.  This field, DueDate, has conditional logic to determine
>> > > its
>> > > calculation.  I'm getting the following error:
>> > >
>> > > Server: Msg 156, Level 15, State 1, Procedure
>> > > ir_ReportApprovalPastDue,
>> > > Line
>> > > 28
>> > > Incorrect syntax near the keyword 'AS'.
>> > > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>> > >
>> > > It doesn't seem to like how I'm trying to assign the result of the
>> > > DATEADD
>> > > function.  Any help or guidance is sincerely appreciated.  Thanks in
>> > > advance.
>> > >
>> > > Greg
>> > >
>> > > CREATE PROCEDURE dbo.ir_ReportApprovalPastDue
>> > >
>> > > @DateComp DateTime
>> > >
>> > > AS
>> > >
>> > > DECLARE
>> > > @DayNum int
>> > >
>> > > SELECT
>> > > A.Status,
>> > > A.Cost,
>> > > A.Invoice_no,
>> > > A.Price_Sold,
>> > > IE.First_Payment,
>> > > IM.RFI_No,
>> > > IM.Customer_No,
>> > > IM.Invoice_Date,
>> > > IM.Status,
>> > > IM.Salesperson,
>> > > IM.Sales_Code,
>> > > M.LastName,
>> > > M.FirstName,
>> > > M.Company,
>> > > CASE
>> > > WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
>> > > IE.First_Payment) = 0) THEN
>> > > DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
>> > > ELSE IE.First_Payment AS DueDate
>> > > END
>> > > FROM
>> > > Active_Inventory AS A,
>> > > Inv_Extra_Data AS IE,
>> > > Inv_Master AS IM,
>> > > ML_HCC AS M
>> > > WHERE
>> > > A.Status = 'Approval' AND
>> > > A.Invoice_no != 0 AND
>> > > IM.Status = 'Appr' AND
>> > > DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
>> > > GO
>> > >
>> > >
>> > >
>> >
>> >
>>
>>

"Greg" wrote:

> Thanks for the help guys!  I made the changes you suggested and it passes the
> syntax check; but, I'm now getting the following error:
>
> Server: Msg 207, Level 16, State 3, Procedure ir_ReportApprovalPastDueTest3,
> Line 10
> Invalid column name 'DueDate'.
>
> What am I doing wrong?  Any help or guidance is much appreciated.  Thanks in
> advance.
>
> CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
>
> @DateComp DateTime
>
> AS
>
> DECLARE
> @DayNum int
>
> SELECT
>     A.Status,
>     A.Cost,
>     A.Invoice_no,
>     A.Price_Sold,
>     IE.First_Payment,
>     IM.RFI_No,
>     IM.Customer_No,
>     IM.Invoice_Date,
>     IM.Status,
>     IM.Salesperson,
>     IM.Sales_Code,
>     M.LastName,
>     M.FirstName,
>     M.Company,
>     CASE
>         WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> GETDATE(),IE.First_Payment) = 0) THEN
>             DATEADD(dd, 30, IM.Invoice_Date)
>         ELSE IE.First_Payment
>     END AS DueDate
>
> FROM
>     Active_Inventory AS A,
>     Inv_Extra_Data AS IE,
>     Inv_Master AS IM,
>     ML_HCC AS M
> WHERE
>     A.Status = 'Approval' AND
>     A.Invoice_no != 0 AND
>     IM.Status = 'Appr' AND
>     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> GO
>
>
>
>
> "Greg" wrote:
>
> > I'm trying to write the following stored proc.  The key is that I have a
> > field that I'm creating that will be returned in the resultset for binding to
> > a report.  This field, DueDate, has conditional logic to determine its
> > calculation.  I'm getting the following error:
> >
> > Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue, Line
> > 28
> > Incorrect syntax near the keyword 'AS'.
> > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> >
> > It doesn't seem to like how I'm trying to assign the result of the DATEADD
> > function.  Any help or guidance is sincerely appreciated.  Thanks in advance.
> >
> > Greg
> >
> > CREATE PROCEDURE dbo.ir_ReportApprovalPastDue 
> >
> > @DateComp DateTime
> >
> > AS
> >
> > DECLARE
> > @DayNum int
> >
> > SELECT
> >     A.Status,
> >     A.Cost,
> >     A.Invoice_no,
> >     A.Price_Sold,
> >     IE.First_Payment,
> >     IM.RFI_No,
> >     IM.Customer_No,
> >     IM.Invoice_Date,
> >     IM.Status,
> >     IM.Salesperson,
> >     IM.Sales_Code,
> >     M.LastName,
> >     M.FirstName,
> >     M.Company,
> >     CASE
> >         WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> > IE.First_Payment) = 0) THEN
> >             DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
> >         ELSE IE.First_Payment AS DueDate
> >     END
> > FROM
> >     Active_Inventory AS A,
> >     Inv_Extra_Data AS IE,
> >     Inv_Master AS IM,
> >     ML_HCC AS M
> > WHERE
> >     A.Status = 'Approval' AND
> >     A.Invoice_no != 0 AND
> >     IM.Status = 'Appr' AND
> >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > GO
> >
> >
> >

"Alejandro Mesa" wrote:

> You can not reference a column alias in the "where" clause of the "select"
> statement where you are creating it. You have to use the expression that you
> are aliasing (sorry if this is not the correct verb) .
>
> >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
>
> CASE
> WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd, >
> GETDATE(),IE.First_Payment) = 0) THEN DATEADD(dd, 30, IM.Invoice_Date)
> ELSE IE.First_Payment
> END <= CONVERT(datetime, CONVERT(int, GETDATE()))
>
>
> BTW, I did not recommended to change "IE.First_Payment = NULL" by
> "IE.First_Payment IS NOT NULL". What I said or tried was to use the pattern:
>
> expression is [not] null
>
> "is null" or "is not null", depend what you want. In your case, it can be:
>
> IE.First_Payment IS NULL
>
>
> AMB
>
> "Greg" wrote:
>
> > Thanks for the help guys!  I made the changes you suggested and it passes the
> > syntax check; but, I'm now getting the following error:
> >
> > Server: Msg 207, Level 16, State 3, Procedure ir_ReportApprovalPastDueTest3,
> > Line 10
> > Invalid column name 'DueDate'.
> >
> > What am I doing wrong?  Any help or guidance is much appreciated.  Thanks in
> > advance.
> >
> > CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
> >
> > @DateComp DateTime
> >
> > AS
> >
> > DECLARE
> > @DayNum int
> >
> > SELECT
> >     A.Status,
> >     A.Cost,
> >     A.Invoice_no,
> >     A.Price_Sold,
> >     IE.First_Payment,
> >     IM.RFI_No,
> >     IM.Customer_No,
> >     IM.Invoice_Date,
> >     IM.Status,
> >     IM.Salesperson,
> >     IM.Sales_Code,
> >     M.LastName,
> >     M.FirstName,
> >     M.Company,
> >     CASE
> >         WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> > GETDATE(),IE.First_Payment) = 0) THEN
> >             DATEADD(dd, 30, IM.Invoice_Date)
> >         ELSE IE.First_Payment
> >     END AS DueDate
> >
> > FROM
> >     Active_Inventory AS A,
> >     Inv_Extra_Data AS IE,
> >     Inv_Master AS IM,
> >     ML_HCC AS M
> > WHERE
> >     A.Status = 'Approval' AND
> >     A.Invoice_no != 0 AND
> >     IM.Status = 'Appr' AND
> >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > GO
> >
> >
> >
> >
> > "Greg" wrote:
> >
> > > I'm trying to write the following stored proc.  The key is that I have a
> > > field that I'm creating that will be returned in the resultset for binding to
> > > a report.  This field, DueDate, has conditional logic to determine its
> > > calculation.  I'm getting the following error:
> > >
> > > Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue, Line
> > > 28
> > > Incorrect syntax near the keyword 'AS'.
> > > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> > >
> > > It doesn't seem to like how I'm trying to assign the result of the DATEADD
> > > function.  Any help or guidance is sincerely appreciated.  Thanks in advance.
> > >
> > > Greg
> > >
> > > CREATE PROCEDURE dbo.ir_ReportApprovalPastDue 
> > >
> > > @DateComp DateTime
> > >
> > > AS
> > >
> > > DECLARE
> > > @DayNum int
> > >
> > > SELECT
> > >     A.Status,
> > >     A.Cost,
> > >     A.Invoice_no,
> > >     A.Price_Sold,
> > >     IE.First_Payment,
> > >     IM.RFI_No,
> > >     IM.Customer_No,
> > >     IM.Invoice_Date,
> > >     IM.Status,
> > >     IM.Salesperson,
> > >     IM.Sales_Code,
> > >     M.LastName,
> > >     M.FirstName,
> > >     M.Company,
> > >     CASE
> > >         WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> > > IE.First_Payment) = 0) THEN
> > >             DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
> > >         ELSE IE.First_Payment AS DueDate
> > >     END
> > > FROM
> > >     Active_Inventory AS A,
> > >     Inv_Extra_Data AS IE,
> > >     Inv_Master AS IM,
> > >     ML_HCC AS M
> > > WHERE
> > >     A.Status = 'Approval' AND
> > >     A.Invoice_no != 0 AND
> > >     IM.Status = 'Appr' AND
> > >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > > GO
> > >
> > >
> > >

> > > CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
> > >
> > > @DateComp DateTime
> > >
> > > AS
> > >
> > > DECLARE
> > > @DayNum int
> > >
> > > SELECT
> > >     A.Status,
> > >     A.Cost,
> > >     A.Invoice_no,
> > >     A.Price_Sold,
> > >     IE.First_Payment,
> > >     IM.RFI_No,
> > >     IM.Customer_No,
> > >     IM.Invoice_Date,
> > >     IM.Status,
> > >     IM.Salesperson,
> > >     IM.Sales_Code,
> > >     M.LastName,
> > >     M.FirstName,
> > >     M.Company,
> > >     CASE
> > >         WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> > > GETDATE(),IE.First_Payment) = 0) THEN
> > >             DATEADD(dd, 30, IM.Invoice_Date)
> > >         ELSE IE.First_Payment
> > >     END AS DueDate
> > >
> > > FROM
> > >     Active_Inventory AS A,
> > >     Inv_Extra_Data AS IE,
> > >     Inv_Master AS IM,
> > >     ML_HCC AS M
> > > WHERE
> > >     A.Status = 'Approval' AND
> > >     A.Invoice_no != 0 AND
> > >     IM.Status = 'Appr' AND
> > >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > > GO

"Greg" wrote:

> Thanks for the help.  Do I substitue this conditional clause for the table
> alias in the WHERE clause?  I'm not quite sure what to do here.  Thanks in
> advance for getting me over the hump here.
>
> "Alejandro Mesa" wrote:
>
> > You can not reference a column alias in the "where" clause of the "select"
> > statement where you are creating it. You have to use the expression that you
> > are aliasing (sorry if this is not the correct verb) .
> >
> > >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> >
> > CASE
> > WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd, >
> > GETDATE(),IE.First_Payment) = 0) THEN DATEADD(dd, 30, IM.Invoice_Date)
> > ELSE IE.First_Payment
> > END <= CONVERT(datetime, CONVERT(int, GETDATE()))
> >
> >
> > BTW, I did not recommended to change "IE.First_Payment = NULL" by
> > "IE.First_Payment IS NOT NULL". What I said or tried was to use the pattern:
> >
> > expression is [not] null
> >
> > "is null" or "is not null", depend what you want. In your case, it can be:
> >
> > IE.First_Payment IS NULL
> >
> >
> > AMB
> >
> > "Greg" wrote:
> >
> > > Thanks for the help guys!  I made the changes you suggested and it passes the
> > > syntax check; but, I'm now getting the following error:
> > >
> > > Server: Msg 207, Level 16, State 3, Procedure ir_ReportApprovalPastDueTest3,
> > > Line 10
> > > Invalid column name 'DueDate'.
> > >
> > > What am I doing wrong?  Any help or guidance is much appreciated.  Thanks in
> > > advance.
> > >
> > > CREATE PROCEDURE dbo.ir_ReportApprovalPastDueTest3
> > >
> > > @DateComp DateTime
> > >
> > > AS
> > >
> > > DECLARE
> > > @DayNum int
> > >
> > > SELECT
> > >     A.Status,
> > >     A.Cost,
> > >     A.Invoice_no,
> > >     A.Price_Sold,
> > >     IE.First_Payment,
> > >     IM.RFI_No,
> > >     IM.Customer_No,
> > >     IM.Invoice_Date,
> > >     IM.Status,
> > >     IM.Salesperson,
> > >     IM.Sales_Code,
> > >     M.LastName,
> > >     M.FirstName,
> > >     M.Company,
> > >     CASE
> > >         WHEN (IE.First_Payment IS NOT NULL) OR (DATEDIFF(dd,
> > > GETDATE(),IE.First_Payment) = 0) THEN
> > >             DATEADD(dd, 30, IM.Invoice_Date)
> > >         ELSE IE.First_Payment
> > >     END AS DueDate
> > >
> > > FROM
> > >     Active_Inventory AS A,
> > >     Inv_Extra_Data AS IE,
> > >     Inv_Master AS IM,
> > >     ML_HCC AS M
> > > WHERE
> > >     A.Status = 'Approval' AND
> > >     A.Invoice_no != 0 AND
> > >     IM.Status = 'Appr' AND
> > >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > > GO
> > >
> > >
> > >
> > >
> > > "Greg" wrote:
> > >
> > > > I'm trying to write the following stored proc.  The key is that I have a
> > > > field that I'm creating that will be returned in the resultset for binding to
> > > > a report.  This field, DueDate, has conditional logic to determine its
> > > > calculation.  I'm getting the following error:
> > > >
> > > > Server: Msg 156, Level 15, State 1, Procedure ir_ReportApprovalPastDue, Line
> > > > 28
> > > > Incorrect syntax near the keyword 'AS'.
> > > > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> > > >
> > > > It doesn't seem to like how I'm trying to assign the result of the DATEADD
> > > > function.  Any help or guidance is sincerely appreciated.  Thanks in advance.
> > > >
> > > > Greg
> > > >
> > > > CREATE PROCEDURE dbo.ir_ReportApprovalPastDue 
> > > >
> > > > @DateComp DateTime
> > > >
> > > > AS
> > > >
> > > > DECLARE
> > > > @DayNum int
> > > >
> > > > SELECT
> > > >     A.Status,
> > > >     A.Cost,
> > > >     A.Invoice_no,
> > > >     A.Price_Sold,
> > > >     IE.First_Payment,
> > > >     IM.RFI_No,
> > > >     IM.Customer_No,
> > > >     IM.Invoice_Date,
> > > >     IM.Status,
> > > >     IM.Salesperson,
> > > >     IM.Sales_Code,
> > > >     M.LastName,
> > > >     M.FirstName,
> > > >     M.Company,
> > > >     CASE
> > > >         WHEN (IE.First_Payment = NULL) OR (DATEDIFF(dd, GETDATE(),
> > > > IE.First_Payment) = 0) THEN
> > > >             DATEADD(Day, 30, IM.Invoice_Date) AS DueDate
> > > >         ELSE IE.First_Payment AS DueDate
> > > >     END
> > > > FROM
> > > >     Active_Inventory AS A,
> > > >     Inv_Extra_Data AS IE,
> > > >     Inv_Master AS IM,
> > > >     ML_HCC AS M
> > > > WHERE
> > > >     A.Status = 'Approval' AND
> > > >     A.Invoice_no != 0 AND
> > > >     IM.Status = 'Appr' AND
> > > >     DueDate <= CONVERT(datetime, CONVERT(int, GETDATE()))
> > > > GO
> > > >
> > > >
> > > >