Solved

Cannot see the error in this can anyone lend tips on how to troubleshoot SQL.

Posted on 2013-10-25
11
294 Views
Last Modified: 2013-10-26
Spent the last hour trying to find the error.....?

Msg 170, Level 15, State 1, Procedure Rpt_SnyderAccount_PL_Summary_RB, Line 40
Line 40: Incorrect syntax near '.'.


USE [WebSnakDS_SNYB_Test]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SnyderAccountP&LSummary_RB]    Script Date: 10/22/2013 11:47:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Declare 
	ALTER PROCEDURE [dbo].[Rpt_SnyderAccount_PL_Summary_RB] 
	(
	@DB_Include			int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 

	) AS
-----------------------------------------------------------------------------------
-- Creating TEMP Table Bringing Macola RAMS and WebSNAK together
-----------------------------------------------------------------------------------

CREATE TABLE #TEMPTABLE
		
(
	[Data_Source] VARCHAR(12),--  what database is the data coming from  macola or rams?
	[InvoiceDate] DATETIME,
	[SNAK_SaleTypesID] CHAR (1),
	[CustPromoAmt] MONEY,
	[MFGCostAmt] MONEY, 
	[GrossAmt] MONEY,
	[CustName] VARCHAR(30),
	[SNAK_CustomerID] VARCHAR(8),
	[CHAINNAME] VARCHAR(30),
	[SNAK_CHAINID] VARCHAR(10)
	
	
)

 
-----------------------------------------------------------------------------------
-- WEBSNAK DATA
-----------------------------------------------------------------------------------

if (@DB_Include = 0 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
insert  INTO #TEMPTABLE

SELECT
	'WEBSNAK'
	Invoices.InvoiceDate,
	InvoiceDetails.SNAK_SaleTypesID, 
	InvoiceDetails.CustPromoAmt,
	InvoiceDetails.MfgCostAmt,
	InvoiceDetails.GrossAmt,
	Customers.CustName, 
	Customers.SNAK_CustomerID,
	Chains.ChainName, 
	Chains.SNAK_ChainID
FROM
    { oj ((dbo.Invoices Invoices 
     INNER JOIN dbo.Customers Customers 
			ON	Invoices.Customers_Syskey = Customers.Customers_Syskey)
     INNER JOIN dbo.InvoiceDetails InvoiceDetails 
			ON	Invoices.Invoices_Syskey = InvoiceDetails.Invoices_Syskey)
     INNER JOIN dbo.Chains Chains 
			ON	Customers.Chains_Syskey = Chains.Chains_Syskey}

Open in new window

0
Comment
Question by:ruavol2
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 200 total points
Comment Utility
Try removing all the parens from around the FROM clause:

FROM
    dbo.Invoices Invoices 
     INNER JOIN dbo.Customers Customers 
	 ON	Invoices.Customers_Syskey = Customers.Customers_Syskey
     INNER JOIN dbo.InvoiceDetails InvoiceDetails 
	 ON	Invoices.Invoices_Syskey = InvoiceDetails.Invoices_Syskey
     INNER JOIN dbo.Chains Chains 
	 ON	Customers.Chains_Syskey = Chains.Chains_Syskey

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
Comment Utility
>    { oj ((dbo.Invoices Invoices
What's the squiggly left parenthesis mark doing there?   Delete all parentheses, squiggly or otherwise, in your FROM .. JOIN block.

>      'WEBSNAK'
Missing a comma here
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
Comment Utility
>insert  INTO #TEMPTABLE
Also it's considered a poor programming practice to do an INSERT INTO table and not spell out all the columns, as that places a dependancy on the SELECT clause having ALL columns in EXACTLY the same order that it appears in #TEMPTABLE.  Any deviaiton from this will throw an error.
0
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 100 total points
Comment Utility
In addition to all of the above, you seem to have a BEGIN without a corresponding END:

if (@DB_Include = 0 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
insert  INTO #TEMPTABLE

SELECT
	'WEBSNAK',
	Invoices.InvoiceDate,
	InvoiceDetails.SNAK_SaleTypesID, 
	InvoiceDetails.CustPromoAmt,
	InvoiceDetails.MfgCostAmt,
	InvoiceDetails.GrossAmt,
	Customers.CustName, 
	Customers.SNAK_CustomerID,
	Chains.ChainName, 
	Chains.SNAK_ChainID
FROM
     dbo.Invoices Invoices 
     INNER JOIN dbo.Customers Customers 
			ON	Invoices.Customers_Syskey = Customers.Customers_Syskey
     INNER JOIN dbo.InvoiceDetails InvoiceDetails 
			ON	Invoices.Invoices_Syskey = InvoiceDetails.Invoices_Syskey
     INNER JOIN dbo.Chains Chains 
			ON	Customers.Chains_Syskey = Chains.Chains_Syskey
			
			END

Open in new window

0
 

Author Comment

by:ruavol2
Comment Utility
I did not show the END at the end but it was in there.

Also it's considered a poor programming practice to do an INSERT INTO table and not spell out all the columns, as that places a dependancy on the SELECT clause having ALL columns in EXACTLY the same order that it appears in #TEMPTABLE

I did spell them out EXACTLY unless it is CASE SENSITIVE.....is it CASE SENSITIVE...?

Sqwiggly came from Crystal Reports....I stole some of the code in the beginning so I can learn. Unfortunately for me I have a hard time with picking things up without just doing it. I tried reading books in order to deal with women.....that did not work out so I just dove in and figured it out that way. Sometimes you just have to get your hands dirty.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 200 total points
Comment Utility
>> is it CASE SENSITIVE...?

That depends on how your administrator configured the server.  One way to find out is to do this:

select top 1 * from dbo.Customers
select top 1 * from dbo.CUSTOMERS

If you get results from both queries, it is not case-sensitive.
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
(well, unless you have two tables, one called Customers and the other CUSTOMERS ;)
0
 

Author Comment

by:ruavol2
Comment Utility
It was the missing comma. Cannot believe I missed that......jeeese louise....!
Thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>     INNER JOIN dbo.InvoiceDetails InvoiceDetails
btw using an alias that is exactly the same name as the table kinda defeats the purpose of the alias.  Better would be i for invoices, c for customers, id for invoice details, and ch for chains.  Much easier to read.
0
 

Author Closing Comment

by:ruavol2
Comment Utility
I checked the Parenthesis and French Braces they are all okay. There was an end....just did not copy down that far....I have everything else commented out.....It was the comma.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>It was the missing comma. Cannot believe I missed that......jeeese louise....!
if only I had one cent per that problem, and it is why I recommend "comma first", like so:
SELECT
        'WEBSNAK'
      , Invoices.InvoiceDate
      , InvoiceDetails.SNAK_SaleTypesID
      , InvoiceDetails.CustPromoAmt
      , InvoiceDetails.MfgCostAmt
      , InvoiceDetails.GrossAmt
      , Customers.CustName
      , Customers.SNAK_CustomerID
      , Chains.ChainName
      , Chains.SNAK_ChainID
FROM dbo.Invoices Invoices
        INNER JOIN dbo.Customers Customers
                ON Invoices.Customers_Syskey = Customers.Customers_Syskey
        INNER JOIN dbo.InvoiceDetails InvoiceDetails
                ON Invoices.Invoices_Syskey = InvoiceDetails.Invoices_Syskey
        INNER JOIN dbo.Chains Chains
                ON Customers.Chains_Syskey = Chains.Chains_Syskey

Open in new window

I don't expect to convert all, but I have converted many :)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now