INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help with a view with 6 tables

Need help with a view with 6 tables

(OP)
I',m using PSQL 9.5. I have a working view with 6 tables in it; it works fine.  Now the user wants me to add another table to it.  The new table is a system table, SyNote.  My problem is that the Slordnam table does not have notes for every order, so the link to the system table would have to be a left outer join.  Will all of the other tables need to be redefined as left outer joins?  Here is my view:

REATE VIEW "ACQtdPrice" AS

SELECT "t1" ."CustomerID" ,"t6" ."Name" ,"t1" ."OrderID" ,"t1" ."TypeID" ,

"t5" ."Description" "Type Desc" ,"t1" ."OrderDate" ,"t1" ."BeginDate" ,"t1" ."billaddress1" ,

"t1" ."billaddress2" ,"t1" ."billcity" ,"t1" ."billstate" ,"t1" ."billzip" ,"t1" ."FirstDate" ,

"t1" ."LastDate" ,"t1" ."Description1" ,"t1" ."Description2" ,"t1" ."Description3" ,

"t1" ."PurchaseOrder" ,"t1" ."ReleaseID" ,"t1" ."Contact" ,"t1" ."Feeable" ,"t1" ."FeeCodeID" ,

"t1" ."TaxCodeID" ,"t1" ."Taxable" ,"t1" ."TaxCertNo" ,"t1" ."TaxCertRecvDate" ,

"t1" ."SalespersonID" ,"t1" ."ZoneID" ,"t1" ."OrderStatus" ,"t1" ."InvoiceType" ,

"t1" ."CreditType" ,"t1" ."DiscountLevel" ,"t6" ."unitdiscount" "Discount By Level" ,

"t6" ."County" ,"t2" ."LocationID" ,"t2" ."ProductID" ,"t2" ."UpdatePrice" ,"t2" ."UpdateFreight" ,

"t2" ."UpdateProdDesc" ,"t2" ."Price" ,"t1" ."PriceListID" ,"t2" ."Frt1FreightRateType" ,

"t2" ."Frt1FreightRate" ,"t2" ."Frt1FreightPay" ,"t1" ."FreightFob" ,"t2" ."UnitCost" ,

"t2" ."OrderQty" ,"t2" ."ShipQty" ,"t2" ."MatlUnitID" ,"t3" ."Description" "Prod Desc" ,

"t1" ."RequirePO" ,"t1" ."carrierID"

FROM "slordnam" "t1" ,"slorder" "t2" ,"inproduc" "t3" ,"Sytype" "t5" ,"slcust" "t6"

WHERE "t1" ."orderID" = "t2" ."orderid"  AND "t2" ."productID" = "t3" ."productid"

AND "t1" ."OrderFlag" = 'O' AND ("t1" ."TypeID" = "t5" ."TypeID" AND "t5" ."Category" = '@ ORDER TYPE' )

AND "t1" ."customerID" = "t6" ."CustomerID"

The new table would be "Synote" "t7" and it links to Slordnam.NotesID = Synote.NotesID.  I don't even know if this is possible.

Thanks - Cathy
 

RE: Need help with a view with 6 tables

Greetings!  I have had almost sixty (60) tables linked as LEFT OUTER JOINS in Pervasive to create Crystal Reports with success.

  You may want to DTS into MS-SQL for improved performance with larger tables.

Thanks!

James Keep, PMP
Crystal Reports(tm) Certified Consultant 8.5 (CRCC)
Authorized Crystal Engineer (ACE)
CMRC
Crystal Decisions Business Partner
Montreal, Qc, Canada
www.cmrc.qc.ca

RE: Need help with a view with 6 tables

(OP)
thanks for replying so fast.  I thought that that was the way I was going to have to go, but I needed to bounce it off of someone else.  Thansk.
Cathy

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close