Some Minor SQL errors

Dev Shed Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

August 3rd, 2003, 09:47 AM

LucyToons

Junior Member

Join Date: Aug 2003

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Some Minor SQL errors

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.

URL

Regards Lucy

August 4th, 2003, 05:20 AM

Silian

Gogga

Join Date: Jul 2003

Posts: 198

Time spent in forums: < 1 sec
Reputation Power: 6

You have way more errors than you think

Firstly, 2 get different error messages: You can't use:

Code:

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

sql is stupid

You must do:

Code:

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')

Horrible, isn't it? (You must do this 4 all your insert statements.)

Unfortantly, the code still won't work. datetime is misspelled in table customer.

Then, you must create and insert the stuff in the branch table, before you can create and insert the customers.

And this is the end of my help 4 now, bcs even I am stuck

Don't worry, sql is a mystery 2 everyone

Good luck!

p.s, I'll remove the foreign constraint on table customers if I were you. It just causes 2 many problems.

pps, I liked the name of the thread

Last edited by Silian : August 4th, 2003 at 05:42 AM.

August 4th, 2003, 05:50 AM

LucyToons

Junior Member

Join Date: Aug 2003

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

ok thanks has helped a lot
thou I now have different errors

August 4th, 2003, 06:13 AM

christo

Introspective

Join Date: Nov 2001

Location: London, UK

Posts: 3,296

Time spent in forums: 3 Days 1 h 5 m 9 sec
Reputation Power: 101

let's see them

christo

__________________
.
Spiration channels: Free scripts, programming tutorials and articles
Dotcut alerts: Online Press cuttings / news alerts
Clearprop: UK microlight school, wiltshire
Uk dating: UK safe dating with Topdates
About Christo
.
.

August 4th, 2003, 06:39 AM

Silian

Gogga

Join Date: Jul 2003

Posts: 198

Time spent in forums: < 1 sec
Reputation Power: 6

Oh yes, I forgot 2 say this: Make sure that the number of stuff you try to insert corresponds 2 the number of columns in your new table. I specifically noticed that you were trying 2 insert more stuff in customer than the table has columns 4.

Most of your other problems should be sorted if you remove the foreign key from customer (I never understand why people want 2 add foreign keys (is it a standard somewhere?) bcs it causes more problems than it solves)

If you still have problems, try running your code bit by bit and figuring out where the prob is.

Then, if you still have problems, let us know, and Christo will be more than happy 2 help you out

August 4th, 2003, 06:44 AM

LucyToons

Junior Member

Join Date: Aug 2003

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

okie dokie u are right more data than i have fields (which ive fixed)

heres the new code
ive taken the create views out as they arent supposed to have stuff before and after redone the spelling mistakes and as I say fixed the extra data problem however im getting this one error sumthing to do with the branch table and the contents I think

Code:

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'

Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

August 4th, 2003, 07:07 AM

Silian

Gogga

Join Date: Jul 2003

Posts: 198

Time spent in forums: < 1 sec
Reputation Power: 6

I've found the problem! Hurray

Sorry, I got the same error, and couldn't figure it out at all. You are telling the server:

Code:

CREATE UNIQUE NONCLUSTERED INDEX NC_branch_no ON customer(branch_no)

basically, what you are saying, is that there can't be duplicate branch_no in the customer tables. Not sure why you would want 2 do that.

Then, just check the insert statement 4 loans. You are trying to insert 2 'ln1' loan numbers. Guess it's just a typing error that should be 'ln1' and 'ln2' (espesially since the next one is 'ln3').

August 4th, 2003, 08:31 AM

LucyToons

Junior Member

Join Date: Aug 2003

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

could u show me the working code
ive changed the ln1 to ln2 and agree more than one customer should be able to use a particular branch
but still have the same error...........

August 4th, 2003, 08:46 AM

Silian

Gogga

Join Date: Jul 2003

Posts: 198

Time spent in forums: < 1 sec
Reputation Power: 6

Sorry, I am getting a "The log file for database 'RooQIs' is full. Back up the transaction log for the database to free up some log space." message. Don't really know how 2 fix this, which means that someone else is going 2 have 2 do it, which means sitting around doing nothing 4 a week

Anyway, you must drop all your tables, delete the line that says:

Code:

CREATE UNIQUE NONCLUSTERED INDEX NC_branch_no ON customer(branch_no)

Then run all of the scripts in sections. If you still get an error, let us know +- where you get it, as well as the error.

(That's the horrible thing of SQL. You think you've solved all of your errors, and then another one creeps up

)

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: