473,746 Members | 2,667 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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
9 2289
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*****@comcas t.net> wrote in message
news:b3******** *************** ***@posting.goo gle.com...
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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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
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.DA TE, JANUARY_TEST.CX RKEY, JANUARY_TEST.AR EA,
JANUARY_TEST.RE GION,
JANUARY_TEST.MA RKET, JANUARY_TEST.MT A, JANUARY_TEST.LA TA,
JANUARY_TEST.AK A, JANUARY_TEST.MA RS_NAME,
JANUARY_TEST.OF FICE_CLLI, JANUARY_TEST.Ca rrier, JANUARY_TEST.SW ITCH,
JANUARY_TEST.NP A_NXX, JANUARY_TEST.MO U,
JANUARY_TEST.[TERM MTA], JANUARY_TEST.[TERM STATE], JANUARY_TEST.[TERM
LATA], JANUARY_TEST.[TERM RC],
JANUARY_TEST.RC _ID, JANUARY_TEST.OC N_NAME, JANUARY_TEST.OC N,
JANUARY_TEST.CA TEGORY,

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_RA TES2
FROM JANUARY_TEST LEFT JOIN [DOMESTIC LD RATES] ON
JANUARY_TEST.RA TEKEY = [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_TES T.[T_LATA] + '\' + dbo.JANUARY_TES T.[T_STATE] + '\' +
dbo.JANUARY_TES T.[OCN] AS RATEKEY,
dbo.JANUARY_TES T.[DATE],dbo.JANUARY_TE ST.CXRKEY,dbo.J ANUARY_TEST.Are a,dbo.JANUARY_T EST.Region,
dbo.JANUARY_TES T.Market,dbo.JA NUARY_TEST.AKA, dbo.JANUARY_TES T.MARS_NAME,dbo .JANUARY_TEST.O _MTA,
dbo.JANUARY_TES T.O_MTA_NAME,db o.JANUARY_TEST. O_STATE,dbo.JAN UARY_TEST.O_LAT A,dbo.JANUARY_T EST.O_LATA_NAME ,
dbo.JANUARY_TES T.OFFICE_CLLI,d bo.JANUARY_TEST .Trunk,dbo.JANU ARY_TEST.Carrie r,dbo.JANUARY_T EST.NPA_NXX,
dbo.JANUARY_TES T.CALLS,dbo.JAN UARY_TEST.MOU,d bo.JANUARY_TEST .TANDEM,dbo.JAN UARY_TEST.T_MTA ,dbo.JANUARY_TE ST.T_MTA_NAME,
dbo.JANUARY_TES T.T_STATE,dbo.J ANUARY_TEST.T_L ATA,dbo.JANUARY _TEST.[RC_ABBRE],dbo.JANUARY_TE ST.RC_ID,SWITCH ,
dbo.JANUARY_TES T.[OCN],dbo.JANUARY_TE ST.[OCN_NAME],dbo.JANUARY_TE ST.[CATEGORY],

CASE WHEN (SUBSTRING(dbo. JANUARY_TEST.[OFFICE_CLLI], 5, 2) =
dbo.JANUARY_TES T.[T_STATE]) THEN (
(CASE WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR4' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR4
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR3' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR3
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR2' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR2
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR1' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR1
ELSE TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_TANDEM
END)
ELSE (CASE WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR4' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR4
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR3' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR3
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR2' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR2
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR1' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR1
ELSE TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_TANDEM
END)) AS 'CPM'
INTO MARS_5050.DBO.J ANUARY_TEST_RAT ES2
FROM dbo.JANUARY_TES T LEFT JOIN TELCO.dbo.DOMES TIC_LD_RATES2 ON
RATEKEY = TELCO.dbo.DOMES TIC_LD_RATES2.R ATEKEY
ORDER BY dbo.JANUARY_TES T.[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*****@comcas t.net> wrote in message
news:b3******** *************** ***@posting.goo gle.com...
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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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
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_TES T.[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.J ANUARY_TEST_RAT ES2
FROM dbo.JANUARY_TES T J LEFT JOIN TELCO.dbo.DOMES TIC_LD_RATES2 D ON
J.RATEKEY = D.RATEKEY
ORDER BY J.[T_LATA] DESC

John

"wiredog" <wi*****@comcas t.net> wrote in message
news:b3******** *************** ***@posting.goo gle.com...
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.DA TE, JANUARY_TEST.CX RKEY, JANUARY_TEST.AR EA,
JANUARY_TEST.RE GION,
JANUARY_TEST.MA RKET, JANUARY_TEST.MT A, JANUARY_TEST.LA TA,
JANUARY_TEST.AK A, JANUARY_TEST.MA RS_NAME,
JANUARY_TEST.OF FICE_CLLI, JANUARY_TEST.Ca rrier, JANUARY_TEST.SW ITCH,
JANUARY_TEST.NP A_NXX, JANUARY_TEST.MO U,
JANUARY_TEST.[TERM MTA], JANUARY_TEST.[TERM STATE], JANUARY_TEST.[TERM
LATA], JANUARY_TEST.[TERM RC],
JANUARY_TEST.RC _ID, JANUARY_TEST.OC N_NAME, JANUARY_TEST.OC N,
JANUARY_TEST.CA TEGORY,

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_RA TES2
FROM JANUARY_TEST LEFT JOIN [DOMESTIC LD RATES] ON
JANUARY_TEST.RA TEKEY = [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_TES T.[T_LATA] + '\' + dbo.JANUARY_TES T.[T_STATE] + '\' +
dbo.JANUARY_TES T.[OCN] AS RATEKEY,
dbo.JANUARY_TES T.[DATE],dbo.JANUARY_TE ST.CXRKEY,dbo.J ANUARY_TEST.Are a,dbo.JA
NUARY_TEST.Regi on, dbo.JANUARY_TES T.Market,dbo.JA NUARY_TEST.AKA, dbo.JANUARY_TES T.MARS_NAME,dbo .
JANUARY_TEST.O_ MTA, dbo.JANUARY_TES T.O_MTA_NAME,db o.JANUARY_TEST. O_STATE,dbo.JAN UARY_TEST.O_LAT A
,dbo.JANUARY_TE ST.O_LATA_NAME, dbo.JANUARY_TES T.OFFICE_CLLI,d bo.JANUARY_TEST .Trunk,dbo.JANU ARY_TEST.Carrie r
,dbo.JANUARY_TE ST.NPA_NXX, dbo.JANUARY_TES T.CALLS,dbo.JAN UARY_TEST.MOU,d bo.JANUARY_TEST .TANDEM,dbo.JAN U
ARY_TEST.T_MTA, dbo.JANUARY_TES T.T_MTA_NAME, dbo.JANUARY_TES T.T_STATE,dbo.J ANUARY_TEST.T_L ATA,dbo.JANUARY _TEST.[RC_ABBRE]
,dbo.JANUARY_TE ST.RC_ID,SWITCH , dbo.JANUARY_TES T.[OCN],dbo.JANUARY_TE ST.[OCN_NAME],dbo.JANUARY_TE ST.[CATEGOR
Y],
CASE WHEN (SUBSTRING(dbo. JANUARY_TEST.[OFFICE_CLLI], 5, 2) =
dbo.JANUARY_TES T.[T_STATE]) THEN (
(CASE WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR4' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR4
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR3' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR3
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR2' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR2
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR1' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_VENDOR1
ELSE TELCO.dbo.DOMES TIC_LD_RATES2.I NTRA_TANDEM
END)
ELSE (CASE WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR4' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR4
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR3' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR3
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR2' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR2
WHEN dbo.JANUARY_TES T.[CARRIER] = 'VENDOR1' THEN
TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_VENDOR1
ELSE TELCO.dbo.DOMES TIC_LD_RATES2.I NTER_TANDEM
END)) AS 'CPM'
INTO MARS_5050.DBO.J ANUARY_TEST_RAT ES2
FROM dbo.JANUARY_TES T LEFT JOIN TELCO.dbo.DOMES TIC_LD_RATES2 ON
RATEKEY = TELCO.dbo.DOMES TIC_LD_RATES2.R ATEKEY
ORDER BY dbo.JANUARY_TES T.[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*****@comcas t.net> wrote in message
news:b3******** *************** ***@posting.goo gle.com...
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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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.R ATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFI C.[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
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_SU M].RATEKEY,
DATA.dbo.[2004_JANUARY_SU M].[DATE],
DATA.dbo.[2004_JANUARY_SU M].CXRKEY,
DATA.dbo.[2004_JANUARY_SU M].Area,
DATA.dbo.[2004_JANUARY_SU M].Region,
DATA.dbo.[2004_JANUARY_SU M].Market,
DATA.dbo.[2004_JANUARY_SU M].AKA,
DATA.dbo.[2004_JANUARY_SU M].MARS_NAME,
DATA.dbo.[2004_JANUARY_SU M].O_MTA,
DATA.dbo.[2004_JANUARY_SU M].O_MTA_NAME,
DATA.dbo.[2004_JANUARY_SU M].O_STATE,
DATA.dbo.[2004_JANUARY_SU M].O_LATA,
DATA.dbo.[2004_JANUARY_SU M].O_LATA_NAME,
DATA.dbo.[2004_JANUARY_SU M].MSC_CLLI,
DATA.dbo.[2004_JANUARY_SU M].Trunk,
DATA.dbo.[2004_JANUARY_SU M].Carrier,
DATA.dbo.[2004_JANUARY_SU M].NPA_NXX,
DATA.dbo.[2004_JANUARY_SU M].CALLS,
DATA.dbo.[2004_JANUARY_SU M].MOU,
DATA.dbo.[2004_JANUARY_SU M].TANDEM,
DATA.dbo.[2004_JANUARY_SU M].T_MTA,
DATA.dbo.[2004_JANUARY_SU M].T_MTA_NAME,
DATA.dbo.[2004_JANUARY_SU M].T_STATE,
DATA.dbo.[2004_JANUARY_SU M].T_LATA,
DATA.dbo.[2004_JANUARY_SU M].[RC ABBRE],
DATA.dbo.[2004_JANUARY_SU M].RC_ID,
DATA.dbo.[2004_JANUARY_SU M].SWITCH,
DATA.dbo.[2004_JANUARY_SU M].[OCN],
DATA.dbo.[2004_JANUARY_SU M].[OCN_NAME],
DATA.dbo.[2004_JANUARY_SU M].[CATEGORY],
CASE WHEN SUBSTRING(DATA. dbo.[2004_JANUARY_SU M].[MSC_CLLI], 5, 2) =
DATA.dbo.[2004_JANUARY_SU M].[T_STATE] THEN
(CASE WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_GX
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .[INTRA_VENDOR1]
ELSE TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_TANDEM
END)
ELSE
(CASE WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_GX
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .[INTER_VENDOR1]
ELSE TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_TANDEM
END)
END AS CPM,
CPM * MOU as [COST]
INTO TEST.dbo.[2004_JANUARY_RA TES]
FROM DATA.dbo.[2004_JANUARY_SU M] LEFT OUTER JOIN
TELECOM.dbo.DOM ESTIC_LD_RATES2 ON
DATA.dbo.[2004_JANUARY_SU M].RATEKEY =
TELECOM.dbo.DOM ESTIC_LD_RATES2 .RATEKEY
ORDER BY DATA.dbo.[2004_JANUARY_SU M].[T_LATA] DESC
OPTION (MAXDOP 2)
Jul 20 '05 #5
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*****@comcas t.net> wrote in message
news:b3******** *************** ***@posting.goo gle.com...
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_SU M].RATEKEY,
DATA.dbo.[2004_JANUARY_SU M].[DATE],
DATA.dbo.[2004_JANUARY_SU M].CXRKEY,
DATA.dbo.[2004_JANUARY_SU M].Area,
DATA.dbo.[2004_JANUARY_SU M].Region,
DATA.dbo.[2004_JANUARY_SU M].Market,
DATA.dbo.[2004_JANUARY_SU M].AKA,
DATA.dbo.[2004_JANUARY_SU M].MARS_NAME,
DATA.dbo.[2004_JANUARY_SU M].O_MTA,
DATA.dbo.[2004_JANUARY_SU M].O_MTA_NAME,
DATA.dbo.[2004_JANUARY_SU M].O_STATE,
DATA.dbo.[2004_JANUARY_SU M].O_LATA,
DATA.dbo.[2004_JANUARY_SU M].O_LATA_NAME,
DATA.dbo.[2004_JANUARY_SU M].MSC_CLLI,
DATA.dbo.[2004_JANUARY_SU M].Trunk,
DATA.dbo.[2004_JANUARY_SU M].Carrier,
DATA.dbo.[2004_JANUARY_SU M].NPA_NXX,
DATA.dbo.[2004_JANUARY_SU M].CALLS,
DATA.dbo.[2004_JANUARY_SU M].MOU,
DATA.dbo.[2004_JANUARY_SU M].TANDEM,
DATA.dbo.[2004_JANUARY_SU M].T_MTA,
DATA.dbo.[2004_JANUARY_SU M].T_MTA_NAME,
DATA.dbo.[2004_JANUARY_SU M].T_STATE,
DATA.dbo.[2004_JANUARY_SU M].T_LATA,
DATA.dbo.[2004_JANUARY_SU M].[RC ABBRE],
DATA.dbo.[2004_JANUARY_SU M].RC_ID,
DATA.dbo.[2004_JANUARY_SU M].SWITCH,
DATA.dbo.[2004_JANUARY_SU M].[OCN],
DATA.dbo.[2004_JANUARY_SU M].[OCN_NAME],
DATA.dbo.[2004_JANUARY_SU M].[CATEGORY],
CASE WHEN SUBSTRING(DATA. dbo.[2004_JANUARY_SU M].[MSC_CLLI], 5, 2) =
DATA.dbo.[2004_JANUARY_SU M].[T_STATE] THEN
(CASE WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_GX
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .[INTRA_VENDOR1]
ELSE TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_TANDEM
END)
ELSE
(CASE WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_GX
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .[INTER_VENDOR1]
ELSE TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_TANDEM
END)
END AS CPM,
CPM * MOU as [COST]
INTO TEST.dbo.[2004_JANUARY_RA TES]
FROM DATA.dbo.[2004_JANUARY_SU M] LEFT OUTER JOIN
TELECOM.dbo.DOM ESTIC_LD_RATES2 ON
DATA.dbo.[2004_JANUARY_SU M].RATEKEY =
TELECOM.dbo.DOM ESTIC_LD_RATES2 .RATEKEY
ORDER BY DATA.dbo.[2004_JANUARY_SU M].[T_LATA] DESC
OPTION (MAXDOP 2)

Jul 20 '05 #6
wiredog (wi*****@comcas t.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
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******* ************@ne ws-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*****@comcas t.net> wrote in message
news:b3******** *************** ***@posting.goo gle.com...
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_SU M].RATEKEY,
DATA.dbo.[2004_JANUARY_SU M].[DATE],
DATA.dbo.[2004_JANUARY_SU M].CXRKEY,
DATA.dbo.[2004_JANUARY_SU M].Area,
DATA.dbo.[2004_JANUARY_SU M].Region,
DATA.dbo.[2004_JANUARY_SU M].Market,
DATA.dbo.[2004_JANUARY_SU M].AKA,
DATA.dbo.[2004_JANUARY_SU M].MARS_NAME,
DATA.dbo.[2004_JANUARY_SU M].O_MTA,
DATA.dbo.[2004_JANUARY_SU M].O_MTA_NAME,
DATA.dbo.[2004_JANUARY_SU M].O_STATE,
DATA.dbo.[2004_JANUARY_SU M].O_LATA,
DATA.dbo.[2004_JANUARY_SU M].O_LATA_NAME,
DATA.dbo.[2004_JANUARY_SU M].MSC_CLLI,
DATA.dbo.[2004_JANUARY_SU M].Trunk,
DATA.dbo.[2004_JANUARY_SU M].Carrier,
DATA.dbo.[2004_JANUARY_SU M].NPA_NXX,
DATA.dbo.[2004_JANUARY_SU M].CALLS,
DATA.dbo.[2004_JANUARY_SU M].MOU,
DATA.dbo.[2004_JANUARY_SU M].TANDEM,
DATA.dbo.[2004_JANUARY_SU M].T_MTA,
DATA.dbo.[2004_JANUARY_SU M].T_MTA_NAME,
DATA.dbo.[2004_JANUARY_SU M].T_STATE,
DATA.dbo.[2004_JANUARY_SU M].T_LATA,
DATA.dbo.[2004_JANUARY_SU M].[RC ABBRE],
DATA.dbo.[2004_JANUARY_SU M].RC_ID,
DATA.dbo.[2004_JANUARY_SU M].SWITCH,
DATA.dbo.[2004_JANUARY_SU M].[OCN],
DATA.dbo.[2004_JANUARY_SU M].[OCN_NAME],
DATA.dbo.[2004_JANUARY_SU M].[CATEGORY],
CASE WHEN SUBSTRING(DATA. dbo.[2004_JANUARY_SU M].[MSC_CLLI], 5, 2) =
DATA.dbo.[2004_JANUARY_SU M].[T_STATE] THEN
(CASE WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_GX
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .[INTRA_VENDOR1]
ELSE TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTRA_TANDEM
END)
ELSE
(CASE WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR4' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_GX
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR3' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_VENDOR3
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR2' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_VENDOR2
WHEN DATA.dbo.[2004_JANUARY_SU M].[CARRIER] = 'VENDOR1' THEN
TELECOM.DBO.DOM ESTIC_LD_RATES2 .[INTER_VENDOR1]
ELSE TELECOM.DBO.DOM ESTIC_LD_RATES2 .INTER_TANDEM
END)
END AS CPM,
CPM * MOU as [COST]
INTO TEST.dbo.[2004_JANUARY_RA TES]
FROM DATA.dbo.[2004_JANUARY_SU M] LEFT OUTER JOIN
TELECOM.dbo.DOM ESTIC_LD_RATES2 ON
DATA.dbo.[2004_JANUARY_SU M].RATEKEY =
TELECOM.dbo.DOM ESTIC_LD_RATES2 .RATEKEY
ORDER BY DATA.dbo.[2004_JANUARY_SU M].[T_LATA] DESC
OPTION (MAXDOP 2)

Jul 20 '05 #8
wiredog (wi*****@comcas t.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****@sommarsk og.se

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

I think missed a not!!

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

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

Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
13705
by: dare197 | last post by:
I have a SP that returns the information I want but it returns it in 2 separate queries. Example: Query 1 Name, Number, Class Row 1- Mike Phillips, 154AA, AA and Query 2 Time, Manual
1
6437
by: js | last post by:
I have tables with columns that stores datetime data in int format on SQL server 2000. For example, the datetime for '4/5/2004 00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is 1081180799. I need to generate reports that display datetime columns in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is my query statment. select iorg_name as org, ref_num as , c_first_name as , c_last_name as , sym as...
4
2022
by: dschl | last post by:
Hi, I'm converting an Access 2000 database to Sql Server and must be missing something obvious. Using the Import utility in Sql Server, the Access queries seem to get executed and the resultant data imported as tables. Oops! Using the Upsize lizard in Access 2003, the queries aren't even in the selection list of "tables" to upsize. It looks like the Upsize wizard
1
5337
by: Stefan V. | last post by:
Hello! I am trying to convert a query written for SQL Server 2000 database tables, to a MS Access query. Here is what I have in SQL Server: SELECT t2.*, CASE WHEN t2.QType = '3' THEN t1.Note ELSE CASE WHEN t2.QType = '2' THEN CASE WHEN CONVERT(varchar(100), t1.ANumber) = '1' THEN 'Yes' ELSE 'No' END ELSE CASE WHEN CONVERT(varchar(5), t2.Qnumber)+'.' +
0
1763
by: Timppa | last post by:
Hi, I'm converting ACCESS 2000 database to SQL Server. I have .adp project. In the .mdb I have form where I'll insert rows into two different tables using docmd.GoToRecod ,,acNewRec. In .adp form's Record source property I have following Stored Procedure (in .mdb there were similar query).
2
2133
by: Mark Flippin | last post by:
I'm converting the backend of an Access 2000 database to SQL Server 2000. The existing database has user and group security through a specific workgroup file. Under the "user and group accounts" there is a button to print the users and group relationships. As there are only about 50 authorized users and 6 groups, this has worked.
5
2525
by: Robert | last post by:
I have a series of web applications (configured as separate applications) on a server. There is a main application at the root and then several virtual directories that are independant applications. I am testing an upgrade of all of the sites and have converted the main root site...although not necessarily fixed any issues. I move on instead and converted one of the virtual roots that is a seperate
2
3637
by: ILCSP | last post by:
Hello, I have the following query in Access 2000 that I need to convert to SQL 2000: UPDATE tblShoes, tblBoxes SET tblShoes.Laces1 = Null WHERE (((tblShoes.ShoesID)=Int(.)) AND ((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B")) WITH OWNERACCESS OPTION;
2
11181
by: bipinskulkarni | last post by:
Hi, i have a field createdby with datatype GUID. In following query ,i encountered with the error "Conversion failed when converting from a character string to uniqueidentifier" select ISNULL(VW_tbl_ChildProducts.createdBy,' ' ) as createdBy from table.
0
8800
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9500
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9285
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9218
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8227
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6772
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6060
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3292
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2765
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.