Post reply

INSERT statement

  • October 19, 2012 at 4:15 am

    #268773

    Hi,

    I have a table which holds the status of peoples orders. It holds information on order_id, order_status, order_desc, letter.

    A letter is automatically printed for each of the different statuses. However for one of the statuses 'Dispatched' i need two different letters to be printed.

    So far i have done

    insert into order_status (order_id,order_status,

    order_desc, letter) values('025', 'Dispatched', 'Black tailored coat ','DPT notification');

    This prints the DPT notification fine but i need another letter to print along with this. How can I insert two values into the same field?

    The letters are stored in the sys_docs table.

    It would be helpful if anyone has any ideas on how to do this!

    Thanks.

  • October 19, 2012 at 4:32 am

    #1550780

    New letter, new row:

    INSERT INTO order_status (order_id,order_status, order_desc, letter)

    VALUES

    ('025', 'Dispatched', 'Black tailored coat ','DPT notification'),

    ('025', 'Dispatched second notification', 'Black tailored coat ','DPT2 notification');

    You may need to change your data types and primary key constraint to do this, but it's the right way of doing it.

    John

  • October 19, 2012 at 4:54 am

    #1550790

    Thanks for your help.

    I have tried this. However when the status is 'dispatched' i need the two letters to print together.

    Doing it this way means to print the second letter the status has to be 'Dispatched second notification'.

    Is there a way i can make them both print when the status is dispatched?

    Thanks.

  • October 19, 2012 at 5:43 am

    #1550813

    I take it you have a table called Status or something like that, to which the main table has a foreign key constraint? That table would contain values such as Received, Processed, Packed, Dispatched. I would create a new table called StatusLetters, with a foreign key constraint to Status, something like this:

    Status~Letter

    Received~Receipt Notification

    Processed~Process Notification

    Packed~Pack Notification

    Dispatched~Dispatch Notification 1

    Dispatched~Dispatch Notification 2

    John

  • October 26, 2012 at 9:32 am

    #1553255

    I'm guessing the printing is initiated by a trigger? In that case include a condition inside the trigger which initiates the printing of a second letter in cases where the status is 'dispatched'.

    Or, if it's an application loading the content of the table, change the application code.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply