April 5, 2010 at 5:08 pm
I am new to the sql server.
I have below query which runs in SQL server 7
I need help to convert it to run in SQL server 2005, I don't want to change the compatibility level.
FROMtblVoucherDetails IDETAIL,
tblVoucherGroup PACKAGE,
tblChargeType CHARGE,
tblUnit UNIT,
#tmpTicketInfo INFO
WHEREINFO.RegionID = PACKAGE.RegionID AND
INFO.TicketNumber = PACKAGE.VoucherNumber AND
INFO.RegionID = IDETAIL.RegionID AND
INFO.TicketNumber = IDETAIL.VoucherNumber AND
IDETAIL.RegionID = PACKAGE.RegionID AND
IDETAIL.VoucherNumber = PACKAGE.VoucherNumber AND
IDETAIL.PackageID = PACKAGE.GroupID AND
IDETAIL.ChargeTypeID *= CHARGE.ChargeTypeID AND
CHARGE.CompanyID = @CompanyID AND
IDETAIL.UnitID = UNIT.UnitID AND
UNIT.CompanyID = @CompanyID AND
IDETAIL.BidTypeID = 2
Thanks,
April 5, 2010 at 5:20 pm
Start by posting the table definitions for the tables involved with Keys on those tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 5:37 pm
Here are the details
[tblChargeType](
[ChargeTypeID] [tinyint] NOT NULL,
[ChargeType] [varchar](60) NULL,
[ShowInvoice] [tinyint] NULL,
[ShowPO] [tinyint] NULL,
[ShowService] [tinyint] NULL,
[ShowRevenue] [tinyint] NULL,
[CompanyID] [int] NOT NULL CONSTRAINT [DF_tblChargeType_CompanyID] DEFAULT (1),
[StatusID] [tinyint] NOT NULL CONSTRAINT [DF_tblChargeType_StatusID] DEFAULT (1),
[ChangedWho] [varchar](50) NULL,
[ChangedWhen] [smalldatetime] NULL,
CONSTRAINT [PK_tblChargeType_1__10] PRIMARY KEY CLUSTERED
(
[ChargeTypeID] ASC,
[CompanyID] ASC
)
[tblVoucherDetails](
[RegionID] [tinyint] NOT NULL,
[BidNumber] [varchar](30) NOT NULL,
[SubJobNumber] [varchar](25) NOT NULL,
[VoucherNumber] [varchar](25) NOT NULL,
[ItemID] [smallint] NOT NULL,
[DetailID] [tinyint] NOT NULL,
[BidTypeID] [tinyint] NOT NULL,
[PackageID] [smallint] NOT NULL,
[ProductLineName] [varchar](10) NULL,
[ResourceTypeID] [tinyint] NULL,
[ResourceCategory] [varchar](255) NULL,
[ResourceNumber] [varchar](255) NULL,
[IntExt] [tinyint] NULL,
[ResourceStatusID] [tinyint] NULL,
[ShippingTicketNumber] [varchar](25) NULL,
[OwnerID] [tinyint] NULL,
[GroupID] [smallint] NULL,
[PartNumber] [varchar](25) NULL,
[SerialNumber] [varchar](100) NULL,
[ProductCode] [char](10) NULL,
[Taxable] [tinyint] NULL,
[ChargeTypeID] [tinyint] NULL,
[StartDate] [smalldatetime] NULL,
[EndDate] [smalldatetime] NULL,
[Quantity] [int] NULL,
[UnitQuantity] [decimal](9, 2) NOT NULL CONSTRAINT [DF_tblVoucher_Uni12__10] DEFAULT (0),
[UnitID] [int] NOT NULL CONSTRAINT [DF_tblVoucher_Uni10__10] DEFAULT (0),
[UnitPrice] [money] NOT NULL CONSTRAINT [DF_tblVoucher_Uni11__10] DEFAULT (0),
[DiscountPercentage] [decimal](5, 2) NOT NULL,
[SplitPercentage] [decimal](9, 6) NULL,
[Amount] [money] NOT NULL,
[CreditAdmin] [tinyint] NULL,
[CreditChargeTypeID] [tinyint] NOT NULL,
[CreditReason] [varchar](100) NULL,
[OrderBy] [smallint] NOT NULL,
[ProductLineTotal] [money] NULL,
[TaxPercentage] [decimal](5, 3) NULL,
[VendorID] [varchar](10) NULL,
[TaxTypeID] [tinyint] NULL CONSTRAINT [DF_tblVoucherDetails_TaxTypeID] DEFAULT (0),
[ReqNumber] [varchar](25) NULL,
[ReqItemID] [smallint] NULL,
[ItemNumber] [varchar](5) NULL,
[VendNameID] [int] NULL CONSTRAINT [DF_tblVoucherDetails_VendNameID] DEFAULT (0),
[VendAddrID] [int] NULL CONSTRAINT [DF_tblVoucherDetails_VendAddrID] DEFAULT (0),
[ProductID] [int] NULL,
[PriceTypeID] [int] NULL,
[CompanyProductCodeID] [int] NULL,
[ProductGroupID] [int] NULL,
[CompanyID] [int] NULL,
[DistrictID] [int] NULL,
[SAPItemID] [varchar](50) NULL,
[SAPItemName] [varchar](40) NULL,
[AliasNumber] [varchar](40) NULL,
[RigTicketNumber] [varchar](25) NULL,
CONSTRAINT [PK_tblVoucherDetails] PRIMARY KEY NONCLUSTERED
(
[RegionID] ASC,
[VoucherNumber] ASC,
[ItemID] ASC,
[DetailID] ASC
)
[tblVoucherGroup](
[RegionID] [int] NOT NULL,
[BidNumber] [varchar](20) NOT NULL,
[SubJobNumber] [varchar](25) NOT NULL,
[VoucherNumber] [varchar](25) NOT NULL,
[GroupID] [smallint] NOT NULL,
[BidTypeID] [tinyint] NOT NULL,
[GroupDescription] [varchar](255) NOT NULL,
[GroupHeader] [varchar](255) NOT NULL,
[GroupComment] [varchar](255) NOT NULL,
[StartDate] [smalldatetime] NOT NULL,
[EndDate] [smalldatetime] NOT NULL,
[Quantity] [smallint] NOT NULL,
[UnitQuantity] [decimal](9, 2) NOT NULL,
[UnitID] [tinyint] NOT NULL,
[UnitPrice] [money] NOT NULL,
[DiscountPercentage] [decimal](5, 2) NOT NULL,
[Amount] [money] NOT NULL,
[Taxable] [tinyint] NULL,
[GroupOrderBy] [smallint] NOT NULL,
[BidGroupID] [smallint] NOT NULL,
[SAPGroupID] [varchar](50) NULL,
[PageBreak] [tinyint] NULL,
[OrderByGroupID] [smallint] NULL,
[IncludeLLAmount] [tinyint] NULL,
[SAPGroupName] [varchar](40) NULL,
[RigTicketNumber] [varchar](25) NULL,
CONSTRAINT [PK_tblVoucherGroup] PRIMARY KEY NONCLUSTERED
(
[RegionID] ASC,
[VoucherNumber] ASC,
[GroupID] ASC
)
[tblUnit](
[CompanyID] [int] NOT NULL,
[UnitID] [tinyint] NOT NULL,
[UnitDescription] [varchar](30) NOT NULL,
[UnitCode] [varchar](4) NULL,
[ShowPackage] [tinyint] NULL,
[ShowForecast] [tinyint] NULL,
[StatusID] [tinyint] NOT NULL CONSTRAINT [DF_tblUnit_StatusID] DEFAULT (1),
[ChangedWho] [varchar](50) NULL,
[ChangedWhen] [smalldatetime] NULL,
CONSTRAINT [PK_tblUnit] PRIMARY KEY NONCLUSTERED
(
[CompanyID] ASC,
[UnitID] ASC
)
[#tmpTicketInfo](
RegionId,
LocationId,
TicketNumber,
customer,
salesperson,
startdate,
enddate)
April 5, 2010 at 5:47 pm
This should be enough to get you going.
FROMtblVoucherDetails IDETAIL
Inner Join tblVoucherGroup PACKAGE
On Package.RegionID = IDetail.RegionID
AND IDETAIL.VoucherNumber = PACKAGE.VoucherNumber
AND IDETAIL.PackageID = PACKAGE.GroupID
/*Inner Join tblChargeType CHARGE
On Charge.ChargeTypeID *= Idetail.ChargeTypeID */
Inner Join tblUnit UNIT
On IDETAIL.UnitID = UNIT.UnitID
Inner Join #tmpTicketInfo INFO
On INFO.RegionID = PACKAGE.RegionID
AND INFO.TicketNumber = PACKAGE.VoucherNumber
AND INFO.RegionID = IDETAIL.RegionID
AND INFO.TicketNumber = IDETAIL.VoucherNumber
WHERE CHARGE.CompanyID = @CompanyID
AND UNIT.CompanyID = @CompanyID
ANDIDETAIL.BidTypeID = 2
Just make changes as appropriate to the commented section.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 5:54 pm
Thanks,
I couldn't get it, This query is not using tblChargeType table
April 5, 2010 at 6:00 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 6:08 pm
Jason,
I couldn't get your comment "Just make changes as appropriate to the commented section. "
It would be great If you can rewrite the whole query. I don' know the SQL well.
April 5, 2010 at 6:12 pm
ritu.thatola (4/5/2010)
Jason,I couldn't get your comment "Just make changes as appropriate to the commented section. "
It would be great If you can rewrite the whole query. I don' know the SQL well.
Since you don't use that section of code, I would not worry about it.
As for rewriting the entire query, I have rewritten what you have provided. I couldn't rewrite anything further because you did not provide the entire query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 6:30 pm
main problem is with line IDETAIL.ChargeTypeID *= CHARGE.ChargeTypeID. in query where clause.
I have to rewrite the query for SQL server 2005
when I try to run this sql in SQL server 2005 it give me an error.
" The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes."
April 5, 2010 at 6:57 pm
ritu.thatola (4/5/2010)
I am new to the sql server.I have below query which runs in SQL server 7
I need help to convert it to run in SQL server 2005, I don't want to change the compatibility level.
FROMtblVoucherDetails IDETAIL,
tblVoucherGroup PACKAGE,
tblChargeType CHARGE,
tblUnit UNIT,
#tmpTicketInfo INFO
WHEREINFO.RegionID = PACKAGE.RegionID AND
INFO.TicketNumber = PACKAGE.VoucherNumber AND
INFO.RegionID = IDETAIL.RegionID AND
INFO.TicketNumber = IDETAIL.VoucherNumber AND
IDETAIL.RegionID = PACKAGE.RegionID AND
IDETAIL.VoucherNumber = PACKAGE.VoucherNumber AND
IDETAIL.PackageID = PACKAGE.GroupID AND
IDETAIL.ChargeTypeID *= CHARGE.ChargeTypeID AND
CHARGE.CompanyID = @CompanyID AND
IDETAIL.UnitID = UNIT.UnitID AND
UNIT.CompanyID = @CompanyID AND
IDETAIL.BidTypeID = 2
Thanks,
Heh... this is precisely why I keep a copy of SQL Server 2000 on line... it rewrote the code for me and all I had to do was format it.
FROM #tmpTicketInfo INFO
INNER JOIN tblVoucherGroup PACKAGE
ON INFO.RegionId = PACKAGE.RegionID
AND INFO.TicketNumber = PACKAGE.VoucherNumber
INNER JOIN tblVoucherDetails IDETAIL
ON INFO.RegionId = IDETAIL.RegionID
AND INFO.TicketNumber = IDETAIL.VoucherNumber
AND PACKAGE.RegionID = IDETAIL.RegionID
AND PACKAGE.VoucherNumber = IDETAIL.VoucherNumber
AND PACKAGE.GroupID = IDETAIL.PackageID
INNER JOIN tblUnit UNIT
ON IDETAIL.UnitID = UNIT.UnitID
LEFT JOIN tblChargeType CHARGE
ON IDETAIL.ChargeTypeID = CHARGE.ChargeTypeID --This is where the *= was
WHERE CHARGE.CompanyID = @CompanyID
AND UNIT.CompanyID = @CompanyID
AND IDETAIL.BidTypeID = 2
As a side bar, the code contains what I believe to be an unnecessary "skip" join which explains the wierdness of one of the ON clauses. I don't believe that a reference needs to be established between the INFO and IDETAIL aliased tables and they should probably be removed. However, I wanted to give you the exact replacement for your code.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2010 at 7:05 pm
ritu.thatola (4/5/2010)
Jason,I don' know the SQL well.
Hmmmm... then why did they select you to resolve this particular problem? You need to read the writing on the wall... learn SQL well enough so you don't have to post "I don' know the SQL well" in the future. It'll help you keep your job when layoff's happen.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2010 at 7:18 pm
ritu.thatola (4/5/2010)
main problem is with line IDETAIL.ChargeTypeID *= CHARGE.ChargeTypeID. in query where clause.I have to rewrite the query for SQL server 2005
when I try to run this sql in SQL server 2005 it give me an error.
" The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes."
This is the section of code that I commented out. You stated that the Charge table is not used. Thus leave it commented out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 7:48 pm
Jeff Moden (4/5/2010)
ritu.thatola (4/5/2010)
Jason,I don' know the SQL well.
Hmmmm... then why did they select you to resolve this particular problem? You need to read the writing on the wall... learn SQL well enough so you don't have to post "I don' know the SQL well" in the future. It'll help you keep your job when layoff's happen.
Just to reinforce what Jeff said above, we just lost on FTE in our DBA group, guess who got cut? And a hint, not me. The individual who left was extremely weak in several areas regarding SQL Server. This is why he was selected for the RIF. I hope this underlines the writing on the wall, so that you start learning more about SQL before it is too late.
April 5, 2010 at 7:55 pm
Lynn Pettis (4/5/2010)
Jeff Moden (4/5/2010)
ritu.thatola (4/5/2010)
Jason,I don' know the SQL well.
Hmmmm... then why did they select you to resolve this particular problem? You need to read the writing on the wall... learn SQL well enough so you don't have to post "I don' know the SQL well" in the future. It'll help you keep your job when layoff's happen.
Just to reinforce what Jeff said above, we just lost on FTE in our DBA group, guess who got cut? And a hint, not me. The individual who left was extremely weak in several areas regarding SQL Server. This is why he was selected for the RIF. I hope this underlines the writing on the wall, so that you start learning more about SQL before it is too late.
Let's make that 3 to emphasize that point.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2010 at 8:03 am
Sorry to say this, instead of helping me you guys are writing......
I am not a Database person.... Just trying to solve some problem and I hope that this forum would help me ....
Anyway Thanks a lot to all of you who helped me.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply