Copy Database

Is there a way to copy a database using TSQL?

When we set up a new customer we would like to make a copy of the existing database and set up a new database for them. Is there a way to programmatically do this?

There are different ways you could approach this depending on your needs. If one day of data latency isn't an issue, you could just restore a nightly backup of the database in question as a new database name. You could setup a stored procedure to receive variables for database name, physical file name, and any other specific options. Then parse and execute your restore statement.

http://msdn2.microsoft.com/en-us/library/ms186858(d=ide).aspx

|||I was looking for a more direct approach. I know there is the Copy Database wizard. Are there commands that I can use that use the SMO version of that wizard?|||You can use the "VS2003 setup project" to deploy the database to the client. VS 2005 has better support for these kind of scenario.|||

One easy way to set it up (if you have two machines runing SQL Server) is to use the Copy Database Wizard and save either the DTS in the database or save to a script. Then I think you might be able to tweak the script to modify the target database name and filenames.

I haven't done this before so no guarantees. Once you had it setup though as a DTS, you could use Global variables to pass in the databasename and and filenames. You could then execute the DTS from T-Sql and pass in the variables.

Otherwise, I think you can use SMO although it will take looking closely to see the best path. Even using SMO, the easiest way may end up being scripting a backup and restore of the database. It seems to me that it is the cleanest option.

|||We dont' want to "deploy" a database. It need to be a SQL SP that we can run that will copy an existing "base" database in our server to a new database.|||

The copy database wizard has no facility to create a script.

This needs to be something that can be run through the application by an administrator.

|||At the end of the wizard, you can set it to run on a schedule instead of immediately. In the schedule, just set it for a previous date so it won't ever run. Then it will create the copy package for you.|||

That was a great idea....BUT...the script button is disabled.

|||

1) just back up the original database and make a copy

2) Create a new database

3) Then use attach and dettach command

EXEC sp_attach_db @.dbname = N'AdventureWorks',
@.filename1 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf' ;

just set the parameters..all these you can do in one SP.

Converting percent-decoded data, or characters to int

Say I have a percent encoded expression like www%2emysite%2ecom. An unencoded representation would be www.mysite.com.

I have written a tsql regex function to parse an input string and replace any legal percent-encoded substring with its ascii equivalent, returning the unencoded version of the input string. However I'm stuck on one crucial step: converting a percent-encoded substring (ex: '%2e') or its hexadecimal string representation ('0x2e') to an integer.

I've tried CONVERT(bigInt, '0x2e') but of course that isn't working.

What is the best way to go about this? I've been all over the sql server books online, search engines, and other forums. I can't get past this one step. Am I on the right track, or should I look at it a different way?

Without knowing more about the rest of your code, you can do this: exec ('select CONVERT(int, ' + '0x2e' + ') '), though not in a t-sql function (but I don't know how you are doing regex in a T-SQL function)

|||

you have to convert the varchar value to binary value..

You can't direcly assign this value from the varchar varibale/column, if you convert the varchar value it will create a binary equalent of the string..

I once used the following logic to overcome this issue,

Declare @.ValueString as Varchar(100)
Set @.ValueString = '0x2e'

Declare @.Value as VarBinary
Declare @.sql as NVarchar(1000)

Select @.sql = N'select @.val=' + @.ValueString
Exec sp_executesql @.sql, N'@.val varbinary output', @.Value output

Select char(convert(bigint, @.value))

--Result: .

Here we are assinging the Binary value directly to the VarBinary variable, with out using the convert function..But i am not sure how it will help you to create a generic function(since inside the funcation you can't able to call sp_executesql)..

|||Thank you.

I don't know how you are doing regex in a T-SQL function

My t-sql regex find function works via vbScript RegExp class. I'm sure it's not the most efficient ,but it does work very well.

I still haven't made any progress with the current issue. I don't understand why a character expression that is also a well-formed hexadecimal number can't be easily converted into an integer. Sigh...

Converting Oracle SQL to TSQL

Hello,

I am new to sql*server, but have used Oracle for years. Can anyone
recommend a good online guide for converting all the Oracle SQL I know
to TSQL?

tia,

Mikemscgloss@.yahoo.com (Ixnay) wrote in message news:<21326100.0402041527.26afddc0@.posting.google.com>...
> Hello,
> I am new to sql*server, but have used Oracle for years. Can anyone
> recommend a good online guide for converting all the Oracle SQL I know
> to TSQL?
> tia,
> Mike

This might help to get you started:

http://www.microsoft.com/technet/tr...part2/c0761.asp

Unfortunately, since there are so many differences in language and
architecture, there's no easy solution to migrating code. There are
third party apps available to do some conversion, but they have a
number of limitations.

Simon

Converting MS query to SQL Server 2000

I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping some one can give me some
guidance. I believe I have the first portion of the query correct but
do believe this requires a "NESTED" argument. This is where I am
lost.

My Original MS ACCESS Query reads--

SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTRA_VENDOR1])))),

IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTER_VENDOR1]))))) AS CPM,
[CPM]*[MOU] AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC;

I have tried to re-write this in SQL SERVER as --

SELET DISTINCT REGION TRAFIC.*,
CASE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTRA_VENDOR1
ELSE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTER_VENDOR1
END
AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC

My challenge is the Case portion of the query and the nesting! I am
not sure if I have the correct syntax or even chose the correct
argument for my purpose.

Any guidance is appreciated.Hi

Try something like:

SELECT DISTINCT R.*,
CASE
WHEN SUBSTRING([SWITCH CLLI],5,2)=[TERM STATE] THEN
CASE
WHEN [CARRIER]="VENDOR4" THEN D.INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN D.INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN D.INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN D.INTRA_VENDOR1
END
ELSE
CASE
WHEN [CARRIER]="VENDOR4" THEN D.INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN D.INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN D.INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN D.INTER_VENDOR1
END
END
END AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC R LEFT JOIN [DOMESTIC LD RATES] D ON R.RATEKEY =
D.RATEKEY
WHERE R.[TERM LATA])=R.[LATA]
ORDER BY R.[TERM LATA] DESC

It would be better to use the table alias for all columns including
[CARRIER], [SWITCH CLLI], [TERM STATE] even though they may be unique, but
as you don't post DDL I don't know which table they are in. It is also not a
good idea to use * in production code.

John

"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291633.29c8fd64@.posting.google.c om...
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping some one can give me some
> guidance. I believe I have the first portion of the query correct but
> do believe this requires a "NESTED" argument. This is where I am
> lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My challenge is the Case portion of the query and the nesting! I am
> not sure if I have the correct syntax or even chose the correct
> argument for my purpose.
> Any guidance is appreciated.|||I was able to make more progress and this is what I have now--

The critical piece of the query is that it should look at 2 characters
in the [OFFICE_CLLI] column and then compare it to the 2 character
state abbreviation in the [TERM_STATE] column to see if it is
"INTRASTATE" or "INTERSTATE".
After determining the INTER/INTRA question check the [CARRIER] to see
who is the vendor. With those two questions answered the query pulls
the appropriate CPM (COST PER MINUTE) from the table [DOMESTIC LD
RATES] based on a JOIN from the [RATEKEY] column.

And with the correct [CPM] pulled it turns around multiplies the value
from the [MOU] column to give me a COST.

My Original MS ACCESS Query is--

SELECT DISTINCTROW
([TERM_LATA] & "\" & [TERM_STATE] & "\" & [OCN]) AS RATEKEY,
JANUARY_TEST.DATE, JANUARY_TEST.CXRKEY, JANUARY_TEST.AREA,
JANUARY_TEST.REGION,
JANUARY_TEST.MARKET, JANUARY_TEST.MTA, JANUARY_TEST.LATA,
JANUARY_TEST.AKA, JANUARY_TEST.MARS_NAME,
JANUARY_TEST.OFFICE_CLLI, JANUARY_TEST.Carrier, JANUARY_TEST.SWITCH,
JANUARY_TEST.NPA_NXX, JANUARY_TEST.MOU,
JANUARY_TEST.[TERM MTA], JANUARY_TEST.[TERM STATE], JANUARY_TEST.[TERM
LATA], JANUARY_TEST.[TERM RC],
JANUARY_TEST.RC_ID, JANUARY_TEST.OCN_NAME, JANUARY_TEST.OCN,
JANUARY_TEST.CATEGORY,

IIf(Mid([OFFICE_CLLI],5,2)=[TERM STATE],
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD RATES]![INTRA_VENDOR1])))),

IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD RATES]![INTER_VENDOR1]))))) AS
CPM,

[CPM]*[MOU] AS COST INTO JANUARY_TEST_RATES2
FROM JANUARY_TEST LEFT JOIN [DOMESTIC LD RATES] ON
JANUARY_TEST.RATEKEY = [DOMESTIC LD RATES].RATEKEY
ORDER BY JANUARY_TEST.[TERM LATA] DESC;

MS ACCESS it works well I now need to go the SQL SERVER to handle my
databases. When I use Query Analyser I get a message invalid syntax
near ELSE.

Any additional advice. I have been able to get it this far in
converting to SQL SERVER 2000--

SELECT DISTINCT
dbo.JANUARY_TEST.[T_LATA] + '\' + dbo.JANUARY_TEST.[T_STATE] + '\' +
dbo.JANUARY_TEST.[OCN] AS RATEKEY,
dbo.JANUARY_TEST.[DATE],dbo.JANUARY_TEST.CXRKEY,dbo.JANUARY_TEST.Area,dbo .JANUARY_TEST.Region,
dbo.JANUARY_TEST.Market,dbo.JANUARY_TEST.AKA,dbo.J ANUARY_TEST.MARS_NAME,dbo.JANUARY_TEST.O_MTA,
dbo.JANUARY_TEST.O_MTA_NAME,dbo.JANUARY_TEST.O_STA TE,dbo.JANUARY_TEST.O_LATA,dbo.JANUARY_TEST.O_LATA _NAME,
dbo.JANUARY_TEST.OFFICE_CLLI,dbo.JANUARY_TEST.Trun k,dbo.JANUARY_TEST.Carrier,dbo.JANUARY_TEST.NPA_NX X,
dbo.JANUARY_TEST.CALLS,dbo.JANUARY_TEST.MOU,dbo.JA NUARY_TEST.TANDEM,dbo.JANUARY_TEST.T_MTA,dbo.JANUA RY_TEST.T_MTA_NAME,
dbo.JANUARY_TEST.T_STATE,dbo.JANUARY_TEST.T_LATA,d bo.JANUARY_TEST.[RC_ABBRE],dbo.JANUARY_TEST.RC_ID,SWITCH,
dbo.JANUARY_TEST.[OCN],dbo.JANUARY_TEST.[OCN_NAME],dbo.JANUARY_TEST.[CATEGORY],

CASE WHEN (SUBSTRING(dbo.JANUARY_TEST.[OFFICE_CLLI], 5, 2) =
dbo.JANUARY_TEST.[T_STATE]) THEN (
(CASE WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR4' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR4
WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR3' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR3
WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR2' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR2
WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR1' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR1
ELSE TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_TANDEM
END)
ELSE (CASE WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR4' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR4
WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR3' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR3
WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR2' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR2
WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR1' THEN
TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR1
ELSE TELCO.dbo.DOMESTIC_LD_RATES2.INTER_TANDEM
END)) AS 'CPM'
INTO MARS_5050.DBO.JANUARY_TEST_RATES2
FROM dbo.JANUARY_TEST LEFT JOIN TELCO.dbo.DOMESTIC_LD_RATES2 ON
RATEKEY = TELCO.dbo.DOMESTIC_LD_RATES2.RATEKEY
ORDER BY dbo.JANUARY_TEST.[T_LATA] DESC
OPTION (MAXDOP 2)

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<v5yEc.6651$wp6.72768805@.news-text.cableinet.net>...
> Hi
> Try something like:
> SELECT DISTINCT R.*,
> CASE
> WHEN SUBSTRING([SWITCH CLLI],5,2)=[TERM STATE] THEN
> CASE
> WHEN [CARRIER]="VENDOR4" THEN D.INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN D.INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN D.INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN D.INTRA_VENDOR1
> END
> ELSE
> CASE
> WHEN [CARRIER]="VENDOR4" THEN D.INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN D.INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN D.INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN D.INTER_VENDOR1
> END
> END
> END AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC R LEFT JOIN [DOMESTIC LD RATES] D ON R.RATEKEY =
> D.RATEKEY
> WHERE R.[TERM LATA])=R.[LATA]
> ORDER BY R.[TERM LATA] DESC
> It would be better to use the table alias for all columns including
> [CARRIER], [SWITCH CLLI], [TERM STATE] even though they may be unique, but
> as you don't post DDL I don't know which table they are in. It is also not a
> good idea to use * in production code.
> John
> "wiredog" <wiredog@.comcast.net> wrote in message
> news:b3af3ec6.0406291633.29c8fd64@.posting.google.c om...
> > I am struggling rewriting my query from MS Access' IIF, Then to SQL
> > Servers TSQL language. I am hoping some one can give me some
> > guidance. I believe I have the first portion of the query correct but
> > do believe this requires a "NESTED" argument. This is where I am
> > lost.
> > My Original MS ACCESS Query reads--
> > SELECT DISTINCTROW REGION_TRAFIC.*,
> > IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> > IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> > IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> > IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> > IIf([Carrier]="VENDOR1",[DOMESTIC LD
> > RATES]![INTRA_VENDOR1])))),
> > IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> > IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> > IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> > IIf([Carrier]="VENDOR1",[DOMESTIC LD
> > RATES]![INTER_VENDOR1]))))) AS CPM,
> > [CPM]*[MOU] AS COST
> > INTO INTRALATA_LD
> > FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> > REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> > WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> > ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> > I have tried to re-write this in SQL SERVER as --
> > SELET DISTINCT REGION TRAFIC.*,
> > CASE
> > WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> > RATES].INTRA_VENDOR4
> > WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> > RATES].INTRA_VENDOR3
> > WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> > RATES].INTRA_VENDOR2
> > WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> > RATES].INTRA_VENDOR1
> > ELSE
> > WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> > RATES].INTER_VENDOR4
> > WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> > RATES].INTER_VENDOR3
> > WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> > RATES].INTER_VENDOR2
> > WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> > RATES].INTER_VENDOR1
> > END
> > AS CPM
> > CPM*MOU AS COST
> > INTO INTRALATA_LD
> > FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> > REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> > WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> > ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> > My challenge is the Case portion of the query and the nesting! I am
> > not sure if I have the correct syntax or even chose the correct
> > argument for my purpose.
> > Any guidance is appreciated.|||Hi

There is a missing END for the third CASE statement. Although posting tends
to mess up the formatting, you should look at using indentation and layout
so that you can see where things match. Accesscan be a pain in that it will
use fully qualified table names and put an excessive number of brackets into
the statement. You may want to start writing your SQL in Query Analyser. I
would also recommend that you look at storing your code in a source code
control system, then you can monitor changes and maintain an safe archive
of your code. I would also not use query hints unless you see a specific
problem in production.

SELECT DISTINCT
ISNULL(J.[T_LATA],'') + '\' +
ISNULL(J.[T_STATE],'') + '\' +
ISNULL(J.[OCN],'') AS RATEKEY,
J.[DATE],
J.CXRKEY,
J.Area,
J.Region,
J.Market,
J.AKA,
J.MARS_NAME,
J.O_MTA,
J.O_MTA_NAME,
J.O_STATE,
J.O_LATA,
J.O_LATA_NAME,
J.OFFICE_CLLI,
J.Trunk,
J.Carrier,
J.NPA_NXX,
J.CALLS,
J.MOU,
J.TANDEM,
J.T_MTA,
J.T_MTA_NAME,
J.T_STATE,
J.T_LATA,
J.[RC_ABBRE],
J.RC_ID,SWITCH,
J.[OCN],
J.[OCN_NAME],
J.[CATEGORY],
CASE WHEN SUBSTRING(J.[OFFICE_CLLI], 5, 2) = J.[T_STATE] THEN
CASE WHEN J.[CARRIER] = 'VENDOR4' THEN D.INTRA_VENDOR4
WHEN J.[CARRIER] = 'VENDOR3' THEN D.INTRA_VENDOR3
WHEN J.[CARRIER] = 'VENDOR2' THEN D.INTRA_VENDOR2
WHEN J.[CARRIER] = 'VENDOR1' THEN D.INTRA_VENDOR1
ELSE D.INTRA_TANDEM
END
ELSE
CASE WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR4' THEN D.INTER_VENDOR4
WHEN J.[CARRIER] = 'VENDOR3' THEN D.INTER_VENDOR3
WHEN J.[CARRIER] = 'VENDOR2' THEN D.INTER_VENDOR2
WHEN J.[CARRIER] = 'VENDOR1' THEN D.INTER_VENDOR1
ELSE D.INTER_TANDEM
END
END AS 'CPM'
INTO MARS_5050.DBO.JANUARY_TEST_RATES2
FROM dbo.JANUARY_TEST J LEFT JOIN TELCO.dbo.DOMESTIC_LD_RATES2 D ON
J.RATEKEY = D.RATEKEY
ORDER BY J.[T_LATA] DESC

John

