Print Page | Close Window

Some Minor SQL errors

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=4703
Printed Date: 30 March 2026 at 1:57pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Some Minor SQL errors
Posted By: LucyToons
Subject: Some Minor SQL errors
Date Posted: 03 August 2003 at 8:51am

Hi, I have some sql that is throwing up about 7 or 8 errors, and although i've tried hard I cannot see why the errors are there (cant see wood for the trees so to speak)
Im not overly confident with SQL so if someone could just have a look and perhap see where im going wrong that'd be really nice, even nicer if you could tell me as well

These are the errors for the code which is linked below


Server: Msg 170, Level 15, State 1, Line 64
Line 64: Incorrect syntax near 'C2'.
Server: Msg 170, Level 15, State 1, Line 73
Line 73: Incorrect syntax near 'B2'.
Server: Msg 170, Level 15, State 1, Line 80
Line 80: Incorrect syntax near 'L2'.
Server: Msg 170, Level 15, State 1, Line 87
Line 87: Incorrect syntax near 'LN1'.
Server: Msg 170, Level 15, State 1, Line 93
Line 93: Incorrect syntax near 'M2'.
Server: Msg 111, Level 15, State 1, Line 161
'CREATE VIEW' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 169
'CREATE VIEW' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 175
'CREATE VIEW' must be the first statement in a query batch.


http://www.rillie-place.co.uk/sqlerrors.txt - http://www.rillie-place.co.uk/sqlerrors.txt

Regards Lucy


Replies:
Posted By: Flamewave
Date Posted: 03 August 2003 at 4:01pm

When you do a insert, you need to specify the columns, ie:

INSERT INTO tblname (column1, column2) VALUES ('value1', 'value2')

Also, Im not sure if you can do more then one record at a time with a insert, I believe you have to do each record individually with its own insert statement (not 100% positive on this though).

Then when you go to create a view, you cannot have any other scripts before or after the view, ie you have to run the code to create the view by itself.

Hope this helps.



-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: LucyToons
Date Posted: 03 August 2003 at 6:44pm

ok the veiw bit i get

the insert part im not sure as there are only errors every now and again (thou in the insert sections)

 

so if u have to have a statement each time there should be more errors?

 

 

Posted By: LucyToons
Date Posted: 04 August 2003 at 7:42am 


CREATE TABLE branch
	(branch_no VARCHAR(10) NOT NULL,
	branch_name VARCHAR(60) NOT NULL,
	branch_address VARCHAR(120) NOT NULL,
	branch_town VARCHAR(50) NOT NULL,
	branch_postcode VARCHAR(10) NOT NULL,
	branch_phone VARCHAR(18) NOT NULL,
CONSTRAINT PK_branch PRIMARY KEY (branch_no))

CREATE TABLE customer
	(customer_no VARCHAR(6) NOT NULL,
	customer_name VARCHAR(30) NOT NULL,
	customer_address VARCHAR(100) NOT NULL,
	customer_town VARCHAR(30) NOT NULL,
	customer_postcode VARCHAR(8) NOT NULL,
	customer_phone VARCHAR(14),
	branch_no VARCHAR(5) NOT NULL,
	date_recorded DATETIME,
CONSTRAINT PK_customer PRIMARY KEY (customer_no))



CREATE Table finance_company
	(lender_no VARCHAR(5) NOT NULL,
	lender_name VARCHAR(30) NOT NULL,
	lender_address VARCHAR(100) NOT NULL,
	lender_town VARCHAR(30) NOT NULL,
	lender_postcode VARCHAR(8) NOT NULL,
	lender_phone VARCHAR(14) NOT NULL,
CONSTRAINT PK_finance_company PRIMARY KEY (lender_no))

CREATE TABLE mortgage
	(mortgage_no VARCHAR(5) NOT NULL,
	customer_no VARCHAR(6) NOT NULL,
	mortgage_amount INT NOT NULL,
	monthly_repayment INT NOT NULL,
	lender_no VARCHAR(5) NOT NULL,
CONSTRAINT PK_mortgage PRIMARY KEY (mortgage_no),
CONSTRAINT FK_mortgage FOREiGN KEY (lender_no) References finance_company(lender_no),
CONSTRAINT FK_mortgage2 FOREIGN KEY (customer_no) References customer(customer_no))

CREATE TABLE loan
	(loan_no VARCHAR(5) NOT NULL,
	customer_no VARCHAR(6) NOT NULL,
	loan_amount INT NOT NULL,
	monthly_repayment INT NOT NULL,
	lender_no VARCHAR(5) NOT NULL,
CONSTRAINT PK_loan PRIMARY KEY (loan_no),
CONSTRAINT FK_loan FOREIGN KEY (lender_no)References finance_company(lender_no),
CONSTRAINT FK_loan2 FOREIGN KEY (customer_no)References customer(customer_no))



	CREATE UNIQUE NONCLUSTERED INDEX NC_branch_name ON branch(branch_name)
	CREATE UNIQUE NONCLUSTERED INDEX NC_customer_name ON customer(customer_name)
	CREATE UNIQUE NONCLUSTERED INDEX NC_lender_name ON finance_company(lender_name)
	CREATE UNIQUE NONCLUSTERED INDEX NC_lender_no ON loan(loan_no)
	CREATE UNIQUE NONCLUSTERED INDEX NC_lender_no ON mortgage(mortgage_no)
	CREATE UNIQUE NONCLUSTERED INDEX NC_branch_no ON customer(branch_no)

INSERT INTO customer
VALUES
('C1','Owen Hedges','26 Chatsworth Place','Cleethorpes','DN35 8NG','01234567890','B1',05/09/2003)
INSERT INTO customer
VALUES
('C2','Jono Popham','18 Something Street','Lincoln','LN7 6TY','01234098765','B1',04/09/2003)
INSERT INTO customer
VALUES
('C3','Nigel Gissing','22 Somewhere Lane','Market Rasen','MK34 9UK','09876543210','B2',07/08/2003)
INSERT INTO customer
VALUES
('C4','Michael Davies','45 Satellite Rd','Gainsborough','GB6 9UJ','01267873987','B3',03/03/2003)
INSERT INTO customer
VALUES
('C5','Stephen Keeling','35 Sideburn Street','Lincoln','LN78 09GH','09876786987','B3',12/07/2003)
INSERT INTO customer
VALUES
('C6','Hayley Keeling','37 Sideburn Street','Lincoln','LN78 09GH','09876786999','B2',11/07/2003)

INSERT INTO branch
VALUES
('B1','Lincoln High St','High St','LINCOLN','LN7 3NY','01234 567 891')
INSERT INTO branch
VALUES
('B2','Lloyds TSB','37 High St','LINCOLN','LN7 3RT','01234 536 970')
INSERT INTO branch
VALUES
('B3','Royal Bank Of Scotland','Saltergate','LINCOLN','LN31 3TY','01234 111 891')


INSERT INTO finance_company
VALUES
('L1','Start Finance','10 Ripemov Lane','Lincoln','LN89 9NL',' 01234 567 765')
INSERT INTO finance_company
VALUES
('L2','Cheap Loans','27 Flower Street,','Market Rasen','MK7 0DN','98769 765 987')
INSERT INTO finance_company
VALUES
('L3','Lloyds TSB Personal Credit','High Street,','Lincoln','LN6 9UK','01234 506 978')
INSERT INTO finance_company
VALUES
('L4',' Nationwide Loans','34 Clasketgate Lincoln','LN45 9HY','01234 726 876')

INSERT INTO loan
VALUES
('LN1','C1',25000,200,'L1')
INSERT INTO loan VALUES('LN1','C3',1000,146,'L1')
INSERT INTO loan VALUES('LN3','C4',5000,87,'L3')

