Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Unanswered: choose main or preferred supplier ( was "A thing that cannot be solved...?")

    I have a table which contains suppliers that can supply certain articles.
    There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table.
    As a alternative you can use priority. Each supplier has given priority where
    0 means "most preferred" and any other means "less preferred"
    What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.

    Abbreviated description of the table, called AL.

    SuppNo Article Arthuvudavt Prio
    10101 A-10 NULL 0
    10202 A-10 1 0
    10303 A-10 NULL 1
    10101 B-10 NULL 0
    10202 B-10 NULL 0
    10303 B-10 NULL 1
    10101 C-10 NULL 1
    10202 C-10 NULL 0
    10303 C-10 NULL 1

    In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and
    only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.

    Is there any way this could be done?

    The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.

    Thanks in advance
    Jonas

    Sorry for the poor formatting, I didn't have the time to make it right.
    Last edited by Nephilim; 01-24-05 at 11:29.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just sort the data appropriately, and take the first row from the sorted set.

    -PatP

  3. #3
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Well, that's one way of doing if, but it's not really what I wanna do ....

    The thing is yes, you could do what you say, sort it and set rowcount = 1 or something similar, but what I really wanna do is to make a single T-SQL statement that picks one resultset, and only one under every circumstance described, and I don't know how to do just that.

    (Yes, the earlier explanation was pretty crappy, gotta blame me being swedish ...)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    With your reference to rowcount=1, I'll assume that you are using Microsoft SQL (although it could also be Sybase). If that is the case, you can use the TOP 1 clause within your SELECT statement.

    Bra tur!

    -PatP

  5. #5
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    Quote Originally Posted by Pat Phelan
    With your reference to rowcount=1, I'll assume that you are using Microsoft SQL (although it could also be Sybase). If that is the case, you can use the TOP 1 clause within your SELECT statement.

    Bra tur!

    -PatP
    Yeah, came to think of it just when I read your reply PatP. What can I say, you are The Man. Thanks a bunch! "Tack så mycket!"

    Oh, and it's actually "Lycka till!" if I should be picky, but I got the message ...

  6. #6
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Making things a little more complicated ...

    In a insert-trigger, I'd like to fetch and update a purchase order once it's placed, using info. from the earlier described AL table

    SuppNo Article Arthuvudavt Prio First_field Other_Field
    10101 A-10 NULL 0 12.00 12.00
    10202 A-10 1 0 12.00 13.00
    10303 A-10 NULL 1 25.00 35.00
    10101 B-10 NULL 0 1.00 2.00
    10202 B-10 NULL 0 5.00 5.00
    10303 B-10 NULL 1 5.00 6.00
    10101 C-10 NULL 1 11.00 11.00
    10202 C-10 NULL 0 22.00 35.00
    10303 C-10 NULL 1 35.98 52.34

    I would preferreable do this in one single update statement, and not using variables. I would rather not use cursors to loop the Inserted-tables either, since this is supposed to be a major performance issue.
    The purchase order table is called BP and contains the "First_Field" and "Other_Field" which also exists in the AL-table.
    The problem is that I need to do the update and match the supplier to the article ordered in the way the earlier posts by PatP describes, but as I said earlier, I'd like to do this by using only one update-statement. It's also possible for the inserted-table to contain many rows, and therefore I cannot use the "top 1" method discussed earlier.

    If I use the suggested solution by PatP that he described earlier, that works for the problem described in the beginning, but not for this one.
    Anyone have any ideas? (Oh, yes, I am using T-SQL for MS SQL Server.)

    Thanks in advance Jonas

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about using:
    Code:
    CREATE TABLE Nephilim_Supplier (
       SuppNo 	INT		NULL
    ,  Article 	NVARCHAR(20)	NULL
    ,  Arthuvudavt 	INT		NULL
    ,  Prio 	INT		NULL
    ,  First_field 	MONEY		NULL
    ,  Other_Field	MONEY		NULL
       )
    
    CREATE TABLE Nephilim_Invoice_Detail (
       InvoiceID	INT		NULL
    ,  Article	NVARCHAR(20)	NULL
    ,  SuppNo	INT		NULL
    ,  OtherStuff	NVARCHAR(50)	NULL
       )
    
    INSERT INTO Nephilim_Supplier (
       SuppNo, Article,  Arthuvudavt
    ,  Prio,  First_field,  Other_Field
       ) SELECT         10101, 'A-10', NULL, 0, 12.00, 12.00
       UNION ALL SELECT 10202, 'A-10', 1,    0, 12.00, 13.00
       UNION ALL SELECT 10303, 'A-10', NULL, 1, 25.00, 35.00
       UNION ALL SELECT 10101, 'B-10', NULL, 0, 1.00,   2.00
       UNION ALL SELECT 10202, 'B-10', NULL, 0, 5.00,   5.00
       UNION ALL SELECT 10303, 'B-10', NULL, 1, 5.00,   6.00
       UNION ALL SELECT 10101, 'C-10', NULL, 1, 11.00, 11.00
       UNION ALL SELECT 10202, 'C-10', NULL, 0, 22.00, 35.00
       UNION ALL SELECT 10303, 'C-10', NULL, 1, 35.98, 52.34
    
    INSERT INTO Nephilim_Invoice_Detail (
       InvoiceID, Article
       ) SELECT 1001, 'A-10'
       UNION ALL SELECT 1001, 'B-10'
       UNION ALL SELECT 1001, 'C-10'
       UNION ALL SELECT 1002, 'B-10'
       UNION ALL SELECT 1002, 'C-10'
    
    UPDATE Nephilim_Invoice_Detail
       SET SuppNo = (SELECT TOP 1 SuppNo
          FROM Nephilim_Supplier AS s
          WHERE  s.Article = Nephilim_Invoice_Detail.Article
          ORDER BY Arthuvudavt DESC, Prio, SuppNo)
    
    SELECT *
       FROM Nephilim_Invoice_Detail
    -PatP

  8. #8
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Red face Some comlpementary information ...

    My intention was to update the first_field and other field with info from the supplier in a insert-trigger on the Nephilim_Invoice_Detail table.

    The Nephilim_Invoice_Detail table should be altered like this :
    CREATE TABLE Nephilim_Invoice_Detail (
    InvoiceID INT NULL
    , Article NVARCHAR(20) NULL
    , SuppNo INT NULL
    , OtherStuff NVARCHAR(50) NULL
    , First_field money
    , Other_Field money
    )


    I assume you want me to make the trigger in the following way, given your earlier update-statement.

    create trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
    as
    UPDATE Nephilim_Invoice_Detail
    SET
    first_field = (SELECT TOP 1 first_field
    FROM Nephilim_Supplier AS s
    WHERE s.Article = Nephilim_Invoice_Detail.Article),
    other_field = (SELECT TOP 1 other_field
    FROM Nephilim_Supplier AS s
    WHERE s.Article = Nephilim_Invoice_Detail.Article
    ORDER BY Arthuvudavt DESC, Prio, SuppNo)



    If that is the case, that won't work. I apologize for my poor explanation of the problem.

    EDIT :
    It's not working in neither of the two stated cases below:

    insert into Nephilim_Invoice_Detail
    (InvoiceID, Article, SuppNo, OtherStuff)
    select
    1, 'B-10', NULL, 'Some other stuff'


    insert into Nephilim_Invoice_Detail
    (InvoiceID, Article, SuppNo, OtherStuff)
    select
    1, 'B-10', NULL, 'Some other stuff'
    UNION ALL
    select
    1, 'C-10', NULL, 'Some C-10 stuff'
    Last edited by Nephilim; 01-26-05 at 09:56.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is kind of hard to get your head around the first time you see it, but I'd use something like:
    Code:
    UPDATE a
       SET
          a.first_field = b.first_field
    ,     a.other_field = b.other_field
       FROM Nephilim_Invoice_Detail AS a
       JOIN (SELECT TOP 1 z.first_field, z.first_field, z.other_field
          FROM Nephilim_Supplier AS z
          ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
          ON (b.Article = a.Article)
    You might also need to include the inserted pseudo-table too, to kind of keep a leash on things.

    -PatP
    Last edited by Pat Phelan; 01-26-05 at 23:52. Reason: Fixed a bone-headed typo (oversight) in my code!

  10. #10
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Yes, you're probably right, but I still don't get it ....

    Aye, the inserted table should probably be added to the inserted-trigger, but I still cannot get it to work. The insert-trigger now looks like this :


    alter trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
    as
    UPDATE a
    SET
    a.first_field = b.first_field
    , a.other_field = b.other_field
    , a.suppNo = b.suppNo
    FROM Nephilim_Invoice_Detail AS a
    JOIN (SELECT TOP 1 z.first_field, z.other_field, z.Article
    FROM Nephilim_Supplier AS z
    ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
    ON (b.Article = a.Article)
    JOIN inserted on
    a.Article = inserted.article

    Is there something in that code that I've missed? All I did was to add the join to the inserted table in a similar way you added a join to the subtable made by a select.
    Neither of the two inserts in my previous post works ...

    Starting to feel like total "n00b" here, but then again, I probably am ....

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You appear to be missing the SuppNo in the derived table (the Z alias). What are you seeing (error messages, inappropriate rows updated, appropriate rows not updated, blue flames coming out of your server, etc)?

    I can't get much mental traction on "Neither of the two inserts in my previous post works ..." It doesn't help me to understand what the problem is.

    -PatP

  12. #12
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    What I meant with "neither of the two inserts work" was that yes, they are syntactically correct, but in neither of the two inserts the trigger updates the fields I intended to update. The update-trigger "shoots blanks", or whatever you wanna call it ...

    I suppose you want me to alter the trigger into this:

    alter trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
    as
    UPDATE a
    SET
    a.first_field = b.first_field
    , a.other_field = b.other_field
    , a.suppNo = b.suppNo
    FROM Nephilim_Invoice_Detail AS a
    JOIN (SELECT TOP 1 z.first_field, z.other_field, z.Article
    FROM Nephilim_Supplier AS z
    ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
    ON (b.Article = a.Article)
    JOIN inserted on
    a.Article = inserted.article
    join z on
    a.suppno=z.suppno


    if so, I get a
    Server: Msg 208, Level 16, State 1, Procedure Nephilim_Invoice_Detail_insert, Line 3
    Invalid object name 'z'.

    when I try to do the insert. The z-table is alias:ed into table name b, so I thought I shouldn't have to use z in a join since I joined the a and b tables ...

    (Thoughtfully wonders to himself, "wonder how much patience this PatP dude really has with someone like me. Starting to feel annoyed by myself for not getting this ....)

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is mostly "window dressing" to clean things up so its easier for my feeble mind to grasp, along with an outer join just in case there aren't any rows with the inserted.Article value in the Nephilim_Supplier table:
    Code:
    ALTER TRIGGER Nephilim_Invoice_Detail_insert
       ON Nephilim_Invoice_Detail
       FOR INSERT
    AS
    
    UPDATE a
       SET
          a.first_field = b.first_field
    ,     a.other_field = b.other_field
    ,     a.suppNo = b.suppNo
       FROM inserted
       JOIN Nephilim_Invoice_Detail AS a
          ON (a.Article = inserted.Article)
       LEFT OUTER JOIN (SELECT TOP 1
          z.first_field, z.other_field, z.Article, z.SuppNo
          FROM Nephilim_Supplier AS z
          ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
          ON (b.Article = a.Article)
    
    SELECT inserted.*  --  Debugging, let's see what won't match
       FROM inserted
       LEFT OUTER JOIN (SELECT TOP 1
          z.first_field, z.other_field, z.Article, z.SuppNo
          FROM Nephilim_Supplier AS z
          ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
          ON (b.Article = inserted.Article)
       WHERE b.Article IS NULL
    
    RETURN
    The extra SELECT is just for debugging. It will return any rows that are in the inserted pseudo-table that don't have a match in the derived table.

    -PatP

  14. #14
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    What am I missing...?

    With your altered procedure, that now looks like this :

    ALTER TRIGGER Nephilim_Invoice_Detail_insert
    ON Nephilim_Invoice_Detail
    FOR INSERT
    AS

    UPDATE a
    SET
    a.first_field = b.first_field
    , a.other_field = b.other_field
    , a.suppNo = b.suppNo
    FROM inserted
    JOIN Nephilim_Invoice_Detail AS a
    ON (a.Article = inserted.Article)
    LEFT OUTER JOIN (SELECT TOP 1
    z.first_field, z.other_field, z.Article, z.SuppNo
    FROM Nephilim_Supplier AS z
    ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
    ON (b.Article = a.Article)

    SELECT inserted.* -- Debugging, let's see what won't match
    FROM inserted
    LEFT OUTER JOIN (SELECT TOP 1
    z.first_field, z.other_field, z.Article, z.SuppNo
    FROM Nephilim_Supplier AS z
    ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
    ON (b.Article = inserted.Article)
    WHERE b.Article IS NULL

    RETURN


    And the following data in the Nephilim_Supplier - table

    SuppNo Article Arthuvudavt Prio First_field Other_Field
    ----------- -------------------- ----------- ----------- --------------------- ---------------------
    10101 A-10 NULL 0 12.0000 12.0000
    10202 A-10 1 0 12.0000 13.0000
    10303 A-10 NULL 1 25.0000 35.0000
    10101 B-10 NULL 0 1.0000 2.0000
    10202 B-10 NULL 0 5.0000 5.0000
    10303 B-10 NULL 1 5.0000 6.0000
    10101 C-10 NULL 1 11.0000 11.0000
    10202 C-10 NULL 0 22.0000 35.0000
    10303 C-10 NULL 1 35.9800 52.3400


    The following insert still doesn't fill in the fields I wanted in the trigger, first_field, other_field and suppNo.


    insert into Nephilim_Invoice_Detail
    (InvoiceID, Article, SuppNo, OtherStuff)
    select
    1, 'B-10', NULL, 'Some other stuff'


    The example previously posted with two posts in the inserted table doesn't work either:

    insert into Nephilim_Invoice_Detail
    (InvoiceID, Article, SuppNo, OtherStuff)
    select
    1, 'B-10', NULL, 'Some other stuff'
    UNION ALL
    select
    1, 'C-10', NULL, 'Some C-10 stuff'

    With the given info, the result should be that the first_field, other_field and suppNo should be filled with info from the Nephilim_Supplier.

    In the "single-insert"-case, the result should be

    First_Field and Other_Field from supplier 10101 or 10202, it really doesn't matter since this data is breaking the usage of the table. My trigger should however work despite this.)

    In the "dual-insert"-case I wish the result to be

    First_Field and Other_Field for article C-10 should be the data associated with supplier 10202 for that article, since that supplier has the lowest prio.
    First_Field and Other_Field would be updated as stated in the single insert case.

    What I don't understand here is why the debug part concludes that there is no match. Obviously there is, I am using articles with associated suppliers.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the trigger is spitting out rows, at least from it's perspective there isn't a match. There might be datatype issues (char versus nvarchar perhaps), collation differences, or other issues, but the code doesn't see the relationship and that is all that really matters at this point.

    If you can't work this one out:
    1. Create a totally empty database.
    2. Create the tables.
    3. Create the trigger.
    4. Test to be sure it misbehaves.
    5. Detach the database.
    6. Zip the database (mdf and ldf) up.
    7. Post it here.
    8. I'll see what I can do, but it won't be quick because I'm swamped.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •