Manipulating complex joined query in a view

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack - 22 Jul 2008 21:40 GMT

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

Reply to this Message

Hugo Kornelis - 22 Jul 2008 22:10 GMT

(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.

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message

Roy Harvey (SQL Server MVP) - 22 Jul 2008 22:20 GMT

>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

Reply to this Message

John Bell - 22 Jul 2008 22:21 GMT

>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)
[quoted text clipped - 44 lines]
> 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

Reply to this Message

Jack - 23 Jul 2008 20:02 GMT

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.

> >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)
[quoted text clipped - 98 lines]
>
> John

Reply to this Message