Incorrect Syntax Error Message
-
I tried to alter a table using SQL Query Analyzer and clicked the "Parse Query" to verify the synatx, but receive the following error message: "Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '('."
Can anyone tell me what is wrong with my table structure? Or, whether or not I need to delete the existing table in order for this to work. I'm attempting to create a file number that will display, 0001-04, 0002-04, 0003-04, etc., as data is entered. The first 4 numbers are incremented and is followed by the current year. I would also like to add the OfficeID, in hopes of displaying the file number as: 104-04-0001; 104 being the OfficeID and switch the RIGHT(CAST(DATEPART(yy,RcptDate) to be the first field, followed by (ReceiptID AS varchar(4)),4) and then the OfficeID field. However, I'm not sure how to rearrange the line that I have in red below.
Please help.
ALTER TABLE [Receipts] (
[ReceiptID] [int] IDENTITY (1, 1) NOT NULL,
[Receipt] AS RIGHT('0000' + CAST(ReceiptID AS varchar(4)),4) + ' - ' + RIGHT(CAST(DATEPART(yy,RcptDate) AS varchar(4)),2),
[CityID] [int] NULL,
[RcptDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Receipts_ReptDate] DEFAULT (getdate()),
[RcptAmt] [numeric](18, 0) NULL,
[OfficeID] [int] NULL, ///This field is coming from the Offices table and is shown below.///
[PymtTypeID] [int] NULL,
[PermitNum] [char] (10) NULL,
[CheckNum] [char] (10) NULL,
[RecdFrom] [varchar] (35) NULL,
[CaseNumber] [varchar] (15) NULL,
[CasePlaintiff] [varchar] (30) NULL,
[CaseDefendant] [varchar] (30) NULL,
[CourtName] [varchar] (30) NULL,
[SubpeonaDate] [datetime] NULL,
[UserID] [int] NULL,
[Waived] [bit] NULL,
[WaivedReason] [varchar] (512) NULL,
[Voided] [bit] NULL,
[VoidDate] [datetime] NULL,
[VoidReason] [varchar] (512)NULL,
CONSTRAINT [PK_Receipts] PRIMARY KEY CLUSTERED
(
[ReceiptID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
Offices Table: In a view I can write the view to display what I need as: SUBSTRING(OfficeCostCtr, 3, 3) + ' - ' + OfficeName as CostCenter into one line, to show 104 -Division Name.
CREATE TABLE [Offices] (
[OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
[OfficeName] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OfficeCostCtr] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Offices] PRIMARY KEY CLUSTERED
(
[OfficeID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
-
hm.. DROP and then re-CREATE table you must. Or learn the true syntax of ALTER TABLE! Books on-line, the great tomb of jedi knowledge, will lead you there!
however, Yoda sees more problems. Identity, reset it will not for next year! 01234-04 becomes 01235-05 ! Holes in your sequence, also, an Identity may leave!
A trigger you may need. Or store not this info -- present it in a View!
-
I currently have a view called vwSelect_Receipts, but I don't have the second line (in red above) as part of the view. Can I use the following line within the view as follows?
a). Receipt AS RIGHT('0000' + CAST(ReceiptID AS varchar(4)),4) + ' - ' + RIGHT(CAST(DATEPART(yy,RcptDate) AS varchar(4)),2),
b). I know this line of code does work in a view: SUBSTRING(OfficeCostCtr, 3, 3) + ' - ' + OfficeName as CostCenter
How do I tie this in to line (a.), if I want to show the alias Cost Center first followed by the DATEPART and then the CAST field third????
-
Ask Yoda not "can this be done?" Experiment! It is the tool of the jedi. Try not. Do. Query Analyzer you will open, test view you will create.
Be not discouraged or frightened by error messages. Carefully, you will read them. Learn from them, you will. Adjustments, you will make. Books on-line, you will consult.
And conquer the error messages, you shall!! It is the first step on the path of the jedi!
-
I'm not sure exactly what format you want for the computed column. However, here are some possibilities. Reorganize the expression as needed.
CREATE VIEW vwSelect_Receipts
AS
SELECT ...
SUBSTRING([OfficeCostCtr], 3, 3)
+ ' - '
+ [OfficeName]
+ RIGHT('0000' + CAST([ReceiptID] AS varchar(4)),4)
+ ' - '
+ RIGHT(CAST(DATEPART(yy,[RcptDate]) AS varchar(4)),2) AS [Receipt_Layout_1],
SUBSTRING([OfficeCostCtr], 3, 3)
+ ' - '
+ [OfficeName]
+ RIGHT(CAST(DATEPART(yy,[RcptDate]) AS varchar(4)),2)
+ ' - '
+ RIGHT('0000' + CAST([ReceiptID] AS varchar(4)),4) AS [Receipt_Layout_2],
...
FROM [Receipts] JOIN [Offices] ON [Receipts].[OfficeID] = [Offices].[OfficeID]
-
Attn: Enthusiast
I used both examples above, that included the fields in the table. However, when I run the select statement, even though, there are records in the table. It does not display any records. Below is what I choose to use.
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
SUBSTRING([OfficeCostCtr], 3, 3)
+ ' - '
+ [OfficeName]
+ RIGHT(CAST(DATEPART(yy,[RcptDate]) AS varchar(4)),2)
+ ' - '
+ RIGHT('0000' + CAST([ReceiptID] AS varchar(4)),4) AS [ReceiptNum],
CityID,
RcptDate,
RcptAmt,
PymtTypeID,
PermitNum,
CheckNum,
RecdFrom,
CaseNumber,
CasePlaintiff,
CaseDefendant,
CourtName,
SubpeonaDate,
UserLogins.LastName + ', ' + FirstName AS Username,
Waived,
WaivedReason,
Voided,
VoidDate,
VoidReason
FROM [Receipts] JOIN [Offices] ON [Receipts].[OfficeID] = [Offices].[OfficeID]
INNER JOIN UserLogins ON Receipts.UserID = UserLogins.UserID
-
Problem lies not in your new expression -- any expression in the SELECT portion, it affects not the number of rows returned.
Your join, the cause of this, it must be. Unless a WHERE clause you have added, but shown us not.
Veryify you shall -- matching data in both tables on the join expression provided?
-
The expression shouldn't limit the number of rows returned. What do you get if you execute:
SELECT
CityID,
RcptDate,
RcptAmt,
PymtTypeID,
PermitNum,
CheckNum,
RecdFrom,
CaseNumber,
CasePlaintiff,
CaseDefendant,
CourtName,
SubpeonaDate,
UserLogins.LastName + ', ' + FirstName AS Username,
Waived,
WaivedReason,
Voided,
VoidDate,
VoidReason
FROM Receipts JOIN Offices ON Receipts.OfficeID = Offices.OfficeID
INNER JOIN UserLogins ON Receipts.UserID = UserLogins.UserID
-
All the field headers display, but the existing data is not displayed.
-
You've got two inner joins:
1. Receipts INNER JOIN Offices ON Receipts.OfficeID = Offices.OfficeID
2. Receipts INNER JOIN UserLogins ON Receipts.UserID = UserLogins.UserID
The problem lies with your data or the JOINs you've chosen.
Try it without the UserLogins table to make sure the Receipts and Offices table are joining properly.
SELECT
SUBSTRING([OfficeCostCtr], 3, 3)
+ ' - '
+ [OfficeName]
+ RIGHT(CAST(DATEPART(yy,[RcptDate]) AS varchar(4)),2)
+ ' - '
+ RIGHT('0000' + CAST([ReceiptID] AS varchar(4)),4) AS [ReceiptNum],
CityID,
RcptDate,
RcptAmt,
PymtTypeID,
PermitNum,
CheckNum,
RecdFrom,
CaseNumber,
CasePlaintiff,
CaseDefendant,
CourtName,
SubpeonaDate,
-- UserLogins.LastName + ', ' + FirstName AS Username,
Receipts.UserID -- Add this to make sure you've got the correct data here
Waived,
WaivedReason,
Voided,
VoidDate,
VoidReason
FROM [Receipts] JOIN [Offices] ON [Receipts].[OfficeID] = [Offices].[OfficeID]
If you get results, spot check Receipts.UserID to make sure you got the right values there (versus SELECT * FROM UserLogins).
Alternatively, use an OUTER JOIN to UserLogins:
FROM [Receipts] JOIN [Offices] ON [Receipts].[OfficeID] = [Offices].[OfficeID]
OUTER JOIN UserLogins ON Receipts.UserID = UserLogins.UserID
-
Awesome. All the quote you wrote does work the way I want it. The only exception is the Receipt.UserId applied to the OUTER JOIN. When I add the OUTER JOIN to the end of the SELECT statement, the following error is displayed.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'OUTER'.
-
Attn: Enthusiast
Disregard my last post, I got it working properly.
Thanks again for all your help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply