Data from Same Column Joined in Two Different Ways

aliassybasesybase-sql-anywhere

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

Best Answer

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?)

Related Solutions

Query for COLUMN_NAME LIKE “X” in sybase(Transact SQL)

First: This isn't easy to implement and it's going to break very easily (by which I mean performance is going to be horrible, and it's a nice little route for SQL injection attacks if you're not very very careful). I strongly advise you to re-think what you're doing because there has to be a better way.

Second: The question as it stands doesn't actually make sense - what condition are you checking on the columns? NOT NULL? Also, do you want to AND or OR the conditions together?

To actually answer the question, though, to do this you'll need to write some dynamic SQL:

Link sysobjects to syscolumns, filter by the table name and the fields you're interested in.
Build a executable SQL statement using a cursor, then execute it.

Example below is, for each column in the Users table that starts with either 'A' or 'B', return all rows in the Users table that have a non-null value in any of those columns.

Note: I don't have a Sybase install handy to test this. Chances of it actually working are very low, but it should hopefully be enough to make you realise what a bad idea this is.

DECLARE @colname nvarchar(255),
        @sql nvarchar(4000)

SET @sql = 'SELECT * FROM Users WHERE '

DECLARE colcur CURSOR FOR
    SELECT sc.name 
    FROM syscolumns sc 
            INNER JOIN 
         sysobjects so ON so.id = sc.id
    WHERE so.name = 'Users'
        AND so.type = 'U'
        AND (sc.name LIKE 'A%' OR sc.name LIKE 'B%')
    ORDER BY sc.name
OPEN colcur

WHILE 1=1
BEGIN
    FETCH colcur INTO @colname 
    SET @sql = @sql + @colname + ' IS NOT NULL OR '
END

DEALLOCATE colcur

SET @sql = LEFT(@sql, LEN(@sql) - 3) -- need to trim off the trailing 'OR'

EXEC @sql

SQLRemote: Implicit synch of connected objects

It appears that this can be done with subqueries inside publications.

From documentation:

CREATE PUBLICATION SalesRepData (
TABLE SalesReps
TABLE Customers
   SUBSCRIBE BY rep_key
TABLE Contacts
SUBSCRIBE BY (SELECT rep_key
   FROM Customers
   WHERE Contacts.cust_key = Customers.cust_key )
);

This would catch all customer contacts for sales rep's customer.

Quiet important seems to be, that this needs to be updated once the connection has been updated -- e.g. a sales rep is getting new customers. This needs to be done with an BEFORE trigger for UPDATE/INSERT/DELETE.

Best Answer

Related Solutions

Query for COLUMN_NAME LIKE “X” in sybase(Transact SQL)

SQLRemote: Implicit synch of connected objects

Related Question