Converting MS query to SQL Server 2000 -- sql server (alternate)

wiredog NO[at]SPAM comcast.net
6/30/2004 9:42:30 PM

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.JANUARY_TEST.MARS_NAME,dbo.JANUARY_TEST.O_MTA,
dbo.JANUARY_TEST.O_MTA_NAME,dbo.JANUARY_TEST.O_STATE,dbo.JANUARY_TEST.O_LATA,dbo.JANUARY_TEST.O_LATA_NAME,
dbo.JANUARY_TEST.OFFICE_CLLI,dbo.JANUARY_TEST.Trunk,dbo.JANUARY_TEST.Carrier,dbo.JANUARY_TEST.NPA_NXX,
dbo.JANUARY_TEST.CALLS,dbo.JANUARY_TEST.MOU,dbo.JANUARY_TEST.TANDEM,dbo.JANUARY_TEST.T_MTA,dbo.JANUARY_TEST.T_MTA_NAME,
dbo.JANUARY_TEST.T_STATE,dbo.JANUARY_TEST.T_LATA,dbo.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)

[quoted text, click to view]

John Bell
7/1/2004 8:58:52 AM

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

[quoted text, click to view]
dbo.JANUARY_TEST.[DATE],dbo.JANUARY_TEST.CXRKEY,dbo.JANUARY_TEST.Area,dbo.JA
NUARY_TEST.Region,
[quoted text, click to view]
dbo.JANUARY_TEST.Market,dbo.JANUARY_TEST.AKA,dbo.JANUARY_TEST.MARS_NAME,dbo.
JANUARY_TEST.O_MTA,
[quoted text, click to view]
dbo.JANUARY_TEST.O_MTA_NAME,dbo.JANUARY_TEST.O_STATE,dbo.JANUARY_TEST.O_LATA
,dbo.JANUARY_TEST.O_LATA_NAME,
[quoted text, click to view]
dbo.JANUARY_TEST.OFFICE_CLLI,dbo.JANUARY_TEST.Trunk,dbo.JANUARY_TEST.Carrier
,dbo.JANUARY_TEST.NPA_NXX,
[quoted text, click to view]
dbo.JANUARY_TEST.CALLS,dbo.JANUARY_TEST.MOU,dbo.JANUARY_TEST.TANDEM,dbo.JANU
ARY_TEST.T_MTA,dbo.JANUARY_TEST.T_MTA_NAME,
[quoted text, click to view]
dbo.JANUARY_TEST.T_STATE,dbo.JANUARY_TEST.T_LATA,dbo.JANUARY_TEST.[RC_ABBRE]
,dbo.JANUARY_TEST.RC_ID,SWITCH,
[quoted text, click to view]
dbo.JANUARY_TEST.[OCN],dbo.JANUARY_TEST.[OCN_NAME],dbo.JANUARY_TEST.[CATEGOR
Y],
[quoted text, click to view]

wiredog NO[at]SPAM comcast.net
7/5/2004 9:30:57 PM

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

John Bell
7/6/2004 8:26:15 AM

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

[quoted text, click to view]

wiredog NO[at]SPAM comcast.net
7/6/2004 3:58:37 PM

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

[quoted text, click to view]

Erland Sommarskog
7/6/2004 10:02:30 PM

wiredog (wiredog@comcast.net) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Erland Sommarskog
7/7/2004 10:31:45 AM

wiredog (wiredog@comcast.net) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at

John Bell
7/7/2004 10:55:15 AM

Hi Erland

I think missed a not!!

"Personally I prefer NOT to put in MAXDOP until I actually need it"

John
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Converting MS query to SQL Server 2000