pretty new to this.. but my code wont compile? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pretty new to this.. but my code wont compile?

Author

Topic

entrylevel
Starting Member

2 Posts

Posted - 2012-04-09 : 15:20:26

[code]CREATE TABLE CUSTOMER_1 (
CUST_NUM NUMBER PRIMARY KEY,
CUST_LNAME VARCHAR(20),
CUST_FNAME VARCHAR(20),
CUST_BALANCE NUMBER);

CREATE TABLE CUSTOMER_2 (
CUST_NUM NUMBER PRIMARY KEY,
CUST_LNAME VARCHAR(20),
CUST_FNAME VARCHAR(20));

CREATE TABLE INVOICE_1 (
INV_NUM NUMBER PRIMARY KEY,
CUST_NUM NUMBER,
INV_DATE DATE,
INV_AMOUNT NUMBER);

INSERT INTO CUSTOMER_1 VALUES(1000 ,'Smith' ,'Jeanne' ,1050.11);
INSERT INTO CUSTOMER_1 VALUES(1001 ,'Ortega' ,'Juan' ,840.92);

INSERT INTO CUSTOMER_2 VALUES(2000 ,'McPherson' ,'Anne');
INSERT INTO CUSTOMER_2 VALUES(2001 ,'Ortega' ,'Juan');
INSERT INTO CUSTOMER_2 VALUES(2002 ,'Kowalski' ,'Jan');
INSERT INTO CUSTOMER_2 VALUES(2003 ,'Chen' ,'George');

INSERT INTO INVOICE_1 VALUES(8000 ,1000 ,'23-APR-2004' ,235.89);
INSERT INTO INVOICE_1 VALUES(8001 ,1001 ,'23-MAR-2004' ,312.82);
INSERT INTO INVOICE_1 VALUES(8002 ,1001 ,'30-MAR-2004' ,528.1);
INSERT INTO INVOICE_1 VALUES(8003 ,1000 ,'12-APR-2004' ,194.78);
INSERT INTO INVOICE_1 VALUES(8004 ,1000 ,'23-APR-2004' ,619.44);

SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_1
UNION
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;

SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_1
UNION ALL
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;

SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_1
INTERSECT
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;

SELECT INV_NUM, CUSTOMER_1.CUST_NUM, CUST_LNAME, CUST_FNAME, INV_DATE, INV_AMOUNT
FROM INVOICE_1 INNER JOIN CUSTOMER_1 ON INVOICE_1.CUST_NUM=CUSTOMER_1.CUST_NUM
WHERE CUST_BALANCE>=1000;

SELECT INV_NUM, INV_AMOUNT,
(SELECT AVG(INV_AMOUNT) FROM INVOICE_1) AS AVG_INV,
(INV_AMOUNT-(SELECT AVG(INV_AMOUNT) FROM
INVOICE_1)) AS DIFF
FROM INVOICE_1
GROUP BY INV_NUM, INV_AMOUNT;

ALTER TABLE CUSTOMER_1 ADD (CUST_DOB DATE) ADD (CUST_AGE NUMBER);

SELECT CUST_LNAME, CUST_FNAME, ROUND((SYSDATE-CUST_DOB)/365,0) AS AGE
FROM CUSTOMER_1;

CREATE OR REPLACE TRIGGER TRG_UPDATECUSTBALANCE
AFTER INSERT ON INVOICE
FOR EACH ROW
BEGIN
UPDATE CUSTOMER
SET CUST_BALANCE = CUST_BALANCE + :NEW.INV_AMOUNT
WHERE CUST_NUM = :NEW.CUST_NUM;
END;
SELECT * FROM CUSTOMER_1;
SELECT * FROM INVOICE_1;
INSERT INTO INVOICE_1 VALUES (8005,1001,'27-APR-04',225.40);
SELECT * FROM CUSTOMER_1;

[/code]

its an assignment. when i try to compile i get:

quote:

Msg 102, Level 15, State 1, Line 56
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 61
Incorrect syntax near the keyword 'OR'.
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near ':'.

using sql server management studio. i have no problem writing code. the deal is, i cant really get it to compile or show the results to my professor. each part of this code is an an assignment in the book.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-09 : 15:29:48

If you double-click the error message, it will take you to the point in the code when the compiler failed. You'll see which "(" the compiler is having a problem and then you can fix it.

Jim

Everyday I learn something that somebody else already knew

entrylevel
Starting Member

2 Posts

Posted - 2012-04-09 : 15:44:05

thanks for the reply, im aware of that and posted my errors.. but I just wanted to know if anything looked wrong here if someone glanced?

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-04-09 : 16:34:58

OMG!!! I Never knew that double clicking would take you to the error point!!!! Wow! I might not need glasses if I had taken the time to read up on that years ago! :)

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-10 : 05:41:49

I don't think the code you are using here is for SQL Server.
Following are the errors and how you can rectify them:

First Error:

Your Alter Statement is incorrect. Read the link below for help.

[url]http://www.dbforums.com/mysql/1616640-adding-multiple-columns-one-alter-statement.html[/url]

Second Error:

There is no Create Or Replace in SQL Server. Try using If Exists...Drop...Else...Create. CHeck the link below

[url]http://www.sqlservercentral.com/Forums/Topic1280064-392-1.aspx#bm1280068[/url]

Third Error:

SET CUST_BALANCE = CUST_BALANCE + :NEW.INV_AMOUNT

The error is in the above code. Try using temporary variables. Check the examples in the link below for help.

[url]http://msdn.microsoft.com/en-us/library/aa258839(v=sql.80).aspx[/url]

Hope this was helpful.

Vinu Vijayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources