Another question on CASE WHEN THEN....

My fellow SQL expert is out of the office today, and I need help!

What is the syntax when you have (2) conditions that both must be met before the result? Ex:

Commission = CASE WHEN CommissionEvent = 'Portfolio'

And NewCustomer = Yes

THEN it does a formula.... and then there's a second set of (2) conditions that both must be met to produce another result.

WHEN CommissionEvent = 'Portfolio'

And NewCustomer = No

THEN it does a different formula. If it doesn't meet either set of conditions, the result should be 0.

I tried it they way I thought it should work but I am getting an invalid syntax error.

I hope my question makes sense.Commission = CASE WHEN CommissionEvent = 'Portfolio'
And NewCustomer = Yes
THEN /* it does a formula */
WHEN CommissionEvent = 'Portfolio'
And NewCustomer = No
THEN /* it does a different formula */
ELSE 0
END you too can be an SQL expert ;)|||Ex-spurt

a former drip under pressure.....|||Well shoot, I was putting in THEN WHEN where the AND should go-- DUH! I should have just typed it just like in my question!

I was trying to word it similarly to how an IF THEN ELSE statement goes which would have read "THEN IF" in that spot so I thought "THEN WHEN" would have been the substitution.

I like that definition of expert, Brett!! :D

THANKS!|||But I need another ELSE 0 somewhere for if the first statement evaluates as false. I'm getting the 0 when the

CommissionEvent = 'Portfolio' and NewCustomer field = No

but a NULL when the

CommissionEvent <> 'Portfolio' and the NewCustomer field = No.

I tried to put an additional ELSE 0 next to the ending ELSE 0 but got a syntax error. I also tried to put the ELSE 0 before the 2nd WHEN statement but also got a syntax error.|||Post what you have...it'll be easier for to to interprete...

You can have only 1 ELSE...|||Originally posted by notasoccermom
But I need another ELSE 0 somewhere for if the first statement evaluates as false. I'm getting the 0 when the

CommissionEvent = 'Portfolio' and NewCustomer field = No

but a NULL when the

CommissionEvent <> 'Portfolio' and the NewCustomer field = No.

I tried to put an additional ELSE 0 next to the ending ELSE 0 but got a syntax error. I also tried to put the ELSE 0 before the 2nd WHEN statement but also got a syntax error.

What about this version?

Commission = CASE WHEN CommissionEvent = 'Portfolio'
then case
when NewCustomer = Yes
THEN /* it does a formula */
when NewCustomer = No
THEN /* it does a different formula */
else 0
end
ELSE 0
END|||Never mind!! I just figured it out. I had a formula with an alias name in my view that I was trying to use in this CASE statement, but I also had that same column name in one of my tables. So it was pulling the data from my table with that column name which had null values, so I had to rename my alias to something else and use the actual formula for the alias instead of the alias name in the CASE statement. Now it works fine!

Sometimes you just wrack your brain so hard on something that you can't see the forest for the trees, or the trees for the forest! And what I thought was the problem really wasn't at all!

Thanks for everyone's help, I'll have to keep this forum in mind when I can't get answers at work!!|||Originally posted by notasoccermom
I'll have to keep this forum in mind when I can't get answers at work!!

You get answers at work?

Another question (IN keyword)

Is possible in SQLSERVER to use a syntax like this?

select x,y from table_1 where (x,y) not in (select h,k from table_2)

I've tried, but it doen't work.

Do you know any workaround?

Thank you

Fede"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:cjc1bn$nsk$1@.atlantis.cu.mi.it...
> Is possible in SQLSERVER to use a syntax like this?
> select x,y from table_1 where (x,y) not in (select h,k from table_2)
> I've tried, but it doen't work.
> Do you know any workaround?
> Thank you
> Fede

No, that's not supported in MSSQL - you can use a correlated subquery
instead:

select x, y
from table_1 t1
where not exists (
select *
from table_2 t2
where t1.x = t2.h
and t1.y = t2.k)

Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
migration guide (they do exist for other database platforms), but you might
still find some useful stuff here:

