By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,120 Members | 1,799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,120 IT Pros & Developers. It's quick & easy.

Converting MS query to SQL Server 2000

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <wi*****@comcast.net> wrote in message
news:b3**************************@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.

Jul 20 '05 #2

P: n/a
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" <jb************@hotmail.com> wrote in message news:<v5*********************@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" <wi*****@comcast.net> wrote in message
news:b3**************************@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.

Jul 20 '05 #3

P: n/a
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" <wi*****@comcast.net> wrote in message
news:b3**************************@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" <jb************@hotmail.com> wrote in message

news:<v5*********************@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" <wi*****@comcast.net> wrote in message
news:b3**************************@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.

Jul 20 '05 #4

P: n/a
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)
Jul 20 '05 #5

P: n/a
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" <wi*****@comcast.net> wrote in message
news:b3**************************@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)

Jul 20 '05 #6

P: n/a
wiredog (wi*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7

P: n/a
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" <jb************@hotmail.com> wrote in message news:<Hm*******************@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" <wi*****@comcast.net> wrote in message
news:b3**************************@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)

Jul 20 '05 #8

P: n/a
wiredog (wi*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9

P: n/a
Hi Erland

I think missed a not!!

"Personally I prefer NOT to put in MAXDOP until I actually need it"
John
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
wiredog (wi*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.