Need help with a view with 6 tables 1

[MzKitty]

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

 

[Maximizer]

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

http://www.cmrc.qc.ca

 

[MzKitty]

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