error in WHERE statement

dear sirs i am using sql server 2000 enterprise edition, i am new to sql server and also am learning the sql language...

I have a table named spt_datatype_info
that table has a column called TYPE_NAME
I have given a WHERE statement in a query:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

i know actually the value smallint has to be given in quotes...
Now my question is: When i give the Verify SQL syntax
then it does not return any error, but when i run it...then it given the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'smallint'.

what does this mean...??
If the SQL statement is wrong then it should return an error when verifying the statement...

regards,
kanishkit's because this sql is perfectly valid in terms of syntax:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

The only reason it fails is because there is no column named smallint in that table. That's not a syntax error.

Now this query, on the other hand, works, because both columns exist, and has exactly the same form as yours:

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = LOCAL_TYPE_NAME

is that what you are asking?|||not actually, i was saying that i was looking for a value in the column TYPE_NAME named smallint

I was giving the WHERE clause to look for a value called smallint in the column TYPE_NAME. the column has many vaules in it...smallint is one of them...

maybe i should have written it as:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

Since i am new to SQL language....what were you refering to...??...Although i quite got my answer...

Can you please tell me...what is the intersection of the column and row called??....for example in ms Excel when the row 7 and column D meet then a cell is formed...called D7

But in SQl server...what is the intersection of the Column and Row called...?? Like the cell in ms Excel...??

regards,
kanishk|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?|||the intersection of row and column is called "the value of column Y for row X"

:cool:

i realize that sounds somewhat flippant, but relational database theory is based upon primary keys, so "row X" means "the row where the primary key value is X" since that's how you tell rows apart, and "column Y" means "the column with 'Y' as the column name" since that's how you tell columns apart

simple, innit ;)|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?

thanks, i was wondering that when i give the command that you gave up...viz,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

when i run it, then automatically the TYPE_NAME column takes brackets,,,why is that??...i have shown it below...

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

regards,
kanishk|||That is because you are running this query in the SQL section of enterprise manager. Enterprise manager will try to bracket all where conditions
so if your query is something like

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint' OR TYPE_NAME = 'int'

Then on running it you will get

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint') OR (TYPE_NAME = 'int')

Try running the query in SQL Analyzer and you will not get the brackets..... however i guess the brackets are purely for readability and in the end finally makes no difference.....|||dear sir, i have started using the SQL query Analyzer, i have another question,
i just put in the LIKE condition...

SELECT * FROM spt_datatype_info WHERE TYPE_NAME LIKE '%s'

now this will give all the values in the TYPE_NAME column which start with any characters but end with 's' , thats why i have put '%s' , now suppose i want all the values that have 2 letters as starting and the 3rd letter as 's' , so what do symbol do i use??

Like in windows when we used to give in command prompt the ! , exclamation mark for the characters we did not know but were sure of the count of them, and the star '*' for the characters we did not know and were not sure of their count..

Similarly in SQL what would we give for the LIKE condition..??

regards,
kanishk|||... LIKE '__s%'this is all explained nicely in the manual

Error in mdx querys with quotation marks

The problem:
When I try to execute a mdx with quotation mark inside, I get an error!
Why?
I know that (') is from mdx language.
Will I have to erase this characters from my databases?
I don't think it's good.
In my javascript code(asp):
var cst = new ActiveXObject("ADOMD.Cellset");
...
cst.Source = strSource + " CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR,
FORE_COLOR";
cst.open();
strSource = " 'with ...'
Select {[Programa].[Sigla].[Todos os Programas].[Ajuda
r 'A INDUSTRIA],
[Programa].[Sigla].[Todos os Programas].[APOIO A PROJ SOCIAL
],[Programa].
[Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cubo
_pird] CELL
PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"
The error occur when I execute cst.open()!
Microsoft? OLE DB Provider for Analysis Services error '80040e14'
Syntax error, expecting SELECT, near: 'A INDUSTRIA],[Programa].[Sigl
a].
[Todos...
Message posted via http://www.droptable.comYou need to double up quotation marks within quotes:

strSource = " 'with ...'
Select {[Programa].[Sigla].[Todos os Programas].[Ajudar
''A INDUSTRIA],
[Programa].[Sigla].[Todos os Programas].[APOIO A PROJ
SOCIAL],[Programa].
[Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cubo
_pird] CELL
PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"[vbcol=seagreen]
Here's an earlier thread that discusses this:
http://groups-beta.google.com/group...erver.olap/msg/
5b1f83a30f0203eb[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "George Spofford"
Date: Sat, 1 Jun 2002 07:33:16 -0700
Subject: MDX Error with Current Year's
One of those silly things: double it up inside.
With member [measures].[abc] as
'count(descendants([category]._[all Category].[current
year''s].[actuals],,leaves))'
HTH
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com
geo...@.dsslab.com
ISVs & IT organizations: Find out how DSS Lab can speed
your development!
[vbcol=seagreen]
>--Original Message--
>Hello,
>the following MDX fails:

>The error comes because the uniquename is ..year's]

>With member [measures].[abc] as
>'count(descendants([category]_.[all Category].[current
>year's].[actuals],,leaves))'

>How can i write a valid mdx?
[vbcol=seagreen]
>Thanks for reading this
>Jrg
>.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||There is another error!
Microsoft? OLE DB Provider for Analysis Services error '80040e14'
Formula error - cannot find dimension member ("[Programa].[Sigla].&#
91;Todos os
Programas].[APOIO ''A INDUSTRIA]") - in a name-binding function
/Server_1.asp, line 520
"Deepak Puri" wrote:

> You need to double up quotation marks within quotes:
>
> strSource = " 'with ...'
> Select {[Programa].[Sigla].[Todos os Programas].[Ajud
ar ''A INDUSTRIA],
> [Programa].[Sigla].[Todos os Programas].[APOIO A PROJ
> SOCIAL],[Programa].
> [Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
> on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cu
bo_pird] CELL
> PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"
>
> Here's an earlier thread that discusses this:
> http://groups-beta.google.com/group...erver.olap/msg/
> 5b1f83a30f0203eb
> Newsgroups: microsoft.public.sqlserver.olap
> From: "George Spofford"
> Date: Sat, 1 Jun 2002 07:33:16 -0700
> Subject: MDX Error with Current Year's
> One of those silly things: double it up inside.
> With member [measures].[abc] as
> 'count(descendants([category].-[all Category].[current
> year''s].[actuals],,leaves))'
> HTH
> --
> George Spofford
> Microsoft MVP
> Chief Architect / OLAP Solution Provider
> DSS Lab
> http://www.dsslab.com
> geo...@.dsslab.com
> ISVs & IT organizations: Find out how DSS Lab can speed
> your development!
>
>
>
>
>
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Maybe you used double-quote characters (Ascii 34), instead of 2
single-quote characters (Ascii 39) - that's the only way I could create
the error message you got?
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Did you copy your code from your application source and paste it here?
The reason I ask is that in my Internet Explorer, the so-called single
quote characters in the member names do not appear as single quotes but
rather as unicode gibberish. The single quotes surrounding calculation
value definitions appear as they should.
The fact your mdx runs on some applications and does not on others leads
me to be farely certain that the member names in your cube do not
contain regular single quote characters but other characters, and that
is what causes your problems.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!