Converting MS query to SQL Server 2000

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

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