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.
9  2269 
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.
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.
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.
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" <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)
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
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)
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
|
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'...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