"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406302042.4d4e86af@.posting.google.c om...
> I was able to make more progress and this is what I have now--
> The critical piece of the query is that it should look at 2 characters
> in the [OFFICE_CLLI] column and then compare it to the 2 character
> state abbreviation in the [TERM_STATE] column to see if it is
> "INTRASTATE" or "INTERSTATE".
> After determining the INTER/INTRA question check the [CARRIER] to see
> who is the vendor. With those two questions answered the query pulls
> the appropriate CPM (COST PER MINUTE) from the table [DOMESTIC LD
> RATES] based on a JOIN from the [RATEKEY] column.
> And with the correct [CPM] pulled it turns around multiplies the value
> from the [MOU] column to give me a COST.
> My Original MS ACCESS Query is--
> SELECT DISTINCTROW
> ([TERM_LATA] & "\" & [TERM_STATE] & "\" & [OCN]) AS RATEKEY,
> JANUARY_TEST.DATE, JANUARY_TEST.CXRKEY, JANUARY_TEST.AREA,
> JANUARY_TEST.REGION,
> JANUARY_TEST.MARKET, JANUARY_TEST.MTA, JANUARY_TEST.LATA,
> JANUARY_TEST.AKA, JANUARY_TEST.MARS_NAME,
> JANUARY_TEST.OFFICE_CLLI, JANUARY_TEST.Carrier, JANUARY_TEST.SWITCH,
> JANUARY_TEST.NPA_NXX, JANUARY_TEST.MOU,
> JANUARY_TEST.[TERM MTA], JANUARY_TEST.[TERM STATE], JANUARY_TEST.[TERM
> LATA], JANUARY_TEST.[TERM RC],
> JANUARY_TEST.RC_ID, JANUARY_TEST.OCN_NAME, JANUARY_TEST.OCN,
> JANUARY_TEST.CATEGORY,
> IIf(Mid([OFFICE_CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD RATES]![INTER_VENDOR1]))))) AS
> CPM,
> [CPM]*[MOU] AS COST INTO JANUARY_TEST_RATES2
> FROM JANUARY_TEST LEFT JOIN [DOMESTIC LD RATES] ON
> JANUARY_TEST.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> ORDER BY JANUARY_TEST.[TERM LATA] DESC;
> MS ACCESS it works well I now need to go the SQL SERVER to handle my
> databases. When I use Query Analyser I get a message invalid syntax
> near ELSE.
> Any additional advice. I have been able to get it this far in
> converting to SQL SERVER 2000--
> SELECT DISTINCT
> dbo.JANUARY_TEST.[T_LATA] + '\' + dbo.JANUARY_TEST.[T_STATE] + '\' +
> dbo.JANUARY_TEST.[OCN] AS RATEKEY,
dbo.JANUARY_TEST.[DATE],dbo.JANUARY_TEST.CXRKEY,dbo.JANUARY_TEST.Area,dbo .JA
NUARY_TEST.Region,
dbo.JANUARY_TEST.Market,dbo.JANUARY_TEST.AKA,dbo.J ANUARY_TEST.MARS_NAME,dbo.
JANUARY_TEST.O_MTA,
dbo.JANUARY_TEST.O_MTA_NAME,dbo.JANUARY_TEST.O_STA TE,dbo.JANUARY_TEST.O_LATA
,dbo.JANUARY_TEST.O_LATA_NAME,
dbo.JANUARY_TEST.OFFICE_CLLI,dbo.JANUARY_TEST.Trun k,dbo.JANUARY_TEST.Carrier
,dbo.JANUARY_TEST.NPA_NXX,
dbo.JANUARY_TEST.CALLS,dbo.JANUARY_TEST.MOU,dbo.JA NUARY_TEST.TANDEM,dbo.JANU
ARY_TEST.T_MTA,dbo.JANUARY_TEST.T_MTA_NAME,
dbo.JANUARY_TEST.T_STATE,dbo.JANUARY_TEST.T_LATA,d bo.JANUARY_TEST.[RC_ABBRE]
,dbo.JANUARY_TEST.RC_ID,SWITCH,
dbo.JANUARY_TEST.[OCN],dbo.JANUARY_TEST.[OCN_NAME],dbo.JANUARY_TEST.[CATEGOR
Y],
> CASE WHEN (SUBSTRING(dbo.JANUARY_TEST.[OFFICE_CLLI], 5, 2) =
> dbo.JANUARY_TEST.[T_STATE]) THEN (
> (CASE WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR4' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR4
> WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR3' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR3
> WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR2' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR2
> WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR1' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_VENDOR1
> ELSE TELCO.dbo.DOMESTIC_LD_RATES2.INTRA_TANDEM
> END)
> ELSE (CASE WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR4' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR4
> WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR3' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR3
> WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR2' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR2
> WHEN dbo.JANUARY_TEST.[CARRIER] = 'VENDOR1' THEN
> TELCO.dbo.DOMESTIC_LD_RATES2.INTER_VENDOR1
> ELSE TELCO.dbo.DOMESTIC_LD_RATES2.INTER_TANDEM
> END)) AS 'CPM'
> INTO MARS_5050.DBO.JANUARY_TEST_RATES2
> FROM dbo.JANUARY_TEST LEFT JOIN TELCO.dbo.DOMESTIC_LD_RATES2 ON
> RATEKEY = TELCO.dbo.DOMESTIC_LD_RATES2.RATEKEY
> ORDER BY dbo.JANUARY_TEST.[T_LATA] DESC
> OPTION (MAXDOP 2)
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<v5yEc.6651$wp6.72768805@.news-text.cableinet.net>...
> > Hi
> > Try something like:
> > SELECT DISTINCT R.*,
> > CASE
> > WHEN SUBSTRING([SWITCH CLLI],5,2)=[TERM STATE] THEN
> > CASE
> > WHEN [CARRIER]="VENDOR4" THEN D.INTRA_VENDOR4
> > WHEN [CARRIER]="VENDOR3" THEN D.INTRA_VENDOR3
> > WHEN [CARRIER]="VENDOR2" THEN D.INTRA_VENDOR2
> > WHEN [CARRIER]="VENDOR1" THEN D.INTRA_VENDOR1
> > END
> > ELSE
> > CASE
> > WHEN [CARRIER]="VENDOR4" THEN D.INTER_VENDOR4
> > WHEN [CARRIER]="VENDOR3" THEN D.INTER_VENDOR3
> > WHEN [CARRIER]="VENDOR2" THEN D.INTER_VENDOR2
> > WHEN [CARRIER]="VENDOR1" THEN D.INTER_VENDOR1
> > END
> > END
> > END AS CPM
> > CPM*MOU AS COST
> > INTO INTRALATA_LD
> > FROM REGION_TRAFIC R LEFT JOIN [DOMESTIC LD RATES] D ON R.RATEKEY =
> > D.RATEKEY
> > WHERE R.[TERM LATA])=R.[LATA]
> > ORDER BY R.[TERM LATA] DESC
> > It would be better to use the table alias for all columns including
> > [CARRIER], [SWITCH CLLI], [TERM STATE] even though they may be unique,
but
> > as you don't post DDL I don't know which table they are in. It is also
not a
> > good idea to use * in production code.
> > John
> > "wiredog" <wiredog@.comcast.net> wrote in message
> > news:b3af3ec6.0406291633.29c8fd64@.posting.google.c om...
> > > I am struggling rewriting my query from MS Access' IIF, Then to SQL
> > > Servers TSQL language. I am hoping some one can give me some
> > > guidance. I believe I have the first portion of the query correct but
> > > do believe this requires a "NESTED" argument. This is where I am
> > > lost.
> > > > My Original MS ACCESS Query reads--
> > > > SELECT DISTINCTROW REGION_TRAFIC.*,
> > > IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> > > IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> > > IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> > > IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> > > IIf([Carrier]="VENDOR1",[DOMESTIC LD
> > > RATES]![INTRA_VENDOR1])))),
> > > > IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> > > IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> > > IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> > > IIf([Carrier]="VENDOR1",[DOMESTIC LD
> > > RATES]![INTER_VENDOR1]))))) AS CPM,
> > > [CPM]*[MOU] AS COST
> > > INTO INTRALATA_LD
> > > FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> > > REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> > > WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> > > ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> > > > I have tried to re-write this in SQL SERVER as --
> > > > SELET DISTINCT REGION TRAFIC.*,
> > > CASE
> > > WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> > > RATES].INTRA_VENDOR4
> > > WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> > > RATES].INTRA_VENDOR3
> > > WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> > > RATES].INTRA_VENDOR2
> > > WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> > > RATES].INTRA_VENDOR1
> > > ELSE
> > > WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> > > RATES].INTER_VENDOR4
> > > WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> > > RATES].INTER_VENDOR3
> > > WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> > > RATES].INTER_VENDOR2
> > > WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> > > RATES].INTER_VENDOR1
> > > END
> > > AS CPM
> > > CPM*MOU AS COST
> > > INTO INTRALATA_LD
> > > FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> > > REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> > > WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> > > ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> > > > My challenge is the Case portion of the query and the nesting! I am
> > > not sure if I have the correct syntax or even chose the correct
> > > argument for my purpose.
> > > > Any guidance is appreciated.|||John,