http://www.microsoft.com/sql/evalua...ibm/default.asp

Simon|||> No, that's not supported in MSSQL - you can use a correlated subquery
> instead:
> select x, y
> from table_1 t1
> where not exists (
> select *
> from table_2 t2
> where t1.x = t2.h
> and t1.y = t2.k)
> Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
> migration guide (they do exist for other database platforms), but you
might
> still find some useful stuff here:
> http://www.microsoft.com/sql/evalua...ibm/default.asp
> Simon

Thank you very much!

Fede|||"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message news:<cjdp11$nm6$1@.atlantis.cu.mi.it>...
> > No, that's not supported in MSSQL - you can use a correlated subquery
> > instead:
> > select x, y
> > from table_1 t1
> > where not exists (
> > select *
> > from table_2 t2
> > where t1.x = t2.h
> > and t1.y = t2.k)
> > Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
> > migration guide (they do exist for other database platforms), but you
> might
> > still find some useful stuff here:
> > http://www.microsoft.com/sql/evalua...ibm/default.asp
> > Simon
> Thank you very much!
> Fede

Or, somewhat equivalently, you could do:

SELECT t1.x,t1.y FROM Table_1 t1 LEFT JOIN Table_2 t2 ON t1.x = t2.h
and t1.y = t2.k WHERE t2.h IS null

Sorry, just have an intense dislike of "not in" and "not exists". This
second form may (or may not, YMMV) perform better

annoying cut and paste behavior in query analyzer

