Convert Access Query to SQL Server SP

I have this query In MS Access and I need to convert it to a SQL Store
Procedure.
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
SELECT dbo_PTSLSITM.SlsId, dbo_COEMP.NameNick, dbo_PTSLSITM.DateCreate,
dbo_PTSLSITM.DateCreate, Left([slsid],4) AS Branch, dbo_PTSLSITM.DateCreate,
"Branch " & Right([branch],3) AS BranchNumber, dbo_PTSLSITM.AmtPrice,
dbo_PTSLSITM.ItmTyp, dbo_PTSLSITM.ItmId, dbo_PTSLSITM.PtItm,
dbo_PTSLSITM.Des, dbo_PTSLSITM.Reason, dbo_PTSLSITM.AmtPriceSuggest,
dbo_PTSLSITM.QtyShip
FROM dbo_COEMP RIGHT JOIN dbo_PTSLSITM ON dbo_COEMP.EmpId =
dbo_PTSLSITM.EmpIdSpn
WHERE (((dbo_PTSLSITM.SlsId) Not Like "E*") AND ((dbo_PTSLSITM.DateCreate)
Between [Start Date] And [End date]) AND
((dbo_PTSLSITM.AmtPrice)<>[AmtPriceSuggest]) AND
((dbo_PTSLSITM.ItmTyp)<>531));
-------------------------------------------------------------------------

I have this much working
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- CIT_PriceChange_RPT
-- CREATE PROCEDURE dbo.CIT_PriceChange_RPT
Alter PROCEDURE dbo.CIT_PriceChange_RPT

as
begin

SELECT 'SlsId' = [PTSLSITM].[SlsId]
, 'NameNick' = [COEMP].[NameNick]
, 'DateCreate' = [PTSLSITM].[DateCreate]
, 'AmtPrice' = [PTSLSITM].[AmtPrice]
, 'ItmTyp' = [PTSLSITM].[ItmTyp]
, 'ItmId' = [PTSLSITM].[ItmId]
, 'PtItm' = [PTSLSITM].[PtItm]
, 'Des' = [PTSLSITM].[Des]
, 'Reason' = [PTSLSITM].[Reason]
, 'AmtPriceSuggest' = [PTSLSITM].[AmtPriceSuggest]
, 'QtyShip' = [PTSLSITM].[QtyShip]

FROM COEMP
RIGHT JOIN [PTSLSITM] ON [COEMP].[EmpId] = [PTSLSITM].[EmpIdSpn]

WHERE ((PTSLSITM.ItmTyp)<> '531')

END
GO
-------------------------------------------------------------------------

I don't know how to convert these Select and Where statements
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
SELECT PTSLSITM.DateCreate, Left([slsid],4) AS Branch
, PTSLSITM.DateCreate, "Branch " & Right([branch],3) AS BranchNumber

WHERE PTSLSITM.SlsId) Not Like "E*")
AND ((PTSLSITM.AmtPrice)<>[AmtPriceSuggest])
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++

Would someone be so kind as to show me how these should look in SQL?

Thanks!!!

Kelvin

Use single quotes around strings, not double. Use + to concatenate
strings, not &. Use % for the wildcard in LIKE expressions, not *. I
assumed that RIGHT and LEFT return substrings from their respective
ends, which is the same as SQL Server. I also find all the
superfluous parentheses annoying. The version below is based on the
one at the top of your message.

SELECT dbo_PTSLSITM.SlsId,
dbo_COEMP.NameNick,
dbo_PTSLSITM.DateCreate,
dbo_PTSLSITM.DateCreate,
Left([slsid],4) AS Branch,
dbo_PTSLSITM.DateCreate,
'Branch ' + Right([branch],3) AS BranchNumber,
dbo_PTSLSITM.AmtPrice,
dbo_PTSLSITM.ItmTyp,
dbo_PTSLSITM.ItmId,
dbo_PTSLSITM.PtItm,
dbo_PTSLSITM.Des,
dbo_PTSLSITM.Reason,
dbo_PTSLSITM.AmtPriceSuggest,
dbo_PTSLSITM.QtyShip
FROM dbo_COEMP
RIGHT OUTER
JOIN dbo_PTSLSITM
ON dbo_COEMP.EmpId = dbo_PTSLSITM.EmpIdSpn
WHERE dbo_PTSLSITM.SlsId Not Like 'E%'
AND dbo_PTSLSITM.DateCreate Between [Start Date] And [End date]
AND dbo_PTSLSITM.AmtPrice <> [AmtPriceSuggest]
AND dbo_PTSLSITM.ItmTyp <> 531;

Roy Harvey
Beacon Falls, CT

On Wed, 27 Aug 2008 14:23:30 -0500, "Kelvin"
wrote:

>
>I have this query In MS Access and I need to convert it to a SQL Store
>Procedure.
>++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>SELECT dbo_PTSLSITM.SlsId, dbo_COEMP.NameNick, dbo_PTSLSITM.DateCreate,
>dbo_PTSLSITM.DateCreate, Left([slsid],4) AS Branch, dbo_PTSLSITM.DateCreate,
>"Branch " & Right([branch],3) AS BranchNumber, dbo_PTSLSITM.AmtPrice,
>dbo_PTSLSITM.ItmTyp, dbo_PTSLSITM.ItmId, dbo_PTSLSITM.PtItm,
>dbo_PTSLSITM.Des, dbo_PTSLSITM.Reason, dbo_PTSLSITM.AmtPriceSuggest,
>dbo_PTSLSITM.QtyShip
>FROM dbo_COEMP RIGHT JOIN dbo_PTSLSITM ON dbo_COEMP.EmpId =
>dbo_PTSLSITM.EmpIdSpn
>WHERE (((dbo_PTSLSITM.SlsId) Not Like "E*") AND ((dbo_PTSLSITM.DateCreate)
>Between [Start Date] And [End date]) AND
>((dbo_PTSLSITM.AmtPrice)<>[AmtPriceSuggest]) AND
>((dbo_PTSLSITM.ItmTyp)<>531));
>-------------------------------------------------------------------------
>
>I have this much working
>++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>
>-- CIT_PriceChange_RPT
>-- CREATE PROCEDURE dbo.CIT_PriceChange_RPT
>Alter PROCEDURE dbo.CIT_PriceChange_RPT
>
>as
>begin
>
> SELECT 'SlsId' = [PTSLSITM].[SlsId]
> , 'NameNick' = [COEMP].[NameNick]
> , 'DateCreate' = [PTSLSITM].[DateCreate]
> , 'AmtPrice' = [PTSLSITM].[AmtPrice]
> , 'ItmTyp' = [PTSLSITM].[ItmTyp]
> , 'ItmId' = [PTSLSITM].[ItmId]
> , 'PtItm' = [PTSLSITM].[PtItm]
> , 'Des' = [PTSLSITM].[Des]
> , 'Reason' = [PTSLSITM].[Reason]
> , 'AmtPriceSuggest' = [PTSLSITM].[AmtPriceSuggest]
> , 'QtyShip' = [PTSLSITM].[QtyShip]
>
> FROM COEMP
> RIGHT JOIN [PTSLSITM] ON [COEMP].[EmpId] = [PTSLSITM].[EmpIdSpn]
>
> WHERE ((PTSLSITM.ItmTyp)<> '531')
>
>END
>GO
>-------------------------------------------------------------------------
>
>I don't know how to convert these Select and Where statements
>++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>SELECT PTSLSITM.DateCreate, Left([slsid],4) AS Branch
> , PTSLSITM.DateCreate, "Branch " & Right([branch],3) AS BranchNumber
>
>WHERE PTSLSITM.SlsId) Not Like "E*")
> AND ((PTSLSITM.AmtPrice)<>[AmtPriceSuggest])
>++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>
>Would someone be so kind as to show me how these should look in SQL?
>
>Thanks!!!
>
>Kelvin
>

