|
Hello Friends,
I wrote a query for a purpose like this;I have a field by name "CustomerName".I want it to be split as FirstName and LastName as shown below:
CustomerName FirstName LastName
Jack Daniels Jack Daniels
Jack,Daniels Jack Daniels
My query looks like this;
select substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ],
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName]
from TableName
My Problem is i have two conditions the splitter could be "," or "" in the customername field
I need to integrate this conditions to my query either using "case" or "iif".
ie if the charindex is " " do this and if the charindex is ", " do this
I am not sure how i can do this.Any help is welcome
|
|
|
|
|
Use CASE[^] and test if CHARINDEX for comma or space is greater than 0 and based on that use SUBSTRING to split the string. Something like:
... CASE
WHEN CHARINDEX(',', customername) > 0 THEN SUBSTRING(customername,1, CHARINDEX(',', customername) -1)
ELSE SUBSTRING(customername,1,CHARINDEX(' ', customername) -1)
END AS FirstName ....
|
|
|
|
|
Hi Mika,
I modified my query like this;It still is not working 
select customername,
case when CHARINDEX(',', customername)>0 then
substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ],
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName]
else
substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ],
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName]
from TableName
|
|
|
|
|
Trustapple wrote: It still is not working
Getting and error message?
I think it should be more like (may contain several typos):
select customername,
case
when CHARINDEX(',', customername)>0 then
substring(customername,1,CHARINDEX(',', customername) -1)
else
substring(customername,1,CHARINDEX(' ', customername) -1)
end as [firstname ],
case
when CHARINDEX(',', customername)>0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername))
else
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername))
end AS [LastName]
from TableName
|
|
|
|
|
Hey Mika,
Thanks for you trying to help.My server i unavailable now.I will be able to access it only after 5hours.As soon as i try thi s query i will post a reply and let you know.
Many Thanks for your help....
Merry Christmas
|
|
|
|
|
|
Hey Mika,
Thanks again to you and Ben....It worked.I made slight modification in the query though...a very slight one.
The credit goes to you guys ....
Please see the working query and let me know if any tweaking is necessary...becuase i will be cghnaging this select to update query for around 9million records.....
select customername,
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end as [lastname],
-- get the first name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end AS [firstname]
from TableName
|
|
|
|
|
Hey Mika,
The time now is 7:59 AM for you,i beleive....Happy sleeping .
Thanks again to you and Ben....It worked.I made slight modification in the query though...a very slight one.
The credit goes to you guys ....
Please see the working query and let me know if any tweaking is necessary...becuase i will be cghnaging this select to update query for around 9million records.....
select customername,
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end as [lastname],
-- get the first name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end AS [firstname]
from TableName
|
|
|
|
|
I think it should be:
select customername,
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end as [lastname],
-- get the first name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end AS [firstname]
from TableName
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Yeah, you're rught. I misplaced the commas. Corrected now.
Ben Fair wrote: SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
Although it's 'cleaner' to use the rest of the length as a parameter, it's not necessary. Basically if you want the rest of the string you can pass any value as long as it's at least the rest of the length.
|
|
|
|
|
Oh, I wasn't aware of that; I'll have to remember that! Thanks!
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hey ,
Thanks a lot to you and Mika it worked.
You guys have been wonderful.
Hey i ran into another problem....
I might need to integrate one more condition;ie along with our query i want it to also do this that is if the customername is Mika (ie the name is only one word and it doesnot contain any ',' or ' ')Then it should update to FirstName.....Am pasting the working query below can any of you integrate this to my query.....
Name FirstName LastName
Mika Mika
Ben Ben
QUERY:update TableName
set lastname=
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end,
-- get the first name
firstname = case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end
modified on Thursday, December 11, 2008 2:23 AM
|
|
|
|
|
Trustapple wrote: Thanks
You're welcome.
Trustapple wrote: along with our query i want it to also do this that is if the customername is Mika (ie the name is only one word and it doesnot contain any ',' or ' ')Then it should update to FirstName.....
Do you mean something like this:
update TableName
set lastname
= case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
TableName.LastName --<code> no delimitter so don't change lastname in the table</code>
end,
firstname
= case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
customername -- <code>no delimiter so use the full data to firstname</code>
end ...
|
|
|
|
|
Hey Mika,
I got it working.Thanks for your help......again .
Lots of Bestwishes,
Jiju
|
|
|
|
|
Trustapple wrote: Thanks for your help
You're welcome.
|
|
|
|
|
Hallo,
I am working in an company which is using extensivly right outer joins. I argue that outer joins are bad for performance, and we have a lot of performance problems. We are using db2 from IBM.
To show you an example (a query which returns page size error in system temporary tablespace)
But nobody seems to care. My colleges add more and more right outer joins to querries.
Here is the example:
Please give me some feetback if this is an ordinary query (my colleges say a database must be able to handle this) or if I am right. I think this query is overkill.
SELECT RentObj.Classific1 AS Classific1, RentObj.Classific2 AS Classific2, RentObj.Classific3 AS Classific3, RentObj.Classific4 AS Classific4, RentObj.Classific5 AS Classific5, RentObj.Classific6 AS Classific6, RentObj.Classific7 AS Classific7, RentObj.Classific8 AS Classific8, RentObj.Classific9 AS Classific9, RentObj.Classific10 AS Classific10, RentObj.RentObjId AS RentObjId, Car.RegistrationNumber AS RegistrationNumber, RentObj.Number AS Number, Classification_1.Code AS CCode_1, Classification_2.Code AS CCode_2, Classification_3.Code AS CCode_3, Classification_4.Code AS CCode_4, Classification_5.Code AS CCode_5, Classification_6.Code AS CCode_6, Classification_7.Code AS CCode_7, Classification_8.Code AS CCode_8, Classification_9.Code AS CCode_9, Classification_10.Code AS CCode_10, Car.RentObjId AS Car_RentObjId, Car.FirstLicense AS FirstLicense, Car.ChassisNo AS ChassisNo, Car.CarDocumentNo AS CarDocumentNo, Car.CarKeyNo AS CarKeyNo, Car.CubicCapacity AS CubicCapacity, Car.Kilowatt AS Kilowatt, Car.PayLoad AS PayLoad, Car.RadioCode AS RadioCode, Car.MayorRevison AS MayorRevison, Car.TankContent AS TankContent, Car.Consumption AS Consumption, Car.Kilometer AS Kilometer, Car.UnitNumber AS UnitNumber, Car.KilometerMax AS KilometerMax, Car.KMHigCos AS KMHigCos, Car.KMCI AS KMCI, Car.FuelType AS FuelType, Car.PS AS PS, Car.BrakeSpecInvest AS BrakeSpecInvest, Car.Totalweight AS Totalweight, Car.Unloadedweight AS Unloadedweight, Car.MatchRegNumber AS MatchRegNumber, Car.ConsumptionCityIn AS ConsumptionCityIn, Car.ConsumptionCityOut AS ConsumptionCityOut, Car.CO2Emission AS CO2Emission, RentObj.CompanyId AS CompanyId, RentObj.RentObjTypeId AS RentObjTypeId, RentObj.LicenceeId AS LicenceeId, RentObj.ObjEvent AS ObjEvent, RentObj.StationCIId AS StationCIId, RentObj.ObjPositionCIId AS ObjPositionCIId, RentObj.CostCarrierId AS CostCarrierId, RentObj.CheckInStatPlanId AS CheckInStatPlanId, RentObj.CheckInDatePlan AS CheckInDatePlan, RentObj.State AS State, RentObj.Notice AS Notice, RentObj.SumActCostYear AS SumActCostYear, RentObj.SumEstCostYear AS SumEstCostYear, RentObj.SumActCostMonth AS SumActCostMonth, RentObj.SumActCostDay AS SumActCostDay, RentObj.SumActCostHour AS SumActCostHour, RentObj.SumEstCostsMonth AS SumEstCostsMonth, RentObj.SumActCostWeek AS SumActCostWeek, RentObj.SumEstCostWeek AS SumEstCostWeek, RentObj.CostActDay AS CostActDay, RentObj.CostEstDay AS CostEstDay, RentObj.OperatingLife AS OperatingLife, RentObj.Startavailability AS Startavailability, RentObj.ClassificDescr AS ClassificDescr, RentObj.DateEstSale AS DateEstSale, RentObj.DateActSale AS DateActSale, RentObj.SalesType AS SalesType, RentObj.WebVisible AS WebVisible, RentObj.LedgerId AS LedgerId, RentObj.LedgerAsset AS LedgerAsset, RentObj.TaxId AS TaxId, RentObj.isAlienRentObj AS isAlienRentObj, RentObj.CountService AS CountService, RentObj.CreateDataSrc AS CreateDataSrc, RentObj.RentContractNote AS RentContractNote, RentObj.LedgerProcCode AS LedgerProcCode, RentObj.MinDriverLicence AS MinDriverLicence, RentObj.MinDriverAge AS MinDriverAge, RentObj.PriceSellWWW AS PriceSellWWW, RentObj.ListPriceNet AS ListPriceNet, RentObj.ListPriceGross AS ListPriceGross, RentObj.ListPriceType AS ListPriceType, RentObj.PurchasePrice AS PurchasePrice, RentObj.ResidalPrice AS ResidalPrice, RentObj.PurchaseDiscount AS PurchaseDiscount, RentObj.AmortizationType AS AmortizationType, RentObj.AmortizationRecord AS AmortizationRecord, RentObj.BankPercent AS BankPercent, RentObj.LastBankAmount AS LastBankAmount, RentObj.AmortizationAct AS AmortizationAct, RentObj.CustKeyBuyer AS CustKeyBuyer, RentObj.LeasingStart AS LeasingStart, RentObj.LeasingEnd AS LeasingEnd, RentObj.CustKeyLeaser AS CustKeyLeaser, RentObj.LeasingDuration AS LeasingDuration, RentObj.RentDurationType AS RentDurationType, RentObj.CustKeySup AS CustKeySup, RentObj.LeasBackBuy AS LeasBackBuy, RentObj.LeasBackBuyDate AS LeasBackBuyDate, RentObj.LeasRate AS LeasRate, RentObj.SupOrderNo AS SupOrderNo, RentObj.UsageState AS UsageState, RentObj.DamageCount AS DamageCount, RentObj.DamageCosts AS DamageCosts, RentObj.TransferCosts AS TransferCosts, RentObj.CountWKZPrint AS CountWKZPrint, RentObj.WKZType AS WKZType, RentObj.WKZDemarc AS WKZDemarc, RentObj.WKZBase AS WKZBase, RentObj.WKZAmount AS WKZAmount, RentObj.WKZDueDate AS WKZDueDate, RentObj.WKZDuration AS WKZDuration, RentObj.WKZPersonAccNo AS WKZPersonAccNo, RentObj.WKZPercent AS WKZPercent, RentObj.WKZTaxId AS WKZTaxId, RentObj.ActivOrgaKey AS ActivOrgaKey, RentObj.RentContractId AS RentContractId, RentObj.RentObjTransactId AS RentObjTransactId, RentObj.WWWSellDescr AS WWWSellDescr, RentObj.PurchasePrCalcPer AS PurchasePrCalcPer, RentObj.PurchasePrCalcMonth AS PurchasePrCalcMonth, RentObj.PurchasePrCalcBase AS PurchasePrCalcBase, RentObj.CalcCode1 AS CalcCode1, RentObj.CalcCode2 AS CalcCode2, RentObj.CalcCode3 AS CalcCode3, RentObj.CalcCode4 AS CalcCode4, RentObj.CalcCode5 AS CalcCode5, RentObj.CalcCode6 AS CalcCode6, RentObj.CalcCode7 AS CalcCode7, RentObj.CalcCode8 AS CalcCode8, RentObj.CalcCode9 AS CalcCode9, RentObj.CalcCode10 AS CalcCode10, RentObj.Classific11 AS Classific11, RentObj.Classific12 AS Classific12, RentObj.Classific13 AS Classific13, RentObj.Classific14 AS Classific14, RentObj.Classific15 AS Classific15, RentObj.ClassificDescr2 AS ClassificDescr2, RentObj.ClassificDescr3 AS ClassificDescr3, RentObj.ClassificDescr4 AS ClassificDescr4, RentObj.ClassificDescr5 AS ClassificDescr5, RentObj.ContentRentObjId AS ContentRentObjId, RentObj.PropertyVariId AS PropertyVariId, RentObj.LedgerIdDeprec AS LedgerIdDeprec, RentObj.LedgerIdAsset AS LedgerIdAsset, RentObj.SupBillNo AS SupBillNo, RentObj.BillingDate AS BillingDate, RentObj.InsurClassific1 AS InsurClassific1, RentObj.InsurClassific2 AS InsurClassific2, RentObj.LeasContractNo AS LeasContractNo, RentObj.CheckOutDate AS CheckOutDate, RentObj.CheckInDate AS CheckInDate, RentObj.CheckOutStationId AS CheckOutStationId, RentObj.Lic_ShortDescr AS Lic_ShortDescr, RentObj.CustKeyPES AS CustKeyPES, RentObj.PESNotice AS PESNotice, RentObj.PESAmountDisc AS PESAmountDisc, RentObj.PESAmountNet AS PESAmountNet, RentObj.hasSellBill AS hasSellBill, RentObj.TaxPerc AS TaxPerc, RentObj.PurchaseDicountAmount AS PurchaseDicountAmount, RentObj.AmountSellPlan AS AmountSellPlan, RentObj.AmountSellNet AS AmountSellNet, RentObj.SellDiscPerc AS SellDiscPerc, RentObj.AmountSellDisc AS AmountSellDisc, RentObj.SellTaxId AS SellTaxId, RentObj.SellTaxPerc AS SellTaxPerc, RentObj.AmountSellGross AS AmountSellGross, RentObj.PurchaseTaxAmount AS PurchaseTaxAmount, RentObj.AmountSellTax AS AmountSellTax, RentObj.AgentIdBuyer AS AgentIdBuyer, RentObjType.RentObjTypeNo AS RentObjTypeNo, CostCarrier.Descr1 AS CostCarrier_Descr1, CostCarrier.CostCarrierNo AS CostCarrierNo, CostCarrier.CostCarrierNoC AS CostCarrierNoC, Ledger.LedgerNo AS LedgerNo, Ledger.Descr1 AS Ledger_Descr1, Ledger.TaxPercentage AS TaxPercentage, Ledger.LedgerNoC AS LedgerNoC, RentObj.StateShortDescr AS StateShortDescr, RentObj.AssetNo AS AssetNo, BusinessArea.ShortDescr AS BusinessAreaShortDescr, RentObj.BusinessAreaId AS BusinessAreaId, BusinessAreaAsset.ShortDescr AS BussAreaAssetShortDescr, RentObj.BussAreaAssetId AS BussAreaAssetId, RentObj.ExtCode1 AS ExtCode1, RentObj.ExtCode2 AS ExtCode2, RentObj.ExtCode3 AS ExtCode3, RentObj.ExtCode4 AS ExtCode4, RentObj.ExtCode5 AS ExtCode5, RentObj.ClassificDamageImageId AS ClassificDamageImageId, RentObj.NextAppointmentDate AS NextAppointmentDate, RentObj.NextAppointmentKM AS NextAppointmentKM, RentObj.NextAppointmentDateType AS NextAppointmentDateType, RentObj.NextAppointmentKMType AS NextAppointmentKMType, RentObj.hasBuyBill AS hasBuyBill, RentObj.DoAutoCalculateCosts AS DoAutoCalculateCosts, RentObj.WKZNo AS WKZNo, RentObj.WKZBonusNo AS WKZBonusNo, RentObj.PlanStartAvailability AS PlanStartAvailability, Classification11.Code AS CCode_11, Classification12.Code AS CCode_12, RentObj.PESAmountOpen AS PESAmountOpen, RentObj.PESAmountBooked AS PESAmountBooked, RentObj.LastAccrualDate AS LastAccrualDate, RentObj.ClassificDescr6 AS ClassificDescr6, Classification_1.ShortDescr AS ShortDescr_CL1, Classification_2.ShortDescr AS ShortDescr_CL2, Classification_3.ShortDescr AS ShortDescr_CL3, Classification_4.ShortDescr AS ShortDescr_CL4, Classification_5.ShortDescr AS ShortDescr_CL5, Classification_6.ShortDescr AS ShortDescr_CL6, Classification_7.ShortDescr AS ShortDescr_CL7, Classification_8.ShortDescr AS ShortDescr_CL8, Classification_9.ShortDescr AS ShortDescr_CL9, Classification_10.ShortDescr AS ShortDescr_CL10, Classification11.ShortDescr AS ShortDescr_CL11, Classification12.ShortDescr AS ShortDescr_CL12, RentObj.cid_modda AS cid_modda, RentObj.CountABDataInt1 AS CountABDataInt1, RentObj.CountABDataInt2 AS CountABDataInt2, RentObj.CountABDataInt3 AS CountABDataInt3, 'EMA' AS EMA, RentObj.SellPlanDampingPr AS SellPlanDampingPr, RentObj.OwnRentObj AS OwnRentObj, Classification_1.ClassificationId AS ClassificationId1, Classification_10.ClassificationId AS ClassificationId10, Classification_2.ClassificationId AS ClassificationId2, Classification_3.ClassificationId AS ClassificationId3, Classification_4.ClassificationId AS ClassificationId4, Classification_5.ClassificationId AS ClassificationId5, Classification_6.ClassificationId AS ClassificationId6, Classification_7.ClassificationId AS ClassificationId7, Classification_8.ClassificationId AS ClassificationId8, Classification_9.ClassificationId AS ClassificationId9, Classification11.ClassificationId AS ClassificationId11, Classification12.ClassificationId AS ClassificationId12 FROM CRM.Car Car, CRM.Classification Classification12 RIGHT OUTER JOIN CRM.Classification Classification11 RIGHT OUTER JOIN CRM.BusinessArea BusinessAreaAsset RIGHT OUTER JOIN CRM.BusinessArea BusinessArea RIGHT OUTER JOIN CRM.Ledger Ledger RIGHT OUTER JOIN CRM.CostCarrier CostCarrier RIGHT OUTER JOIN CRM.RentObjType RentObjType RIGHT OUTER JOIN CRM.Classification Classification_2 RIGHT OUTER JOIN CRM.Classification Classification_3 RIGHT OUTER JOIN CRM.Classification Classification_4 RIGHT OUTER JOIN CRM.Classification Classification_5 RIGHT OUTER JOIN CRM.Classification Classification_6 RIGHT OUTER JOIN CRM.Classification Classification_7 RIGHT OUTER JOIN CRM.Classification Classification_8 RIGHT OUTER JOIN CRM.Classification Classification_9 RIGHT OUTER JOIN CRM.Classification Classification_10 RIGHT OUTER JOIN CRM.Classification Classification_1 RIGHT OUTER JOIN CRM.RentObj RentObj ON Classification_1.ClassificationId = RentObj.Classific1 ON Classification_10.ClassificationId = RentObj.Classific10 ON Classification_9.ClassificationId = RentObj.Classific9 ON Classification_8.ClassificationId = RentObj.Classific8 ON Classification_7.ClassificationId = RentObj.Classific7 ON Classification_6.ClassificationId = RentObj.Classific6 ON Classification_5.ClassificationId = RentObj.Classific5 ON Classification_4.ClassificationId = RentObj.Classific4 ON Classification_3.ClassificationId = RentObj.Classific3 ON Classification_2.ClassificationId = RentObj.Classific2 ON RentObjType.RentObjTypeId = RentObj.RentObjTypeId ON CostCarrier.CostCarrierId = RentObj.CostCarrierId ON Ledger.LedgerId = RentObj.LedgerId ON BusinessArea.BusinessAreaId = RentObj.BusinessAreaId ON BusinessAreaAsset.BusinessAreaId = RentObj.BussAreaAssetId ON Classification11.ClassificationId = RentObj.Classific11 ON Classification12.ClassificationId = RentObj.Classific12 WHERE Car.RentObjId = RentObj.RentObjId AND (((RentObj.ObjEvent = '20')) AND ((RentObj.State = '02')) AND RentObj.isAlienRentObj = 0) AND (RentObj.CompanyId = 10) ORDER BY 14 ASC
|
|
|
|
|
Joining 18 tables does not seem like a good idea. You need a data warehouse with denormalized design.
Maybe the Right outer join is a business requirement. But we should not assume that the database will just handle this!! Here comes our responsibility as developers, designers and DBAs to optimize the structure to satisfy business needs.
|
|
|
|
|
|
Basically that's not a hard select for a database, especially for db2. But if you have performance problems, something is wrong, no doubt about it. Perhaps you are lacking indices, logic in the query is wrong etc. It's not possible to say based on the query text what's the right corrective action (or I suspect actions) in your this case.
For the performance: I think you don't have to argue about it. Simply take the execution plan and go through it. It will reveal your bottlenecks in this query.
But most importantly, you should go through the logic of the database design. I don't say that it's bad since I'm not aware of the logic nor the requirements, but I doubt that there are problems.
Some observations:
- why are the tables having same names, with a running number at the end. Some kind of custom partitioning or is it reasoned
- seems that there are cumulated fields in the tables
- based on conditions this seems like an OLTP query but based on the select and from portions the modeling is more close to a star-model
- if the rows in classification tables are just 0/1 related to rentobj, perhaps use scalar subqueries in select
- this contains two different joining syntaxes (between car and rentobj). Use just one etc...
|
|
|
|
|
Hello,
Many thanks for the replies.
The problem is. This query does not work at all. The query returns with an error telling me that there is no temporary system tablespace which can handle this query (pagesize). Without the order by clause the query is working. Strange enough, no error number is returned, only the description (in german)
An object (rentobj) are assigned 12 categories like manufactor , type of object (truck,van,bus) etc. The category descriptions are stored in an own table. There can be only one category assigned to one object (1:1) But one object can have up to 12 categories, this is why there are 12 categories numbered from 1 til 12. Basically there are 12 joins on the same table category using 12 different fields in rentobj. This joins are used to retrieve the description of each category of the object.
I am not the DBA of this database so I can't change system tables space settings. And the design is done by somebody else.
The base table rentobj has more than hundred thousend records. I can't look at the execution plan. The query tool we are using does not allow that.
Many thanks again
modified on Wednesday, December 10, 2008 11:09 PM
|
|
|
|
|
dl4gbe wrote: Without the order by clause the query is working.
Obviously too much I/O and space usage.
dl4gbe wrote: I can't change system tables space settings
Concentrate on the execution plan. Find the bottlenecks and eliminate them. For starters create efficient indices if not present.
dl4gbe wrote: I can't look at the execution plan. The query tool we are using does not allow that
Have a chat with your DBA or whoever has the access to correct tools (if I remember correctly, graphical tool for db2 administration has a very nice execution plan visualization).
If you're not allowed to modify system settings, nor design, I hope that you still may create indexes (perhaps with you DBA). Otherwise you don't have tools to correct the problem.
|
|
|
|
|
I am building a query which returns a few calculated values. Some of these are as simple as ColA - ColB, while others are far more complex - but are often based on the result of a previous result. My question is, how can I reuse these fields that I have calculated? A lot of the time that I do these calculations, I also need to exclude some values in the WHERE clause, based on these answers.
Example:
SELECT ColA - ColB [Result]
FROM Table
WHERE (ColA - ColB) > 0
How could I calculate ColA - ColB just once - and use the result of that in the where clause or another column?
Thanks in advance
|
|
|
|
|
One way is to use inline views, like:
SELECT alias1.Result
FROM (SELECT (ColA - ColB) AS Result
FROM Table) alias1
WHERE alias1.result > 0
|
|
|
|
|
Thanks for the reply. I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis, but using inline views does the trick as well - so thanks.
|
|
|
|
|
toticow wrote: I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis
That won't be possible (and wouldn't be efficient) since sql is set based language. What you were hoping for would be record based and this would be close to cursor handling (and of course could be implemented in an procedure using cursors). But you don't want to use cursors since (as Mycroft in this forum said) they are evil
Also have a look at other set based variations Ben Fair posted.
toticow wrote: so thanks
You're welcome.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
