0

In the following sybase query I would like to pull the vendor column twice: the first time I would like to pull the vendor associated with the ReceiveWorksheet table by associating it with a key RCW_VEN_FK, and I then would like to pull from the same table again but this time associating it with the StockInventoryLocal table by the key SIL_VEN_FK_Default. The goal is to pull the data into two different columns - one which will show the default vendor and one which will show the vendor associated with the receiving worksheet.

I've been looking for quite some time on how to accomplish this. The first time I received an error that the item was already created (when trying to simply pull from the Vendor table twice), I then tried with aliasing and was unable to accomplish it in that manner.

Does anyone have a suggestion or a resource on how to pull this information from the Vendor table twice and associate it with different keys in different tables and have it populate as two different columns?

ALTER VIEW "ecrs"."00_Test3_BD"( /* view_column_name, ... */ )
AS
SELECT "ecrs"."Vendor"."VEN_Code",
"ecrs"."Vendor"."VEN_CompanyName",
"ecrs"."StockInventoryLocal"."SIL_VEN_FK_Default",
"ecrs"."ReceiveWorksheet"."RCW_VEN_FK"
FROM ( "ecrs"."Vendor" JOIN "ecrs"."StockInventoryLocal" As "DefaultVendor" ON "ecrs"."Vendor"."VEN_PK" = "ecrs"."StockInventoryLocal"."SIL_VEN_FK_Default" ) JOIN "ecrs"."ReceiveWorksheet" As "WorksheetVendor"  ON "ecrs"."Vendor"."VEN_PK" = "ecrs"."ReceiveWorksheet"."RCW_VEN_FK"

I tried the above and received an error "Table 'DefaultVendor' not found."

  • I know nothing special about sybase but in databases you can alias your tables during query which means you can use the same table multiple times by just aliasing each instance to a separate name. The syntax should be something like vendor AS vendor1 and vendor AS vendord2. – Patrick Mevzek Dec 27 '17 at 20:28
  • I tried that, but how do I pull from the different tables for the different alias' in the from statement? – Ben Denody Dec 27 '17 at 20:34
  • You use vendor1 and vendor2 exactly as table names, you can use them everywhere you would have used a table name. Only after having defined them of course (that is after vendor AS vendor1 and vendor AS vendor2) . If Sybase supports them, "Common Table Expressions" could help you too maybe. – Patrick Mevzek Dec 27 '17 at 20:47
  • Also try to put here a smaller example showing your problem because your current one if far too big to parse for someone not having the context and the details of your business case. – Patrick Mevzek Dec 27 '17 at 20:48
  • Updated the post with a smaller example and am getting the same error I was getting before. – Ben Denody Dec 27 '17 at 20:57
1

Assuming ecrs is a user/schema, and keeping in mind I'm not quite following the textual description (eg, not sure which column(s) from the Vendor table you're trying to display twice), see if the following gets you started ... we can tweak based on your feedback:

SELECT v.VEN_Code,
       v.VEN_CompanyName,
       sil.SIL_VEN_FK_Default,
       rcw.RCW_VEN_FK

FROM   ecrs.Vendor v

JOIN   ecrs.StockInventoryLocal sil
ON     v.VEN_PK = sil.SIL_VEN_FK_Default

JOIN   ecrs.ReceiveWorksheet rcw
ON     v.VEN_PK = rcw.RCW_VEN_FK

I'm assuming this isn't really what you want since you mention wanting to pull different vendors, ie, v.VEN_PK = sil.SIL_VEN_FK_Default = rcw.RCW_VEN_FK (all the same vendor).

If sil.SIL_VEN_FK_Default and rcw.RCW_VEN_FK are different values, then we'll need two references to ecrs.Vendor, but then I don't understand what the relationship is between StockInventoryLocal and ReceiveWorksheet.

In other words, let's say we join Vendor(#1) with StockInventoryLocal, and we join Vendor(#2) with ReceiveWorksheet, how do we join these two sets of joins to each other (eg, is there a relationship between StockInventoryLocal and ReceiveWorksheet? is there a relationship berween the two Vendors?)

| improve this answer | |
  • Thanks for the answer, markp., even though it was directly applicable to the situation it was helpful! I made a bunch of adjustments and it's now working, I want to upvote your answer but don't have enough points :(. – Ben Denody Jan 3 '18 at 15:46
  • yeah, didn't think it would be a perfect answer as we were missing a few details; as for the upvoting ... no problem ... though you should be able to accept the answer (if appropriate); regards – markp-fuso Jan 3 '18 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.