A couple more: use aliases for readability, and ALWAYS prefix column names
when using multiple sources. The optimizer might know what table
AmtPriceSuggest comes from, but I don't! And why is DateCreate returned
three times? Branch looks like a column derived on line 5, so the reference
on line 7 is probably not valid. I tend to use alias = expression instead
of expression as alias for this reason. Also there is probably a better way
to handle the BETWEEN, unless all datetime values are stored as dates with
the time set to midnight. I am guessing on some of the table prefixes.
Object prefixes, too, should be used. I'll assumed based on the awkward and
horribly awkward and completely un-typeable object names that they are in
the dbo schema.


SELECT p.SlsId,
c.NameNick,
p.DateCreate,
p.DateCreate,
Branch = LEFT(p.SlsId,4),
BranchNumber = 'Branch ' + SUBSTRING(p.SlsId, 2, 3),
p.AmtPrice,
p.ItmTyp,
p.ItmId,
p.PtItm,
p.Des,
p.Reason,
p.AmtPriceSuggest,
p.QtyShip
FROM dbo.dbo_COEMP AS c
RIGHT OUTER
JOIN dbo.dbo_PTSLSITM AS p
ON c.EmpId = p.EmpIdSpn
WHERE p.SlsId NOT LIKE 'E%'
AND p.DateCreate BETWEEN c.[Start Date] AND c.[End date]
AND p.AmtPrice <> p.[AmtPriceSuggest]
AND p.ItmTyp <> 531;


On 8/27/08 8:31 PM, in article 75sbb4hvs7dpq622ejeb45g0r3hp7m3u1f-at-4ax.com,
"Roy Harvey (SQL Server MVP)" wrote:

