Manipulating complex joined query in a view
in [Programming SQL]
|
From: Jack on 22 Jul 2008 16:40 I have a view in a sql server database. This view involves join on project_num with associated with four entities (along with other joins) tblSMI SM prs.dbo.project PR prs.cbo.tblCost C and (SELECT Project_Num, Plant = MIN(Plant) FROM PRS.dbo.Project_Plant PP2 GROUP BY Project_Num) PP However project_num data in all except for tblSMI are similar type e.g.06-02691-00 or 08-04511-00 The corresponding project_num that comes out of tblSMI SM is corresondingly 06-02691 or 08-04511 Thus in order for the join to succeed, I need to extract the first 8 characters from each of project_num field out of the rest of the three entities. I am trying to do it using the following: Left([Projectno],8) However, it is not working that way. Any help is appreciated. Thanks SELECT PR.Project_Num AS ProjectNo, PR.Gen_Level, PR.Customer_ID, PR.User_ID, PR.Project_Name, PR.Project_Desc, PR.Date_Submitted, PR.Date_Due, PR.Date_Estimated, PR.Date_Completed, PR.Completed, PR.Cust_PartDesc1, PR.Project_Status, PR.Eng_ID, PR.Cust_PartNum1 AS ProjectPartNbr, PR.Cust_PartDesc1 AS ProjectPartDesc, CUST.Company_Name, CUST.City, CUST.State, CUST.Contact_Name, CUST.Phone_Num, PP.Plant, PL.Plant_Desc, PR.YearlyQty, C.aQty, SM.annVol1, SM.accManager, U.Last_Name, U.First_Name FROM prs.dbo.tblSMI SM LEFT OUTER JOIN prs.dbo.Users U ON SM.accManager = U.User_ID RIGHT OUTER JOIN prs.dbo.Project PR LEFT OUTER JOIN prs.dbo.tblCost C ON C.project_num = PR.Project_Num ON SM.project_num = PR.Project_Num LEFT OUTER JOIN prs.dbo.Customer CUST ON PR.Customer_ID = CUST.Customer_ID LEFT OUTER JOIN (SELECT Project_Num, Plant = MIN(Plant) FROM PRS.dbo.Project_Plant PP2 GROUP BY Project_Num) PP ON PP.Project_Num = PR.Project_Num LEFT OUTER JOIN prs.dbo.Plants PL ON PL.Plant = PP.Plant
From: Hugo Kornelis on 22 Jul 2008 17:10 On Tue, 22 Jul 2008 13:40:29 -0700, Jack wrote: (snip) >Thus in order for the join to succeed, I need to extract the first 8 >characters from each of project_num field out of the rest of the three >entities. I am trying to do it using the following: Left([Projectno],8) > >However, it is not working that way. Any help is appreciated. Thanks Hi Jack, What does "it is not working" mean? Do you get error messages? Incorrect results? Smoke in the server room? The best way to let us help you is to give us a clear description and a way to reproduce the problem. In this case, that would mean posting the following: * Table definitions, posted as CREATE TABLE statements, for all tables involved. You may omit irrelevant columns, but do please include all constraints, properties, and indexes. * Sample data, posted as INSERT statements. We don't need hundreds of rows, but we do need enough to show the problems involved in the query. * The current (malfunctioning) query text. (Already posted) * Expected results, based on the sample data in your post, * In case not obvious - the difference between expected output and results you actually see. * If you get errors, the complete text (use copy and paste to prevent typos!) of these error messages. See www.aspfaq.com/5006 for some tips on how to assemble some of these ingredients. -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Roy Harvey (SQL Server MVP) on 22 Jul 2008 17:20 On Tue, 22 Jul 2008 13:40:29 -0700, Jack <Jack(a)discussions.microsoft.com> wrote: >However, it is not working that way. Any help is appreciated. Thanks Along with everything that Hugo said... Are you saying that the ON clause test ON SM.project_num = PR.Project_Num When written as: ON LEFT(SM.project_num,8) = PR.Project_Num is what was not working? I also noticed that amidst a string of LEFT OUTER joins there is a RIGHT OUTER join. Mixing them can be quite tricky, and you probably need to elaborate a bit on why this is being used. Roy Harvey Beacon Falls, CT
From: John Bell on 22 Jul 2008 17:21 "Jack" <Jack(a)discussions.microsoft.com> wrote in message news:DC0184DA-3DE6-4AFA-8492-5FB89C043E73(a)microsoft.com... >I have a view in a sql server database. This view involves join on > project_num with associated with four entities (along with other joins) > > tblSMI SM > prs.dbo.project PR > prs.cbo.tblCost C > and (SELECT Project_Num, Plant = MIN(Plant) > FROM PRS.dbo.Project_Plant PP2 > GROUP BY Project_Num) PP > > However project_num data in all except for tblSMI are similar type > e.g.06-02691-00 or 08-04511-00 > The corresponding project_num that comes out of tblSMI SM is > corresondingly > 06-02691 or 08-04511 > > Thus in order for the join to succeed, I need to extract the first 8 > characters from each of project_num field out of the rest of the three > entities. I am trying to do it using the following: Left([Projectno],8) > > However, it is not working that way. Any help is appreciated. Thanks > > > SELECT PR.Project_Num AS ProjectNo, PR.Gen_Level, PR.Customer_ID, > PR.User_ID, PR.Project_Name, PR.Project_Desc, PR.Date_Submitted, > PR.Date_Due, > PR.Date_Estimated, PR.Date_Completed, PR.Completed, > PR.Cust_PartDesc1, PR.Project_Status, PR.Eng_ID, PR.Cust_PartNum1 AS > ProjectPartNbr, > PR.Cust_PartDesc1 AS ProjectPartDesc, > CUST.Company_Name, CUST.City, CUST.State, CUST.Contact_Name, > CUST.Phone_Num, > PP.Plant, > PL.Plant_Desc, PR.YearlyQty, C.aQty, SM.annVol1, > SM.accManager, U.Last_Name, U.First_Name > FROM prs.dbo.tblSMI SM LEFT OUTER JOIN > prs.dbo.Users U ON SM.accManager = U.User_ID RIGHT > OUTER JOIN > prs.dbo.Project PR LEFT OUTER JOIN > prs.dbo.tblCost C ON C.project_num = PR.Project_Num > ON > SM.project_num = PR.Project_Num LEFT OUTER JOIN > prs.dbo.Customer CUST ON PR.Customer_ID = > CUST.Customer_ID LEFT OUTER JOIN > (SELECT Project_Num, Plant = MIN(Plant) > FROM PRS.dbo.Project_Plant PP2 > GROUP BY Project_Num) PP ON PP.Project_Num = > PR.Project_Num LEFT OUTER JOIN > prs.dbo.Plants PL ON PL.Plant = PP.Plant Hi Why are you doing so many outer joins? SELECT PR.Project_Num AS ProjectNo, PR.Gen_Level, PR.Customer_ID, PR.User_ID, PR.Project_Name, PR.Project_Desc, PR.Date_Submitted, PR.Date_Due, PR.Date_Estimated, PR.Date_Completed, PR.Completed, PR.Cust_PartDesc1, PR.Project_Status, PR.Eng_ID, PR.Cust_PartNum1 AS ProjectPartNbr, PR.Cust_PartDesc1 AS ProjectPartDesc, CUST.Company_Name, CUST.City, CUST.State, CUST.Contact_Name, CUST.Phone_Num, PP.Plant, PL.Plant_Desc, PR.YearlyQty, C.aQty, SM.annVol1, SM.accManager, U.Last_Name, U.First_Name FROM prs.dbo.tblSMI SM LEFT OUTER JOIN prs.dbo.Users U ON SM.accManager = U.User_ID RIGHT OUTER JOIN prs.dbo.Project PR ON SM.project_num = LEFT(PR.Project_Num,8) LEFT OUTER JOIN prs.dbo.tblCost C ON C.project_num = PR.Project_Num LEFT OUTER JOIN prs.dbo.Customer CUST ON PR.Customer_ID = CUST.Customer_ID LEFT OUTER JOIN (SELECT Project_Num, Plant = MIN(Plant) FROM PRS.dbo.Project_Plant PP2 GROUP BY Project_Num) PP ON PP.Project_Num = PR.Project_Num LEFT OUTER JOIN prs.dbo.Plants PL ON PL.Plant = PP.Plant The join between tblCost and Project will use all 11 characters, and the project_plant groups will be per 11 character project number. If you want to ignore the last 3 characters then both sides of the join condition will need to use LEFT. I suggest you break the query down into fewer tables and make sure you get the expected results then build up from there. John
From: Jack on 23 Jul 2008 15:02 Thanks Hugo, Roy and John for your input. I believe I got this solution. Instead of trying to work on this complex view to use truncate on project, I used truncate on project to create various source view . I used this comlex view, changed the source veiw to the new ones. With this approach it worked. Regards. "John Bell" wrote: > > "Jack" <Jack(a)discussions.microsoft.com> wrote in message > news:DC0184DA-3DE6-4AFA-8492-5FB89C043E73(a)microsoft.com... > >I have a view in a sql server database. This view involves join on > > project_num with associated with four entities (along with other joins) > > > > tblSMI SM > > prs.dbo.project PR > > prs.cbo.tblCost C > > and (SELECT Project_Num, Plant = MIN(Plant) > > FROM PRS.dbo.Project_Plant PP2 > > GROUP BY Project_Num) PP > > > > However project_num data in all except for tblSMI are similar type > > e.g.06-02691-00 or 08-04511-00 > > The corresponding project_num that comes out of tblSMI SM is > > corresondingly > > 06-02691 or 08-04511 > > > > Thus in order for the join to succeed, I need to extract the first 8 > > characters from each of project_num field out of the rest of the three > > entities. I am trying to do it using the following: Left([Projectno],8) > > > > However, it is not working that way. Any help is appreciated. Thanks > > > > > > SELECT PR.Project_Num AS ProjectNo, PR.Gen_Level, PR.Customer_ID, > > PR.User_ID, PR.Project_Name, PR.Project_Desc, PR.Date_Submitted, > > PR.Date_Due, > > PR.Date_Estimated, PR.Date_Completed, PR.Completed, > > PR.Cust_PartDesc1, PR.Project_Status, PR.Eng_ID, PR.Cust_PartNum1 AS > > ProjectPartNbr, > > PR.Cust_PartDesc1 AS ProjectPartDesc, > > CUST.Company_Name, CUST.City, CUST.State, CUST.Contact_Name, > > CUST.Phone_Num, > > PP.Plant, > > PL.Plant_Desc, PR.YearlyQty, C.aQty, SM.annVol1, > > SM.accManager, U.Last_Name, U.First_Name > > FROM prs.dbo.tblSMI SM LEFT OUTER JOIN > > prs.dbo.Users U ON SM.accManager = U.User_ID RIGHT > > OUTER JOIN > > prs.dbo.Project PR LEFT OUTER JOIN > > prs.dbo.tblCost C ON C.project_num = PR.Project_Num > > ON > > SM.project_num = PR.Project_Num LEFT OUTER JOIN > > prs.dbo.Customer CUST ON PR.Customer_ID = > > CUST.Customer_ID LEFT OUTER JOIN > > (SELECT Project_Num, Plant = MIN(Plant) > > FROM PRS.dbo.Project_Plant PP2 > > GROUP BY Project_Num) PP ON PP.Project_Num = > > PR.Project_Num LEFT OUTER JOIN > > prs.dbo.Plants PL ON PL.Plant = PP.Plant > > Hi > > Why are you doing so many outer joins? > > SELECT PR.Project_Num AS ProjectNo, > PR.Gen_Level, > PR.Customer_ID, > PR.User_ID, > PR.Project_Name, > PR.Project_Desc, > PR.Date_Submitted, > PR.Date_Due, > PR.Date_Estimated, > PR.Date_Completed, > PR.Completed, > PR.Cust_PartDesc1, > PR.Project_Status, > PR.Eng_ID, > PR.Cust_PartNum1 AS ProjectPartNbr, > PR.Cust_PartDesc1 AS ProjectPartDesc, > CUST.Company_Name, > CUST.City, > CUST.State, > CUST.Contact_Name, > CUST.Phone_Num, > PP.Plant, > PL.Plant_Desc, > PR.YearlyQty, > C.aQty, > SM.annVol1, > SM.accManager, > U.Last_Name, > U.First_Name > FROM prs.dbo.tblSMI SM > LEFT OUTER JOIN prs.dbo.Users U ON SM.accManager = U.User_ID > RIGHT OUTER JOIN prs.dbo.Project PR ON SM.project_num = > LEFT(PR.Project_Num,8) > LEFT OUTER JOIN prs.dbo.tblCost C ON C.project_num = PR.Project_Num > LEFT OUTER JOIN prs.dbo.Customer CUST ON PR.Customer_ID = CUST.Customer_ID > LEFT OUTER JOIN (SELECT Project_Num, Plant = MIN(Plant) > FROM PRS.dbo.Project_Plant PP2 > GROUP BY Project_Num) PP ON PP.Project_Num = PR.Project_Num > LEFT OUTER JOIN prs.dbo.Plants PL ON PL.Plant = PP.Plant > > The join between tblCost and Project will use all 11 characters, and the > project_plant groups will be per 11 character project number. If you want to > ignore the last 3 characters then both sides of the join condition will need > to use LEFT. > > I suggest you break the query down into fewer tables and make sure you get > the expected results then build up from there. > > > John >
|
Pages: 1 Prev: Dashboard "Missing Index" Report and Resetting Indexes Next: question on indexing |