background: sql2k on nt5 box..
is there a way to get rid of all those syntax such as
'COLLATE SQL_Latin1_General_CP1_CI_AS '
'[ ]' when cut and paste table script from EM to Query Analyzer?
CREATE TABLE [analyte_property] (
[ESPKEY] [int] NOT NULL ,
[method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
it would be ideal to have script look like this
CREATE TABLE analyte_property (
ESPKEY int NOT NULL ,
method_1 char (35) NULL ,
method_2 char (35) NULL ,
method_3 char (35) NULL ,
method_4 char (35) NULL ,
method_5 char (35) NULL ,
method_6 char (35) NULL ,
method_7 char (35) NULL ,
method_8 char (35) NULL ,
method_9 char (35) NULL ,
method_10 char (35) NULL
) ON PRIMARY
GO
Thank you.
Steve,
In the 'Generate sql script' dialog box 'formatting' tab, check the option
'Script sql7 comptatible feature'.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> background: sql2k on nt5 box..
>
> is there a way to get rid of all those syntax such as
> 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> '[ ]' when cut and paste table script from EM to Query Analyzer?
>
> CREATE TABLE [analyte_property] (
> [ESPKEY] [int] NOT NULL ,
> [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> it would be ideal to have script look like this
> CREATE TABLE analyte_property (
> ESPKEY int NOT NULL ,
> method_1 char (35) NULL ,
> method_2 char (35) NULL ,
> method_3 char (35) NULL ,
> method_4 char (35) NULL ,
> method_5 char (35) NULL ,
> method_6 char (35) NULL ,
> method_7 char (35) NULL ,
> method_8 char (35) NULL ,
> method_9 char (35) NULL ,
> method_10 char (35) NULL
> ) ON PRIMARY
> GO
>
> Thank you.
>
>
|||Thank you, but i already knew that.
that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
when i use EM, genrate sql script function.
i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
besides, is there a way to get rid of those square brackets?
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> Steve,
> In the 'Generate sql script' dialog box 'formatting' tab, check the option
> 'Script sql7 comptatible feature'.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
>
|||I've seen this kind of post so many times...
When you use a GUI interface to do programming you get garbage - square
brackets, verbose collation - whatever...
Using EM to design tables, or create indexed is ACCESS-like. EM is the
"lowest common denominator"...
Our first-commandment in our shop is to script everything. We script BULK
INSERT's, INDEX creation, table creation - STORED PROC creation.
That means we create text files, with file extensions of .SQL and use those
in QA to make changes to the database. Not doing it this way would mean we
were a single-stop shop - not developing code for the hundreds of clients we
want to meet.
When we script a SPROC creation we even add the "GRANT..." permission code -
so that the proper DB role has access to the SPROC. If you use EM to output
a script of a SPROC it doesn't contain any GRANT info - that's weak.
Having a text file for every DB change or definition means we can use VISUAL
SOURCE safe... Yadda, yadda, yadda...
Sorry for ranting...
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
option
>
|||Steve,
None that Iam aware of, except may be a mass replace of 'COLLATE ...' to ''
:-)
[vbcol=seagreen]
Why?In case, any of the column name was like 'method 1' then without [], the
script would fail.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
option
>

annoying cut and paste behavior in query analyzer

background: sql2k on nt5 box..
is there a way to get rid of all those syntax such as
'COLLATE SQL_Latin1_General_CP1_CI_AS '
'[ ]' when cut and paste table script from EM to Query Analyzer?
CREATE TABLE [analyte_property] (
[ESPKEY] [int] NOT NULL ,
[method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
it would be ideal to have script look like this
CREATE TABLE analyte_property (
ESPKEY int NOT NULL ,
method_1 char (35) NULL ,
method_2 char (35) NULL ,
method_3 char (35) NULL ,
method_4 char (35) NULL ,
method_5 char (35) NULL ,
method_6 char (35) NULL ,
method_7 char (35) NULL ,
method_8 char (35) NULL ,
method_9 char (35) NULL ,
method_10 char (35) NULL
) ON PRIMARY
GO
Thank you.Steve,
In the 'Generate sql script' dialog box 'formatting' tab, check the option
'Script sql7 comptatible feature'.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> background: sql2k on nt5 box..
>
> is there a way to get rid of all those syntax such as
> 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> '[ ]' when cut and paste table script from EM to Query Analyzer?
>
> CREATE TABLE [analyte_property] (
> [ESPKEY] [int] NOT NULL ,
> [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> it would be ideal to have script look like this
> CREATE TABLE analyte_property (
> ESPKEY int NOT NULL ,
> method_1 char (35) NULL ,
> method_2 char (35) NULL ,
> method_3 char (35) NULL ,
> method_4 char (35) NULL ,
> method_5 char (35) NULL ,
> method_6 char (35) NULL ,
> method_7 char (35) NULL ,
> method_8 char (35) NULL ,
> method_9 char (35) NULL ,
> method_10 char (35) NULL
> ) ON PRIMARY
> GO
>
> Thank you.
>
>|||Thank you, but i already knew that.
that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
when i use EM, genrate sql script function.
i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
besides, is there a way to get rid of those square brackets?
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> Steve,
> In the 'Generate sql script' dialog box 'formatting' tab, check the option
> 'Script sql7 comptatible feature'.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
>|||I've seen this kind of post so many times...
When you use a GUI interface to do programming you get garbage - square
brackets, verbose collation - whatever...
Using EM to design tables, or create indexed is ACCESS-like. EM is the
"lowest common denominator"...
Our first-commandment in our shop is to script everything. We script BULK
INSERT's, INDEX creation, table creation - STORED PROC creation.
That means we create text files, with file extensions of .SQL and use those
in QA to make changes to the database. Not doing it this way would mean we
were a single-stop shop - not developing code for the hundreds of clients we
want to meet.
When we script a SPROC creation we even add the "GRANT..." permission code -
so that the proper DB role has access to the SPROC. If you use EM to output
a script of a SPROC it doesn't contain any GRANT info - that's weak.
Having a text file for every DB change or definition means we can use VISUAL
SOURCE safe... Yadda, yadda, yadda...
Sorry for ranting...
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
option[vbcol=seagreen]
>|||Steve,
None that Iam aware of, except may be a mass replace of 'COLLATE ...' to ''
:-)

Why?In case, any of the column name was like 'method 1' then without [],
the
script would fail.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
option[vbcol=seagreen]
>

annoying cut and paste behavior in query analyzer

background: sql2k on nt5 box..
is there a way to get rid of all those syntax such as
'COLLATE SQL_Latin1_General_CP1_CI_AS '
'[ ]' when cut and paste table script from EM to Query Analyzer?
CREATE TABLE [analyte_property] (
[ESPKEY] [int] NOT NULL ,
[method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
it would be ideal to have script look like this
CREATE TABLE analyte_property (
ESPKEY int NOT NULL ,
method_1 char (35) NULL ,
method_2 char (35) NULL ,
method_3 char (35) NULL ,
method_4 char (35) NULL ,
method_5 char (35) NULL ,
method_6 char (35) NULL ,
method_7 char (35) NULL ,
method_8 char (35) NULL ,
method_9 char (35) NULL ,
method_10 char (35) NULL
) ON PRIMARY
GO
Thank you.Steve,
In the 'Generate sql script' dialog box 'formatting' tab, check the option
'Script sql7 comptatible feature'.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> background: sql2k on nt5 box..
>
> is there a way to get rid of all those syntax such as
> 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> '[ ]' when cut and paste table script from EM to Query Analyzer?
>
> CREATE TABLE [analyte_property] (
> [ESPKEY] [int] NOT NULL ,
> [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> it would be ideal to have script look like this
> CREATE TABLE analyte_property (
> ESPKEY int NOT NULL ,
> method_1 char (35) NULL ,
> method_2 char (35) NULL ,
> method_3 char (35) NULL ,
> method_4 char (35) NULL ,
> method_5 char (35) NULL ,
> method_6 char (35) NULL ,
> method_7 char (35) NULL ,
> method_8 char (35) NULL ,
> method_9 char (35) NULL ,
> method_10 char (35) NULL
> ) ON PRIMARY
> GO
>
> Thank you.
>
>|||Thank you, but i already knew that.
that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
when i use EM, genrate sql script function.
i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
besides, is there a way to get rid of those square brackets?
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> Steve,
> In the 'Generate sql script' dialog box 'formatting' tab, check the option
> 'Script sql7 comptatible feature'.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > background: sql2k on nt5 box..
> >
> >
> > is there a way to get rid of all those syntax such as
> > 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> > '[ ]' when cut and paste table script from EM to Query Analyzer?
> >
> >
> >
> > CREATE TABLE [analyte_property] (
> > [ESPKEY] [int] NOT NULL ,
> > [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > it would be ideal to have script look like this
> >
> > CREATE TABLE analyte_property (
> > ESPKEY int NOT NULL ,
> > method_1 char (35) NULL ,
> > method_2 char (35) NULL ,
> > method_3 char (35) NULL ,
> > method_4 char (35) NULL ,
> > method_5 char (35) NULL ,
> > method_6 char (35) NULL ,
> > method_7 char (35) NULL ,
> > method_8 char (35) NULL ,
> > method_9 char (35) NULL ,
> > method_10 char (35) NULL
> >
> > ) ON PRIMARY
> > GO
> >
> >
> >
> > Thank you.
> >
> >
> >
>|||I've seen this kind of post so many times...
When you use a GUI interface to do programming you get garbage - square
brackets, verbose collation - whatever...
Using EM to design tables, or create indexed is ACCESS-like. EM is the
"lowest common denominator"...
Our first-commandment in our shop is to script everything. We script BULK
INSERT's, INDEX creation, table creation - STORED PROC creation.
That means we create text files, with file extensions of .SQL and use those
in QA to make changes to the database. Not doing it this way would mean we
were a single-stop shop - not developing code for the hundreds of clients we
want to meet.
When we script a SPROC creation we even add the "GRANT..." permission code -
so that the proper DB role has access to the SPROC. If you use EM to output
a script of a SPROC it doesn't contain any GRANT info - that's weak.
Having a text file for every DB change or definition means we can use VISUAL
SOURCE safe... Yadda, yadda, yadda...
Sorry for ranting...
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> > Steve,
> >
> > In the 'Generate sql script' dialog box 'formatting' tab, check the
option
> > 'Script sql7 comptatible feature'.
> >
> > --
> > Dinesh
> > SQL Server MVP
> > --
> > --
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > background: sql2k on nt5 box..
> > >
> > >
> > > is there a way to get rid of all those syntax such as
> > > 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> > > '[ ]' when cut and paste table script from EM to Query Analyzer?
> > >
> > >
> > >
> > > CREATE TABLE [analyte_property] (
> > > [ESPKEY] [int] NOT NULL ,
> > > [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > it would be ideal to have script look like this
> > >
> > > CREATE TABLE analyte_property (
> > > ESPKEY int NOT NULL ,
> > > method_1 char (35) NULL ,
> > > method_2 char (35) NULL ,
> > > method_3 char (35) NULL ,
> > > method_4 char (35) NULL ,
> > > method_5 char (35) NULL ,
> > > method_6 char (35) NULL ,
> > > method_7 char (35) NULL ,
> > > method_8 char (35) NULL ,
> > > method_9 char (35) NULL ,
> > > method_10 char (35) NULL
> > >
> > > ) ON PRIMARY
> > > GO
> > >
> > >
> > >
> > > Thank you.
> > >
> > >
> > >
> >
> >
>|||Steve,
None that Iam aware of, except may be a mass replace of 'COLLATE ...' to ''
:-)
>> besides, is there a way to get rid of those square brackets?
Why?In case, any of the column name was like 'method 1' then without [], the
script would fail.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> > Steve,
> >
> > In the 'Generate sql script' dialog box 'formatting' tab, check the
option
> > 'Script sql7 comptatible feature'.
> >
> > --
> > Dinesh
> > SQL Server MVP
> > --
> > --
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > background: sql2k on nt5 box..
> > >
> > >
> > > is there a way to get rid of all those syntax such as
> > > 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> > > '[ ]' when cut and paste table script from EM to Query Analyzer?
> > >
> > >
> > >
> > > CREATE TABLE [analyte_property] (
> > > [ESPKEY] [int] NOT NULL ,
> > > [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > it would be ideal to have script look like this
> > >
> > > CREATE TABLE analyte_property (
> > > ESPKEY int NOT NULL ,
> > > method_1 char (35) NULL ,
> > > method_2 char (35) NULL ,
> > > method_3 char (35) NULL ,
> > > method_4 char (35) NULL ,
> > > method_5 char (35) NULL ,
> > > method_6 char (35) NULL ,
> > > method_7 char (35) NULL ,
> > > method_8 char (35) NULL ,
> > > method_9 char (35) NULL ,
> > > method_10 char (35) NULL
> > >
> > > ) ON PRIMARY
> > > GO
> > >
> > >
> > >
> > > Thank you.
> > >
> > >
> > >
> >
> >
>

AND - OR Sql syntax problem

Need a OR clause in the below statement but can't get it right.

Existing:

WHERE(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND(L_PicTable.LinkType ='C')AND(L_PicTable.PicEnabled = 1)

NEED it to be:

WHERE(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND(L_PicTable.LinkType ='C' ORL_PicTable.LinkType ='CS')AND(L_PicTable.PicEnabled = 1)

Can't get this clause to work - are my ('s wrong?

AND(L_PicTable.LinkType ='C' ORL_PicTable.LinkType ='CS')

Try with this:

L_PicTable.LinkType =N'C' OR L_PicTable.LinkType =N'CS'

|||

zoltac007:

AND(L_PicTable.LinkType ='C' ORL_PicTable.LinkType ='CS')

You can useANDL_PicTable.LinkType in ( 'C' ,'CS' ). One more thing, you don't need to place each and every AND clause in a separate pair of braces, they are used just when you want the output ( true or false ) to be returned back considering a set of the conditions as a whole.

|||

Hello zoltac,

There's no problem with the ()'s.

Your clause rewritten in boolean logic is:

(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND (L_PicTable.LinkType ='C') AND(L_PicTable.PicEnabled = 1)

OR

(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND (L_PicTable.LinkType ='CS') AND(L_PicTable.PicEnabled = 1)

What is the statement supposed to do?

Jeroen Molenaar.

|||

I am trying to select all records where Enabled is true, City, State match the page parameters AND the LinkType is either C or CS. There are only three values for the LinkType field, C, S or CS.