> Use single quotes around strings, not double. Use + to concatenate
> strings, not &. Use % for the wildcard in LIKE expressions, not *. I
> assumed that RIGHT and LEFT return substrings from their respective
> ends, which is the same as SQL Server. I also find all the
> superfluous parentheses annoying. The version below is based on the
> one at the top of your message.
>
> SELECT dbo_PTSLSITM.SlsId,
> dbo_COEMP.NameNick,
> dbo_PTSLSITM.DateCreate,
> dbo_PTSLSITM.DateCreate,
> Left([slsid],4) AS Branch,
> dbo_PTSLSITM.DateCreate,
> 'Branch ' + Right([branch],3) AS BranchNumber,
> dbo_PTSLSITM.AmtPrice,
> dbo_PTSLSITM.ItmTyp,
> dbo_PTSLSITM.ItmId,
> dbo_PTSLSITM.PtItm,
> dbo_PTSLSITM.Des,
> dbo_PTSLSITM.Reason,
> dbo_PTSLSITM.AmtPriceSuggest,
> dbo_PTSLSITM.QtyShip
> FROM dbo_COEMP
> RIGHT OUTER
> JOIN dbo_PTSLSITM
> ON dbo_COEMP.EmpId = dbo_PTSLSITM.EmpIdSpn
> WHERE dbo_PTSLSITM.SlsId Not Like 'E%'
> AND dbo_PTSLSITM.DateCreate Between [Start Date] And [End date]
> AND dbo_PTSLSITM.AmtPrice <> [AmtPriceSuggest]
> AND dbo_PTSLSITM.ItmTyp <> 531;
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 27 Aug 2008 14:23:30 -0500, "Kelvin"
> wrote:
>
>>
>> I have this query In MS Access and I need to convert it to a SQL Store
>> Procedure.
>> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>> SELECT dbo_PTSLSITM.SlsId, dbo_COEMP.NameNick, dbo_PTSLSITM.DateCreate,
>> dbo_PTSLSITM.DateCreate, Left([slsid],4) AS Branch, dbo_PTSLSITM.DateCreate,
>> "Branch " & Right([branch],3) AS BranchNumber, dbo_PTSLSITM.AmtPrice,
>> dbo_PTSLSITM.ItmTyp, dbo_PTSLSITM.ItmId, dbo_PTSLSITM.PtItm,
>> dbo_PTSLSITM.Des, dbo_PTSLSITM.Reason, dbo_PTSLSITM.AmtPriceSuggest,
>> dbo_PTSLSITM.QtyShip
>> FROM dbo_COEMP RIGHT JOIN dbo_PTSLSITM ON dbo_COEMP.EmpId =
>> dbo_PTSLSITM.EmpIdSpn
>> WHERE (((dbo_PTSLSITM.SlsId) Not Like "E*") AND ((dbo_PTSLSITM.DateCreate)
>> Between [Start Date] And [End date]) AND
>> ((dbo_PTSLSITM.AmtPrice)<>[AmtPriceSuggest]) AND
>> ((dbo_PTSLSITM.ItmTyp)<>531));
>> -------------------------------------------------------------------------
>>
>> I have this much working
>> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>>
>> -- CIT_PriceChange_RPT
>> -- CREATE PROCEDURE dbo.CIT_PriceChange_RPT
>> Alter PROCEDURE dbo.CIT_PriceChange_RPT
>>
>> as
>> begin
>>
>> SELECT 'SlsId' = [PTSLSITM].[SlsId]
>> , 'NameNick' = [COEMP].[NameNick]
>> , 'DateCreate' = [PTSLSITM].[DateCreate]
>> , 'AmtPrice' = [PTSLSITM].[AmtPrice]
>> , 'ItmTyp' = [PTSLSITM].[ItmTyp]
>> , 'ItmId' = [PTSLSITM].[ItmId]
>> , 'PtItm' = [PTSLSITM].[PtItm]
>> , 'Des' = [PTSLSITM].[Des]
>> , 'Reason' = [PTSLSITM].[Reason]
>> , 'AmtPriceSuggest' = [PTSLSITM].[AmtPriceSuggest]
>> , 'QtyShip' = [PTSLSITM].[QtyShip]
>>
>> FROM COEMP
>> RIGHT JOIN [PTSLSITM] ON [COEMP].[EmpId] = [PTSLSITM].[EmpIdSpn]
>>
>> WHERE ((PTSLSITM.ItmTyp)<> '531')
>>
>> END
>> GO
>> -------------------------------------------------------------------------
>>
>> I don't know how to convert these Select and Where statements
>> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>> SELECT PTSLSITM.DateCreate, Left([slsid],4) AS Branch
>> , PTSLSITM.DateCreate, "Branch " & Right([branch],3) AS BranchNumber
>>
>> WHERE PTSLSITM.SlsId) Not Like "E*")
>> AND ((PTSLSITM.AmtPrice)<>[AmtPriceSuggest])
>> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
>>
>> Would someone be so kind as to show me how these should look in SQL?
>>
>> Thanks!!!
>>
>> Kelvin
>>

On Wed, 27 Aug 2008 21:12:54 -0400, "Aaron Bertrand [SQL Server MVP]"
wrote:

Many good point, Aaron.

>Branch looks like a column derived on line 5, so the reference
>on line 7 is probably not valid.

The reference on line 7 is to the string 'Branch ', not to a column.

>I tend to use alias = expression instead
>of expression as alias for this reason.

I don't understand the reasoning here.

> Also there is probably a better way
>to handle the BETWEEN, unless all datetime values are stored as dates with
>the time set to midnight.

I believe that Access did not have to wait 16+ years to get a DATE
datatype, so I expect that the data has zeroes for time, but the point
absolutely needed to be made.

>SELECT p.SlsId,
> c.NameNick,
> p.DateCreate,
> p.DateCreate,
> Branch = LEFT(p.SlsId,4),
> BranchNumber = 'Branch ' + SUBSTRING(p.SlsId, 2, 3),
> p.AmtPrice,
> p.ItmTyp,
>.....

Roy Harvey
Beacon Falls, CT

> The reference on line 7 is to the string 'Branch ', not to a column.

What column does Right([branch],3) reference in line 7? (Look past the
'Branch ' string, and then look back at line 5.)