INSERT INTO mortgage
VALUES
('M1','C1',60000,200,'L2')
INSERT INTO mortgage VALUES
('M2','C4',40000,80,'L1')


SELECT	branch.branch_no, branch.branch_name, branch.branch_address
FROM branch INNER JOIN
customer ON branch.branch_no = customer.branch_no INNER JOIN
loan ON customer.customer_no = loan.customer_no INNER JOIN
mortgage ON customer.customer_no = mortgage.customer_no WHERE
(loan.loan_no = 'LN3')

SELECT branch.branch_no, branch.branch_name, customer.customer_no,
customer.customer_name, loan.loan_no,
	     finance_company.lender_no, finance_company.lender_name,
customer.customer_address, customer.customer_town,
	     customer.customer_postcode
FROM	branch INNER JOIN
	 customer ON branch.branch_no = customer.branch_no INNER JOIN
	 loan ON customer.customer_no = loan.customer_no INNER JOIN
	 finance_company ON loan.lender_no = finance_company.lender_no
WHERE	(finance_company.lender_no = 'L1')


SELECT	customer.customer_no, loan.loan_no, branch.branch_no, branch.branch_name,
finance_company.lender_name,
	 loan.loan_amount, loan.monthly_repayment, customer.customer_name,
customer.customer_address
FROM	finance_company LEFT OUTER JOIN
	 loan ON finance_company.lender_no = loan.lender_no FULL OUTER JOIN
	 customer INNER JOIN
	 branch ON customer.branch_no = branch.branch_no ON loan.customer_no = 
customer.customer_no
WHERE	(finance_company.lender_name = 'Stuart Finance') AND (branch.branch_name = 
'Lincoln High Street')


SELECT	customer.customer_no, finance_company.lender_name AS 'Lender Name',
mortgage.mortgage_no, branch.branch_no,
	 branch.branch_name, finance_company.lender_name, mortgage.mortgage_amount,
mortgage.monthly_repayment,
	 customer.customer_name, customer.customer_address
FROM	finance_company INNER JOIN
	 mortgage ON finance_company.lender_no = mortgage.lender_no INNER JOIN
	 customer INNER JOIN
	 branch ON customer.branch_no = branch.branch_no ON mortgage.customer_no = 
customer.customer_no
WHERE	(branch.branch_name = 'Lincoln High Street') AND (finance_company.lender_name = 
'Cheap Loans')



SELECT	customer.customer_no, mortgage.mortgage_amount, mortgage.monthly_repayment,
	customer.customer_name, customer.customer_address, branch.branch_no, branch.branch_name
FROM	mortgage INNER JOIN
	 customer ON mortgage.customer_no = customer.customer_no INNER JOIN
	 branch ON customer.branch_no = branch.branch_no
WHERE	(mortgage.mortgage_amount > 40000)



SELECT	customer.customer_no, mortgage.mortgage_amount, mortgage.monthly_repayment, 
	customer.customer_name, customer.customer_address, branch.branch_no, branch.branch_name
FROM	mortgage INNER JOIN
	 customer ON mortgage.customer_no = customer.customer_no INNER JOIN
	 branch ON customer.branch_no = branch.branch_no
WHERE	(mortgage.mortgage_amount > 40000) AND branch_name = 'Lincoln High Street'
 
getting closer :) but still one error :(
 
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
 
 

Posted By: Flamewave
Date Posted: 04 August 2003 at 7:54am
On one of your insert statements you must have either enterd an extra column name or missed entering one. The error means that the number of columns that you enterd in and the number of data items to enter in (the values) don't match up. Or it could be that you mis-typed one of your column names too.

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: LucyToons
Date Posted: 04 August 2003 at 8:40am

('L4',' Nationwide Loans','34 Clasketgate Lincoln','LN45 9HY','01234 726 876')

 

found it there

 

now have another error arrrrrrrrrrrrrrrrrrrrrrrrrrrghhhhhh

 

 


Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net