Simple query for MSSQL

[rogynskyy]

Hi guys,

I have to run a simple query on a MSSQL 2000.
I'm new to database usage.

I need to create a view with the following table columns

LIST_PRICE decimal;
MAP varchar(6);
STREET_NO varchar(10);
STREET_NAME varchar(30);
SQUARE_FEET decimal;
PARKING_FEATURE varchar(9)

Here is a query that SQLGrinder (I'm a Mac user) has generated for me:

CREATE VIEW TEST
(
LIST_PRICE decimal,
MAP varchar(6),
STREET_NO varchar(10),
STREET_NAME varchar(30),
SQUARE_FEET decimal,
PARKING_FEATURE varchar(9)
)

When I run this query, SQLGrinder returns an error:

Line 1: incorrect syntax near "decimal"

Could you please tell me what's wrong with it (and sorry for such a stupid inquiry) and how to run this query properly.

Thanks a lot!

rogik

[iburyak]

1. To create a view use following:

[PHP]CREATE VIEW TEST
AS

LIST_PRICE,
MAP,
STREET_NO ,
STREET_NAME,
SQUARE_FEET,
PARKING_FEATURE
-- here you need a From close meaning from which table to select
FROM table_name[/PHP]

2. To get result do following after successfully creating a view in step 1:

[PHP]Select * from TEST[/PHP]

[rogynskyy]

1. To create a view use following:

[PHP]CREATE VIEW TEST
AS

LIST_PRICE,
MAP,
STREET_NO ,
STREET_NAME,
SQUARE_FEET,
PARKING_FEATURE
-- here you need a From close meaning from which table to select
FROM table_name[/PHP]

2. To get result do following after successfully creating a view in step 1:

[PHP]Select * from TEST[/PHP]

When I use the text in part 1 and then try to execute it (using "Execute" command of SQLGrinder, it still gives me the following error:

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'LIST_PRICE'.

Do you know what could it be?

Thanks a lot!

Oleg

[iburyak]

You should run it on a server side and second statement use from a front end.

[rogynskyy]

You should run it on a server side and second statement use from a front end.

I'm on a client side. Server is a remote real estate database, operated by a 3d party. My task is to get out the following columns from the database for the period of the past 7 days.

LIST_PRICE decimal,
MAP varchar(6),
STREET_NO varchar(10),
STREET_NAME varchar(30),
SQUARE_FEET decimal,
PARKING_FEATURE varchar(9)

Table name is Feed_3PT_SF

Thanks a lot!!!

Oleg

ps. sorry for all these newbie questions - everyone of us starts at some point

[pkreemer]

I think iburyak might have dropped a SELECT when cut n pasting. Otherwise I just pasted the same solution.

Try this, and of course you'll need to replace '<table_name>' with the actual name of the table that these fields are coming from. Or are they coming from more than one table? When creating a view, you do need to know your database structure. :-) And do you have permissions to create views on that database?



CREATE VIEW vwTest
AS

SELECT

LIST_PRICE,
MAP,
STREET_NO ,
STREET_NAME,
SQUARE_FEET,
PARKING_FEATURE

FROM <table_name>


Once it's created you can select against it just as though it were a table:

SELECT * FROM vwTest

[rogynskyy]

I think iburyak might have dropped a SELECT when cut n pasting. Otherwise I just pasted the same solution.

Try this, and of course you'll need to replace '<table_name>' with the actual name of the table that these fields are coming from. Or are they coming from more than one table? When creating a view, you do need to know your database structure. :-) And do you have permissions to create views on that database?



CREATE VIEW vwTest
AS

SELECT

LIST_PRICE,
MAP,
STREET_NO ,
STREET_NAME,
SQUARE_FEET,
PARKING_FEATURE

FROM <table_name>


Once it's created you can select against it just as though it were a table:

SELECT * FROM vwTest

Thank you so much for the reply!

One thing - they deny me a permissiont o create a view on the server side.
What would be my solution?
How should I change the query text?

Thanks,

Oleg

[iburyak]

Just run a select statement. Don't try to create a view then.
Hope you do have read_only rights to a table.

SELECT
LIST_PRICE,
MAP,
STREET_NO ,
STREET_NAME,
SQUARE_FEET,
PARKING_FEATURE

FROM <table_name>

Good Luck.

[rogynskyy]

Just run a select statement. Don't try to create a view then.
Hope you do have read_only rights to a table.

SELECT
LIST_PRICE,
MAP,
STREET_NO ,
STREET_NAME,
SQUARE_FEET,
PARKING_FEATURE

FROM <table_name>

Good Luck.

I do it - it shows me columns in the output section of the SQLGrinder, but the columns are empty. There are no entries in them
Any ideas?

[iam_clint]

i have never used sqlgrinder and it might be a problem with that program, are the columns empty in the db?

[rogynskyy]

i have never used sqlgrinder and it might be a problem with that program, are the columns empty in the db?

They have stuff. It is one of the biggest real estate databases.

Which program is the best for use on a MAC?
I need to pull certain columns and export them into comma delimited.

[iam_clint]

i haven't touched a mac in my life sorry :(

the query they gave you should work

theres no ifs, wheres so it should pull all the data from the db with those column names you sure you put in the right table name?

[iburyak]

I am not sure what you do.
Did you connect to the server?
Did you change table_name to some table that is on your server?

Try to do this statement instead just to check you doing everything else correctly.

Select name from sysobjects

It is a system table and always has some data in it.

Good Luck.

[rogynskyy]

i haven't touched a mac in my life sorry :(

the query they gave you should work

theres no ifs, wheres so it should pull all the data from the db with those column names you sure you put in the right table name?

The database is huge. Could it be a reason that there is too much data, so it cannot display it?

Also,
Is there any way to modify the query to include data only for the past 7 days?
What would it be?



Thanks,

Oleg

[rogynskyy]

I am not sure what you do.
Did you connect to the server?
Did you change table_name to some table that is on your server?

Try to do this statement instead just to check you doing everything else correctly.

Select name from sysobjects

It is a system table and always has some data in it.

Good Luck.

I executed this command:

Select name from sysobjects

and it returned me a list of system objects. This means I'm connected and SQLGrinder works well.

Any ideas?

I just ran the following command
[CODE] Select LIST_PRICE from Feed_3PT_SF[CODE]

And it returned me a LIST_PRICE column without any data in it