5 > Left([slsid],4) AS Branch,
6 > dbo_PTSLSITM.DateCreate,
7 > 'Branch ' + Right([branch],3) AS BranchNumber,

My guess is that in Access you can do that, like say

SELECT a = b + c, d = a + 5 -- , etc.

Where a references the alias you had just defined. This won't work in
T-SQL, obviously. I'm assuming that the query worked that way in Access and
that branch is not a real column name.

>> I tend to use alias = expression instead
>> of expression as alias for this reason.
>
> I don't understand the reasoning here.

I can scan down the left and quickly see all of my column names. Putting
them at the end makes it harder to parse for AS. I didn't say that's the
way you have to do it, just voicing my preference.

On Wed, 27 Aug 2008 22:01:58 -0400, "Aaron Bertrand [SQL Server MVP]"
wrote:

>> The reference on line 7 is to the string 'Branch ', not to a column.
>
>What column does Right([branch],3) reference in line 7? (Look past the
>'Branch ' string, and then look back at line 5.)

Sorry, must have been blind.

Roy

Thanks for all the great input

What I was trying to get is the "101" part out of this string,
"X101000020:01"
The 101 is the branch number

That's what these lines were about.
> 5 > Left([slsid],4) AS Branch,
> 7 > 'Branch ' + Right([branch],3) AS BranchNumber,

I agree, not a good way to get it.
Can you help me out on getting that part of the string?

Thanks for the sugestion on the layout of the code... it is much easier to
read...

Thanks

Kelvin


"Aaron Bertrand [SQL Server MVP]" wrote in message
news:C4DB7F56.1071E%ten.xoc-at-dnartreb.noraa...
>
>> The reference on line 7 is to the string 'Branch ', not to a column.
>
> What column does Right([branch],3) reference in line 7? (Look past the
> 'Branch ' string, and then look back at line 5.)
>
> 5 > Left([slsid],4) AS Branch,
> 6 > dbo_PTSLSITM.DateCreate,
> 7 > 'Branch ' + Right([branch],3) AS BranchNumber,
>
> My guess is that in Access you can do that, like say
>
> SELECT a = b + c, d = a + 5 -- , etc.
>
> Where a references the alias you had just defined. This won't work in
> T-SQL, obviously. I'm assuming that the query worked that way in Access
> and
> that branch is not a real column name.
>
>>> I tend to use alias = expression instead
>>> of expression as alias for this reason.
>>
>> I don't understand the reasoning here.
>
> I can scan down the left and quickly see all of my column names. Putting
> them at the end makes it harder to parse for AS. I didn't say that's the
> way you have to do it, just voicing my preference.
>

This line
'Branch ' + Right([branch],3) AS BranchNumber,

seems to be giving this error...
Msg 207, Level 16, State 1, Procedure CIT_PriceChange_RPT, Line 20
Invalid column name 'branch'.

Is this because I need to declare a variable?

Thanks

Kelvin


"Aaron Bertrand [SQL Server MVP]" wrote in message
news:C4DB7F56.1071E%ten.xoc-at-dnartreb.noraa...
>
>> The reference on line 7 is to the string 'Branch ', not to a column.
>
> What column does Right([branch],3) reference in line 7? (Look past the
> 'Branch ' string, and then look back at line 5.)
>
> 5 > Left([slsid],4) AS Branch,
> 6 > dbo_PTSLSITM.DateCreate,
> 7 > 'Branch ' + Right([branch],3) AS BranchNumber,
>
> My guess is that in Access you can do that, like say
>
> SELECT a = b + c, d = a + 5 -- , etc.
>
> Where a references the alias you had just defined. This won't work in
> T-SQL, obviously. I'm assuming that the query worked that way in Access
> and
> that branch is not a real column name.
>
>>> I tend to use alias = expression instead
>>> of expression as alias for this reason.
>>
>> I don't understand the reasoning here.
>
> I can scan down the left and quickly see all of my column names. Putting
> them at the end makes it harder to parse for AS. I didn't say that's the
> way you have to do it, just voicing my preference.
>