Desc as column name

Hi,
When I run the following sql statement
CREATE TABLE t1 (Desc varchar(50))
I get an error saying Incorrect syntax near keyword Desc.
However, I can run the same command on other database engines.
Where can I find situations like this where an sql statement will work
on other databases but not SqlServer?
The statement
CREATE TABLE t1 ([Desc] varchar(50))
works fine.
Do you suggest that I enclose column names in square brackets ([]) for
all sql statements (select, insert, update, create, delete etc)?
Regards,
PrakashSince you mention other database engines, you should get into the habit of using the ANSI SQL
compliant way to delimit identifiers: double-quotes and not square brackets.
There's a list in Books Online of all the reserved keywords.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>|||Desc is a reserved keyword in SQL Server. It's short for 'descending' and is
used in syntax such as ORDER BY column_name DESC. If you want to use
reserved words as object or column names, you must delimit the name by
either using [ ] as you show below are by using double quotes " ". Keep in
mind that you will need to delimit this name in every statement in which you
reference the name. You might consider just choosing a longer (and more
descriptive) name such as "Description".
For a list of reserved keywords, see this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms189822.aspx
For more information about delimiting names see this Books Online topic
http://msdn2.microsoft.com/en-us/library/ms176027.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>sql

Derived Shape - Replacing a column with itself.

Hi there,

I have a derived column shape in which I replace a column with an expression.

The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)

Like this.

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA

But whenever, the condition is NOT met, ColumnA is set to NULL!!!!

Does this mean that the column value is deleted before the Expression is applied?

If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset?

Cheers.

I'm wondering if there are some data typing issues here. Wha is the type of ColumnA?

-Jamie

|||

Hi Jamie,

It's just a unicode string.

Dave.

|||

I mean in the pipeline. Is it a DT_WSTR?

if so, ensure you don't have any implicit conversions going on. i.e. explicitly cast "Y" as a DT_WSTR.

Also, first try to get this working as a new column rather than replacing ColumnA. See if you exhibit the same problems in that scenario.

-Jamie

|||

Jamie,

in my example setting Column A to the value "y" works fine. It's setting Column A to itself (ie II just want it to retain it's original value) which is the problem.

Do you suggest I explicitly cast the ColumnA in the expression? So with my example....

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : CAST(ColumnA...)

|||

Dave,

No, that's not quite what i meant.

Try this:

DerivedColumn Expression

Replace 'ColumnA' ColumnB != ColumnC ? (DT_WSTR)"y" : ColumnA

My second suggestion was to see if this worked first:

DerivedColumn Expression

Add as new column ColumnB != ColumnC ? "y" : ColumnA

I'm clutching at straws a little bit but if I were you I would definately try to recreate the problem by adding it as a new column rather than replacing ColumnA.

-Jamie

|||

Hi Jamie,

adding a column works fine. It's replacing an exising column that has the "problem".

I should point out as well that the issue only arises when you use an IF condition in the expression.

So this is OK

DerivedColumn Expression

Replace ColumnA ColumnA+"Hello"

This will replace ColumnA with what was in ColumnA + "Hello"

This is NOT OK

DerivedColumn Expression

Replace ColumnA ColumnB != ColumnC ? "y" : ColumnA

So if ColumnB = ColumnC, then ColumnA is set to NULL - NOT what ColumnA was before the expression was applied.

I reckon its a bug.

|||

Hi Dave,

Can you post a simple repro that doesn't reply on external data sources (i.e. just create the same data using a script source component) and then post it up here?

To post up here, just copy the contents of your .dtsx file into your reply.

Thanks

Jamie

|||

Sure,

I'll do it tomorrow.

Speak then.

|||Have you checked if Column B or C is NULL? If so you will always get NULL out. Example here http://wiki.sqlis.com/default.aspx/SQLISWiki/Expressions.html|||

You can quickly test for what Darren is saying by changing your expression like this:

false ? "y" : ColumnA

If ColumnB or ColumnC are NULL, and you want to fail the comparison in that case,you can make your expression like this:

!ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

If you want the condition to do NULL comparisions (that is, you want ColumnB != ColumnC to return true if one is NULL and the other is not, you might need something more like:

(ISNULL(ColumnB) != ISNULL(ColumnC)) || !ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

|||

Your right Darren - it was the NULL values. aaargh!!!

Thanks for everyone's input.

Derived Column from a Condition statement

I've found that there is no such thing as a Case Statement in the Derived Column task in the data flow objects. I've written a ternary statement instead. I can't seem to get it to work exactly how I want it to. For example

AccountCategory == "E" ? 1 : 2

Works fine but the following doesn't

AccountCategory == "E" ? CreditAmount : DebitAmount

The CreditAmount and DebitAmount Fields are spelled correctly, the field type of AccountCategory is String and the Field type of CreditAmount and DebitAmount is numeric, but that seems to be the same situation that I have in the first example that works. Am I missing something? I'd appreciate any advice anyone has to offer. Thanks,

Bill

Only thing you did not mention is what is the data type set for the derived column itself, it should be numeric to match the Credit and Debit columns. The scale and precison must match for all three too.

That is all just guess work, and the syntax looks fine, but what would really help is the actual error message you get.

|||It looks like I stumbled on to how to fix this, I don't know if it is a bug or it is designed to work this way. If I copied the second statement and then deleted the whole row for that particular derived column then copied the statement back into the expression column before naming the column it works. Go figure.|||

Glad to hear you have it working.

Most likely, as Darren suggests, it was a datatype mismatch. However, if you could repro and report the error message, we could give you better advice.

Donald

Derived column expression question......

Greetings,

I have an existing 2000 DTS package that uses the following case statement:

Case

When TERMS_PERCENT ='0'

then 0

else cast(TERMS_PERCENT as decimal(6,2))/100

end as TermsPct

to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

mjanzou wrote:

Greetings,

I have an existing 2000 DTS package that uses the following case statement:

Case

When TERMS_PERCENT ='0'

then 0

else cast(TERMS_PERCENT as decimal(6,2))/100

end as TermsPct

to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

Something like this perhaps?

[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0

-Jamie

|||

Jamie Thomson wrote:

[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0

-Jamie

The TRUE and FALSE values are reversed . Try:

Code Snippet

[TERMS_PERCENT]== "0" ? (DT_NUMERIC,6,2)0 : ((DT_NUMERIC,6,2)[TERMS_PERCENT]) / 100

|||

Ahhh goddamnit!!!