TAMSCODAN
asked on
CREATE SQL VIEW
I am having a problem creating a VIEW on SQL. I have all the required tables built, however when I specify to use a DATABASE
USE ABC123
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
I get the following error:
'CREATE VIEW' must be the first statement in a query batch.
What is causing this error?
USE ABC123
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
I get the following error:
'CREATE VIEW' must be the first statement in a query batch.
What is causing this error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cool, however where would I put the GO statement.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow, that was the issue! is there a way to delete the view in the script if it was already created? so i can run the script again.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
U ARE DA MAN!!!! THANKS!!!!
ASKER
I added the GO Statement as you mentioned previously, and I added a DROP VIEW statement at the begining of the code. Looks like the query executes without errors, however I dont see any results. Any suggestions?
Post the script that you're running.
ASKER
USE ABC123
GO
DROP VIEW abc_only
GO
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
GO
DROP VIEW abc_only
GO
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
SELECT * FROM abc_only;
That should get you everything where role='noun'.
Are you saying that it returns no rows? Check the role column and make sure that at least 1 row has the value 'noun'. Remember that string comparison is case sensitive so it must be all lower case, too.
Kent
ASKER
There are 4 colums, letter, Font, inserted_date and role. The view that I want are letter, Font, inserted_date that contain noun in the role column. In reference to my database it has 4 entries. Are you saying to replace SELECT letter, Font, inserted_date with SELECT * FROM abc_only;
Sure. The view that you've created returns the rows that you want. A view name can be queried almost every place that a table name is appropriate.
Kent
Kent
Put the word 'GO' on a line between to two commands.
Kent