Thank you for the guidance. I was workingon this over the 4th of July
Holiday and it works great. SQL Server is a challenge but it starting
to make sense.

Did have one additional question.

On when I try to add the line . . .

CPM * MOU AS COST,

after all the CASE lines I get the response in SQL Query Analyser,

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'CPM'.

Is it because I can not do this on this particular query since CPM is
yet to be defined or do I just need to rephrase the request another
way?

The query now looks like this. . .

SELECT DISTINCT
DATA.dbo.[2004_JANUARY_SUM].RATEKEY,
DATA.dbo.[2004_JANUARY_SUM].[DATE],
DATA.dbo.[2004_JANUARY_SUM].CXRKEY,
DATA.dbo.[2004_JANUARY_SUM].Area,
DATA.dbo.[2004_JANUARY_SUM].Region,
DATA.dbo.[2004_JANUARY_SUM].Market,
DATA.dbo.[2004_JANUARY_SUM].AKA,
DATA.dbo.[2004_JANUARY_SUM].MARS_NAME,
DATA.dbo.[2004_JANUARY_SUM].O_MTA,
DATA.dbo.[2004_JANUARY_SUM].O_MTA_NAME,
DATA.dbo.[2004_JANUARY_SUM].O_STATE,
DATA.dbo.[2004_JANUARY_SUM].O_LATA,
DATA.dbo.[2004_JANUARY_SUM].O_LATA_NAME,
DATA.dbo.[2004_JANUARY_SUM].MSC_CLLI,
DATA.dbo.[2004_JANUARY_SUM].Trunk,
DATA.dbo.[2004_JANUARY_SUM].Carrier,
DATA.dbo.[2004_JANUARY_SUM].NPA_NXX,
DATA.dbo.[2004_JANUARY_SUM].CALLS,
DATA.dbo.[2004_JANUARY_SUM].MOU,
DATA.dbo.[2004_JANUARY_SUM].TANDEM,
DATA.dbo.[2004_JANUARY_SUM].T_MTA,
DATA.dbo.[2004_JANUARY_SUM].T_MTA_NAME,
DATA.dbo.[2004_JANUARY_SUM].T_STATE,
DATA.dbo.[2004_JANUARY_SUM].T_LATA,
DATA.dbo.[2004_JANUARY_SUM].[RC ABBRE],
DATA.dbo.[2004_JANUARY_SUM].RC_ID,
DATA.dbo.[2004_JANUARY_SUM].SWITCH,
DATA.dbo.[2004_JANUARY_SUM].[OCN],
DATA.dbo.[2004_JANUARY_SUM].[OCN_NAME],
DATA.dbo.[2004_JANUARY_SUM].[CATEGORY],
CASE WHEN SUBSTRING(DATA.dbo.[2004_JANUARY_SUM].[MSC_CLLI], 5, 2) =
DATA.dbo.[2004_JANUARY_SUM].[T_STATE] THEN
(CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_GX
WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.[INTRA_VENDOR1]
ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_TANDEM
END)
ELSE
(CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_GX
WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOMESTIC_LD_RATES2.[INTER_VENDOR1]
ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_TANDEM
END)
END AS CPM,
CPM * MOU as [COST]
INTO TEST.dbo.[2004_JANUARY_RATES]
FROM DATA.dbo.[2004_JANUARY_SUM] LEFT OUTER JOIN
TELECOM.dbo.DOMESTIC_LD_RATES2 ON
DATA.dbo.[2004_JANUARY_SUM].RATEKEY =
TELECOM.dbo.DOMESTIC_LD_RATES2.RATEKEY
ORDER BY DATA.dbo.[2004_JANUARY_SUM].[T_LATA] DESC
OPTION (MAXDOP 2)|||Hi

It looks like you are learning pretty quickly! I don't think SQL Server is
as "quirky" as access.

CPM is an alias for the nested case statements, SQL Server does not allow
you to re-use an alias within the column list (although it can be used in an
order by clause), you can either repeat the CASE statement (which can be a
pain, especially if you want to use the value again in the where clause), or
you may want to create a view and use that instead of the table (don't
include the order by in the view definition), calculate the value when
selecting this from the table which you are inserting the data into or you
could use a derived table. See Books online for more information regarding
these.

I still don't know why you have MAXDOP in the statement and also make use of
table aliases to tidy up the code.

John

