Converting MS query to SQL Server 2000

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