"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0407052030.2cdc05e7@.posting.google.c om...
> John,
> Thank you for the guidance. I was workingon this over the 4th of July
> Holiday and it works great. SQL Server is a challenge but it starting
> to make sense.
> Did have one additional question.
> On when I try to add the line . . .
> CPM * MOU AS COST,
> after all the CASE lines I get the response in SQL Query Analyser,
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'CPM'.
> Is it because I can not do this on this particular query since CPM is
> yet to be defined or do I just need to rephrase the request another
> way?
> The query now looks like this. . .
> SELECT DISTINCT
> DATA.dbo.[2004_JANUARY_SUM].RATEKEY,
> DATA.dbo.[2004_JANUARY_SUM].[DATE],
> DATA.dbo.[2004_JANUARY_SUM].CXRKEY,
> DATA.dbo.[2004_JANUARY_SUM].Area,
> DATA.dbo.[2004_JANUARY_SUM].Region,
> DATA.dbo.[2004_JANUARY_SUM].Market,
> DATA.dbo.[2004_JANUARY_SUM].AKA,
> DATA.dbo.[2004_JANUARY_SUM].MARS_NAME,
> DATA.dbo.[2004_JANUARY_SUM].O_MTA,
> DATA.dbo.[2004_JANUARY_SUM].O_MTA_NAME,
> DATA.dbo.[2004_JANUARY_SUM].O_STATE,
> DATA.dbo.[2004_JANUARY_SUM].O_LATA,
> DATA.dbo.[2004_JANUARY_SUM].O_LATA_NAME,
> DATA.dbo.[2004_JANUARY_SUM].MSC_CLLI,
> DATA.dbo.[2004_JANUARY_SUM].Trunk,
> DATA.dbo.[2004_JANUARY_SUM].Carrier,
> DATA.dbo.[2004_JANUARY_SUM].NPA_NXX,
> DATA.dbo.[2004_JANUARY_SUM].CALLS,
> DATA.dbo.[2004_JANUARY_SUM].MOU,
> DATA.dbo.[2004_JANUARY_SUM].TANDEM,
> DATA.dbo.[2004_JANUARY_SUM].T_MTA,
> DATA.dbo.[2004_JANUARY_SUM].T_MTA_NAME,
> DATA.dbo.[2004_JANUARY_SUM].T_STATE,
> DATA.dbo.[2004_JANUARY_SUM].T_LATA,
> DATA.dbo.[2004_JANUARY_SUM].[RC ABBRE],
> DATA.dbo.[2004_JANUARY_SUM].RC_ID,
> DATA.dbo.[2004_JANUARY_SUM].SWITCH,
> DATA.dbo.[2004_JANUARY_SUM].[OCN],
> DATA.dbo.[2004_JANUARY_SUM].[OCN_NAME],
> DATA.dbo.[2004_JANUARY_SUM].[CATEGORY],
> CASE WHEN SUBSTRING(DATA.dbo.[2004_JANUARY_SUM].[MSC_CLLI], 5, 2) =
> DATA.dbo.[2004_JANUARY_SUM].[T_STATE] THEN
> (CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_GX
> WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR3
> WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR2
> WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.[INTRA_VENDOR1]
> ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_TANDEM
> END)
> ELSE
> (CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_GX
> WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR3
> WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR2
> WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THEN
> TELECOM.DBO.DOMESTIC_LD_RATES2.[INTER_VENDOR1]
> ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_TANDEM
> END)
> END AS CPM,
> CPM * MOU as [COST]
> INTO TEST.dbo.[2004_JANUARY_RATES]
> FROM DATA.dbo.[2004_JANUARY_SUM] LEFT OUTER JOIN
> TELECOM.dbo.DOMESTIC_LD_RATES2 ON
> DATA.dbo.[2004_JANUARY_SUM].RATEKEY =
> TELECOM.dbo.DOMESTIC_LD_RATES2.RATEKEY
> ORDER BY DATA.dbo.[2004_JANUARY_SUM].[T_LATA] DESC
> OPTION (MAXDOP 2)|||wiredog (wiredog@.comcast.net) writes:
> Thank you for the guidance. I was workingon this over the 4th of July
> Holiday and it works great. SQL Server is a challenge but it starting
> to make sense.
> Did have one additional question.
> On when I try to add the line . . .
> CPM * MOU AS COST,
> after all the CASE lines I get the response in SQL Query Analyser,
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'CPM'.
> Is it because I can not do this on this particular query since CPM is
> yet to be defined or do I just need to rephrase the request another
> way?

You can do this in Access, but it is non-standard SQL, and it could
never be implemented in SQL Server because it would conflict with
existing syntax.

On the other hand, there are derived tables, a very powerful feature.
I'll show with a simple example:

SELECT a, b*c as prod
FROM (SELECT a, b = g + i + 2*lf - sin(y), c = u * lf
FROM tbl) AS x

The thing you have in parentheses is a derived table. Logically it
is computed first, but the optimizer is free to rearrange as long as
the result is not affected.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John,

Thank you so much for the advice and patience. It helps to have
someone who can tell me if I am on the right track or if I have missed
the boat altogether. Nothing like being on a timeline and finding out
that one has to work twice as hard JUST to get back to the starting
block.

You can probably tell how panicky I get by my blanket posting on any
group with "mssql" as part of the group name. I am reading up on SQL
as I have only been using SQL for about 5 months MS Access -> MySQL ->
and finally to MS SQL Server 2000.

BTW the reason I sue the MAXDOP is I read somewhere that by limiting
the Degree of Parallelism to <= then number of Processors in the
Server it may actually help the speed of the query. Don't know if it
is true, perhaps it is like spiting on a new bat to break it in.

Cheers and much gratitude!

Ben

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<HmtGc.673$eD1.8467626@.news-text.cableinet.net>...
> Hi
> It looks like you are learning pretty quickly! I don't think SQL Server is
> as "quirky" as access.
> CPM is an alias for the nested case statements, SQL Server does not allow
> you to re-use an alias within the column list (although it can be used in an
> order by clause), you can either repeat the CASE statement (which can be a
> pain, especially if you want to use the value again in the where clause), or
> you may want to create a view and use that instead of the table (don't
> include the order by in the view definition), calculate the value when
> selecting this from the table which you are inserting the data into or you
> could use a derived table. See Books online for more information regarding
> these.
> I still don't know why you have MAXDOP in the statement and also make use of
> table aliases to tidy up the code.
> John
> "wiredog" <wiredog@.comcast.net> wrote in message
> news:b3af3ec6.0407052030.2cdc05e7@.posting.google.c om...
> > John,
> > Thank you for the guidance. I was workingon this over the 4th of July
> > Holiday and it works great. SQL Server is a challenge but it starting
> > to make sense.
> > Did have one additional question.
> > On when I try to add the line . . .
> > CPM * MOU AS COST,
> > after all the CASE lines I get the response in SQL Query Analyser,
> > Server: Msg 207, Level 16, State 3, Line 1
> > Invalid column name 'CPM'.
> > Is it because I can not do this on this particular query since CPM is
> > yet to be defined or do I just need to rephrase the request another
> > way?
> > The query now looks like this. . .
> > SELECT DISTINCT
> > DATA.dbo.[2004_JANUARY_SUM].RATEKEY,
> > DATA.dbo.[2004_JANUARY_SUM].[DATE],
> > DATA.dbo.[2004_JANUARY_SUM].CXRKEY,
> > DATA.dbo.[2004_JANUARY_SUM].Area,
> > DATA.dbo.[2004_JANUARY_SUM].Region,
> > DATA.dbo.[2004_JANUARY_SUM].Market,
> > DATA.dbo.[2004_JANUARY_SUM].AKA,
> > DATA.dbo.[2004_JANUARY_SUM].MARS_NAME,
> > DATA.dbo.[2004_JANUARY_SUM].O_MTA,
> > DATA.dbo.[2004_JANUARY_SUM].O_MTA_NAME,
> > DATA.dbo.[2004_JANUARY_SUM].O_STATE,
> > DATA.dbo.[2004_JANUARY_SUM].O_LATA,
> > DATA.dbo.[2004_JANUARY_SUM].O_LATA_NAME,
> > DATA.dbo.[2004_JANUARY_SUM].MSC_CLLI,
> > DATA.dbo.[2004_JANUARY_SUM].Trunk,
> > DATA.dbo.[2004_JANUARY_SUM].Carrier,
> > DATA.dbo.[2004_JANUARY_SUM].NPA_NXX,
> > DATA.dbo.[2004_JANUARY_SUM].CALLS,
> > DATA.dbo.[2004_JANUARY_SUM].MOU,
> > DATA.dbo.[2004_JANUARY_SUM].TANDEM,
> > DATA.dbo.[2004_JANUARY_SUM].T_MTA,
> > DATA.dbo.[2004_JANUARY_SUM].T_MTA_NAME,
> > DATA.dbo.[2004_JANUARY_SUM].T_STATE,
> > DATA.dbo.[2004_JANUARY_SUM].T_LATA,
> > DATA.dbo.[2004_JANUARY_SUM].[RC ABBRE],
> > DATA.dbo.[2004_JANUARY_SUM].RC_ID,
> > DATA.dbo.[2004_JANUARY_SUM].SWITCH,
> > DATA.dbo.[2004_JANUARY_SUM].[OCN],
> > DATA.dbo.[2004_JANUARY_SUM].[OCN_NAME],
> > DATA.dbo.[2004_JANUARY_SUM].[CATEGORY],
> > CASE WHEN SUBSTRING(DATA.dbo.[2004_JANUARY_SUM].[MSC_CLLI], 5, 2) =
> > DATA.dbo.[2004_JANUARY_SUM].[T_STATE] THEN
> > (CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_GX
> > WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR3
> > WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_VENDOR2
> > WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.[INTRA_VENDOR1]
> > ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTRA_TANDEM
> > END)
> > ELSE
> > (CASE WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR4' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_GX
> > WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR3' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR3
> > WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR2' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_VENDOR2
> > WHEN DATA.dbo.[2004_JANUARY_SUM].[CARRIER] = 'VENDOR1' THEN
> > TELECOM.DBO.DOMESTIC_LD_RATES2.[INTER_VENDOR1]
> > ELSE TELECOM.DBO.DOMESTIC_LD_RATES2.INTER_TANDEM
> > END)
> > END AS CPM,
> > CPM * MOU as [COST]
> > INTO TEST.dbo.[2004_JANUARY_RATES]
> > FROM DATA.dbo.[2004_JANUARY_SUM] LEFT OUTER JOIN
> > TELECOM.dbo.DOMESTIC_LD_RATES2 ON
> > DATA.dbo.[2004_JANUARY_SUM].RATEKEY =
> > TELECOM.dbo.DOMESTIC_LD_RATES2.RATEKEY
> > ORDER BY DATA.dbo.[2004_JANUARY_SUM].[T_LATA] DESC
> > OPTION (MAXDOP 2)|||wiredog (wiredog@.comcast.net) writes:
> BTW the reason I sue the MAXDOP is I read somewhere that by limiting
> the Degree of Parallelism to <= then number of Processors in the
> Server it may actually help the speed of the query. Don't know if it
> is true, perhaps it is like spiting on a new bat to break it in.

Yes, MAXDOP can often help to speed up queries, contradictory as it may
sound. But I have seen several casees where SQL Server has gone for a
parallel plan that has been very complicated and also very slow. On top
of that, since the query takes all processors, this means that all other
processes suffers. If you run a bad plan on one processor on an 8-way
box, there is still plenty of CPU to the rest of the bunch.

Then again, there are certainly queries where parallelism helps you to
get better response time. Personally I prefer to put in MAXDOP until I
actually need it. (I usually review indexes and how the query is written
before I give in.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland

I think missed a not!!

"Personally I prefer NOT to put in MAXDOP until I actually need it"

John
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951F7F4A3F569Yazorman@.127.0.0.1...
> wiredog (wiredog@.comcast.net) writes:
> > BTW the reason I sue the MAXDOP is I read somewhere that by limiting
> > the Degree of Parallelism to <= then number of Processors in the
> > Server it may actually help the speed of the query. Don't know if it
> > is true, perhaps it is like spiting on a new bat to break it in.
> Yes, MAXDOP can often help to speed up queries, contradictory as it may
> sound. But I have seen several casees where SQL Server has gone for a
> parallel plan that has been very complicated and also very slow. On top
> of that, since the query takes all processors, this means that all other
> processes suffers. If you run a bad plan on one processor on an 8-way
> box, there is still plenty of CPU to the rest of the bunch.
> Then again, there are certainly queries where parallelism helps you to
> get better response time. Personally I prefer to put in MAXDOP until I
> actually need it. (I usually review indexes and how the query is written
> before I give in.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Converting MS query to SQL Server 2000

I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping osme one can give me some
guidance. I believe I have the first portion of the query correct but
do belive this requires a "NESTED" argument. This si where I am lost.
My Original MS ACCESS Query reads--
SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTRA_VENDOR1])))),
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTER_VENDOR1]))))) AS CPM,
[CPM]*[MOU] AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
I have tried to re-write this in SQL SERVER as --
SELET DISTINCT REGION TRAFIC.*,
CASE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTRA_VENDOR1
ELSE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTER_VENDOR1
END
AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC
My cahllenge is the Case portion of the query and the nesting! I
amnot sure if I hae the correc syntax or even chose the correct
argument for my purpose.
Any guidance is apprecaited.
DISTINCTROW becomes DISTINCT, MID becomes SUBSTRING, IIF becomes CASE, !
becomes .

> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
Well, are you getting an error message, the wrong information, ...?
http://www.aspfaq.com/
(Reverse address to reply.)
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.c om...
>I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.
|||First issue is the need to bracket REGION TRAFFIC you have a space there and
T-SQL does not like that. If the table name has a "_" then put that in.
In your WHERE clause you need to replace [REGION_TRAFIC]![LATA] with
[REGION_TRAFIC].[LATA] or REGION_TRAFIC.LATA
As far as whether or not you are choosing the proper function you need to
post the DDL and what it is you wish to accomplish.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.c om...
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.
|||wiredog, try this:
SELECT RT.*
, CASE
WHEN SUBSTRING(Switch_CLLI,5,2) = "TERM STATE" THEN
CASE Carrier
WHEN "VENDOR4" THEN DLR.Intra_Vendor4
WHEN "VENDOR3" THEN DLR.Intra_Vendor3
WHEN "VENDOR2" THEN DLR.Intra_Vendor2
WHEN "VENDOR1" THEN DLR.Intra_Vendor1
-- ELSE ??
END
ELSE
CASE Carrier
WHEN "VENDOR4" THEN DLR.Inter_Vendor4
WHEN "VENDOR3" THEN DLR.Inter_Vendor3
WHEN "VENDOR2" THEN DLR.Inter_Vendor2
WHEN "VENDOR1" THEN DLR.Inter_Vendor1
-- ELSE ?
END
END AS CPM
, CPM * MOU AS Cost
INTO INTRALATA_LD
FROM REGION_TRAFIC RT
LEFT JOIN "DOMESTIC LD RATES" DLR
ON RT.RATEKEY = DLR.RATEKEY
WHERE RT."TERM LATA" = RT.LATA
ORDER BY RT."TERM LATA" DESC
Hope this helps,
Gert-Jan
wiredog wrote:
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.
(Please reply only to the newsgroup)

Converting MS query to SQL Server 2000

I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping osme one can give me some
guidance. I believe I have the first portion of the query correct but
do belive this requires a "NESTED" argument. This si where I am lost.
My Original MS ACCESS Query reads--
SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTRA_VENDOR1])))),
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTER_VENDOR1]))))) AS CPM,
[CPM]*[MOU] AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
I have tried to re-write this in SQL SERVER as --
SELET DISTINCT REGION TRAFIC.*,
CASE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTRA_VENDOR1
ELSE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTER_VENDOR1
END
AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC
My cahllenge is the Case portion of the query and the nesting! I
amnot sure if I hae the correc syntax or even chose the correct
argument for my purpose.
Any guidance is apprecaited.DISTINCTROW becomes DISTINCT, MID becomes SUBSTRING, IIF becomes CASE, !
becomes .
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
Well, are you getting an error message, the wrong information, ...?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.com...
>I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.|||First issue is the need to bracket REGION TRAFFIC you have a space there and
T-SQL does not like that. If the table name has a "_" then put that in.
In your WHERE clause you need to replace [REGION_TRAFIC]![LATA] with
[REGION_TRAFIC].[LATA] or REGION_TRAFIC.LATA
As far as whether or not you are choosing the proper function you need to
post the DDL and what it is you wish to accomplish.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.com...
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.|||wiredog, try this:
SELECT RT.*
, CASE
WHEN SUBSTRING(Switch_CLLI,5,2) = "TERM STATE" THEN
CASE Carrier
WHEN "VENDOR4" THEN DLR.Intra_Vendor4
WHEN "VENDOR3" THEN DLR.Intra_Vendor3
WHEN "VENDOR2" THEN DLR.Intra_Vendor2
WHEN "VENDOR1" THEN DLR.Intra_Vendor1
-- ELSE '?
END
ELSE
CASE Carrier
WHEN "VENDOR4" THEN DLR.Inter_Vendor4
WHEN "VENDOR3" THEN DLR.Inter_Vendor3
WHEN "VENDOR2" THEN DLR.Inter_Vendor2
WHEN "VENDOR1" THEN DLR.Inter_Vendor1
-- ELSE '
END
END AS CPM
, CPM * MOU AS Cost
INTO INTRALATA_LD
FROM REGION_TRAFIC RT
LEFT JOIN "DOMESTIC LD RATES" DLR
ON RT.RATEKEY = DLR.RATEKEY
WHERE RT."TERM LATA" = RT.LATA
ORDER BY RT."TERM LATA" DESC
Hope this helps,
Gert-Jan
wiredog wrote:
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.
--
(Please reply only to the newsgroup)

Converting MS query to SQL Server 2000

I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping osme one can give me some
guidance. I believe I have the first portion of the query correct but
do belive this requires a "NESTED" argument. This si where I am lost.
My Original MS ACCESS Query reads--
SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTRA_VENDOR1])))),
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTER_VENDOR1]))))) AS CPM,
[CPM]*[MOU] AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
I have tried to re-write this in SQL SERVER as --
SELET DISTINCT REGION TRAFIC.*,
CASE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTRA_VENDOR1
ELSE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTER_VENDOR1
END
AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC
My cahllenge is the Case portion of the query and the nesting! I
amnot sure if I hae the correc syntax or even chose the correct
argument for my purpose.
Any guidance is apprecaited.DISTINCTROW becomes DISTINCT, MID becomes SUBSTRING, IIF becomes CASE, !
becomes .

> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
Well, are you getting an error message, the wrong information, ...?
http://www.aspfaq.com/
(Reverse address to reply.)
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.com...
>I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VE
NDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VE
NDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VE
NDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VE
NDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VE
NDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VE
NDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.|||First issue is the need to bracket REGION TRAFFIC you have a space there and
T-SQL does not like that. If the table name has a "_" then put that in.
In your WHERE clause you need to replace [REGION_TRAFIC]![LATA] with
[REGION_TRAFIC].[LATA] or REGION_TRAFIC.LATA
As far as whether or not you are choosing the proper function you need to
post the DDL and what it is you wish to accomplish.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.com...
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_V
ENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_V
ENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_V
ENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_V
ENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_V
ENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_V
ENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.|||wiredog, try this:
SELECT RT.*
, CASE
WHEN SUBSTRING(Switch_CLLI,5,2) = "TERM STATE" THEN
CASE Carrier
WHEN "VENDOR4" THEN DLR.Intra_Vendor4
WHEN "VENDOR3" THEN DLR.Intra_Vendor3
WHEN "VENDOR2" THEN DLR.Intra_Vendor2
WHEN "VENDOR1" THEN DLR.Intra_Vendor1
-- ELSE '?
END
ELSE
CASE Carrier
WHEN "VENDOR4" THEN DLR.Inter_Vendor4
WHEN "VENDOR3" THEN DLR.Inter_Vendor3
WHEN "VENDOR2" THEN DLR.Inter_Vendor2
WHEN "VENDOR1" THEN DLR.Inter_Vendor1
-- ELSE '
END
END AS CPM
, CPM * MOU AS Cost
INTO INTRALATA_LD
FROM REGION_TRAFIC RT
LEFT JOIN "DOMESTIC LD RATES" DLR
ON RT.RATEKEY = DLR.RATEKEY
WHERE RT."TERM LATA" = RT.LATA
ORDER BY RT."TERM LATA" DESC
Hope this helps,
Gert-Jan
wiredog wrote:
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_V
ENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_V
ENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_V
ENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_V
ENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_V
ENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_V
ENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.
(Please reply only to the newsgroup)