none
Selecting the right fields RRS feed

  • Question

  • I have a "SalesTable" in my ERP-system and I need to set up a stored procedure to return to me data from at least 1 but up to 5 sales orders.

     

    First problem:

    How can I make the "WHERE OrderNumber = " flexible so it can handle 1 or 2 or ....or 5 numbers ?

     

    Second problem:

    I shall have an address returned for each OrderNumber - sometimes the delivery address is empty and then I should have the invoice address returned - but if the delivery address IS present then I shall have the delivery address returned.

     - how can I make it work ?

     

    Best regards

    KSor, Denmark

     

    Monday, August 25, 2008 1:49 PM

Answers

  • Regarding the first problem. In a case of SQL Server 2005, you could pass XML string with the required ID to stored procedure. Here is sample of SP

     

    CREATE PROCEDURE GetOrders
     @IDs varchar(max)
    AS
    BEGIN
     
    SET NOCOUNT ON;

    DECLARE @Values xml
    SET @Values=@IDs

    SELECT * FROM Orders WHERE ID IN (SELECT TableResult.Columns.value('@value','INT')
     FROM @Values.nodes('/Orders/ID') as TableResult(Columns))

    END

     

    Here is sample of XML you need to pass to retrieve orders with IDs 10 and 20. You could pass as many values as you wish here

     

    <Orders><ID value="10" /><ID value="200" /></Orders>


    Second problem: I do not know structure of your table and how you join them, but if your query could return different values using next kind of condition

     

    SELECT ..... , ISNULL(DeliveryAddress, InvoiceAddress) FROM ....

     

    This query will return value from DeliveryAddress if it is not NULL and will return InvoiceAddress value otherwise.

     

    Monday, August 25, 2008 11:45 PM
    Moderator
  • It would be something like

     

    SELECT ISNULL(DlvAdr1, InvAdr1) as Adr1,

    CASE

       WHEN DlvAdr1 IS NULL THEN InvAdr2

       ELSE DlvAdr2

    END as Adr2,

    CASE

       WHEN DlvAdr1 IS NULL THEN InvAdr3

       ELSE DlvAdr3

    END as Adr3,

    CASE

       WHEN DlvAdr1 IS NULL THEN InvAdr4

       ELSE DlvAdr4

    END as Adr4

     

    Thursday, August 28, 2008 6:47 PM
    Moderator
  • The posted code is correct. If you see squares for some charcters then most likely you have some issues with database encoding. In addition NULL and empty string are totally different things. NULL means value was not initialized, but empty string means value of empty string and if you check for NULL than test will, of course, fail. If you need to check for empty values as well, you need to put additonal logic for it.

     

    Tuesday, September 2, 2008 5:12 PM
    Moderator
  • Maybe. But does my latest posted solution help you in this case?

    Friday, September 12, 2008 9:49 AM
    Moderator

All replies

  • Regarding the first problem. In a case of SQL Server 2005, you could pass XML string with the required ID to stored procedure. Here is sample of SP

     

    CREATE PROCEDURE GetOrders
     @IDs varchar(max)
    AS
    BEGIN
     
    SET NOCOUNT ON;

    DECLARE @Values xml
    SET @Values=@IDs

    SELECT * FROM Orders WHERE ID IN (SELECT TableResult.Columns.value('@value','INT')
     FROM @Values.nodes('/Orders/ID') as TableResult(Columns))

    END

     

    Here is sample of XML you need to pass to retrieve orders with IDs 10 and 20. You could pass as many values as you wish here

     

    <Orders><ID value="10" /><ID value="200" /></Orders>


    Second problem: I do not know structure of your table and how you join them, but if your query could return different values using next kind of condition

     

    SELECT ..... , ISNULL(DeliveryAddress, InvoiceAddress) FROM ....

     

    This query will return value from DeliveryAddress if it is not NULL and will return InvoiceAddress value otherwise.

     

    Monday, August 25, 2008 11:45 PM
    Moderator
  •  

    Thanks VMazur - it's just working so fine ;-))  !

     

    A little fix anyway :

     

    My Invoice address is like this:

     

    InvAdr1

    InvAdr2

    InvAdr3

    InvAdr4

     

    And my delivery address is like this:

     

    DlvAdr1

    DlvAdr2

    DlvAdr3

    DlvAdr4

     

    And if DlvAdr1<>NULL it has to return DlvAdr1, DlvAdr2, DlvAdr3 and DlvAdr4 no matter the content of DlvAdr2, DlvAdr3 and DlvAdr4.

     

    The solution now returns InvAdr3 if DlvAdr3=NULL ;-((

     

    Best regards

    KSor, Denmark

    Tuesday, August 26, 2008 8:33 AM
  • I am a little bit confused about reply. Do you still need help with address fields?

     

    Thursday, August 28, 2008 9:45 AM
    Moderator
  • Hi again !

     

    YES I need help with the address fields.

     

    I have 4 fields for Invoice address and 4 fields for Delivery address.

     

    I have coded my stored procedure like this:

     

    ISNULL(DlvAdr1, InvAdr1)

    ISNULL(DlvAdr2, InvAdr2)

    ISNULL(DlvAdr3, InvAdr3)

    ISNULL(DlvAdr4, InvAdr4)

     

    If DlvAdr1, DlvAdr2 and DlvAdr4 is not null and all 4 invoice addresses is NOT null - then data returned will be:

     

    DlvAdr1

    DlvAdr2

    InvAdr3       ;-((

    DlvAdr4

     

    Maybe it should be something like this psoudo:

     

    If DlvAdr1<>null then return ALL Dvl-fields else return ALL Inv-fields.

     

    I hope I am clear now ,-))

     

    Best regards

    KSor, Denmark

     

    Thursday, August 28, 2008 10:14 AM
  • It would be something like

     

    SELECT ISNULL(DlvAdr1, InvAdr1) as Adr1,

    CASE

       WHEN DlvAdr1 IS NULL THEN InvAdr2

       ELSE DlvAdr2

    END as Adr2,

    CASE

       WHEN DlvAdr1 IS NULL THEN InvAdr3

       ELSE DlvAdr3

    END as Adr3,

    CASE

       WHEN DlvAdr1 IS NULL THEN InvAdr4

       ELSE DlvAdr4

    END as Adr4

     

    Thursday, August 28, 2008 6:47 PM
    Moderator
  • Thanks - just working fine !

     

    Best regards

    KSor, Denmark

     

    Friday, August 29, 2008 10:10 AM
  • In fact it's NOT working !

     

    My DlvAdr1 is empty when I look at teh field in my ERP system but when I use the field in SQL testíng if it's NULL then the test fails !

     

    In SQL the field has a length of 1 and is shown as a small square when data is returned from my stored procedure.

     

    What is wrong ?

     

     

    Tuesday, September 2, 2008 1:31 PM
  • The posted code is correct. If you see squares for some charcters then most likely you have some issues with database encoding. In addition NULL and empty string are totally different things. NULL means value was not initialized, but empty string means value of empty string and if you check for NULL than test will, of course, fail. If you need to check for empty values as well, you need to put additonal logic for it.

     

    Tuesday, September 2, 2008 5:12 PM
    Moderator
  • The spooky character has value of 2

     

    What is this character and how can I "kill" it in the SQL statements ?

     

    Best regards

    KSor, Denmark

     

    Monday, September 8, 2008 8:26 AM
  • It is control character. You can remove it or replace it with sapce or some other character using next kind of statement.

     

    Example replaces ASCII characters code 2 with space character

     

    UPDATE MyTable SET MyColumnName = REPLACE(MyColumnName, CHAR(2), CHAR(32))

    Thursday, September 11, 2008 9:46 AM
    Moderator
  • I don't dare to change it ;-((

     

    I'm afraid MS Dynamics C5 will not run - I'm told it assumes char(2) for NULL-strings !

     

    But thanks for the statement anyway !

     

    /KSor, Denmark

     

    Thursday, September 11, 2008 10:07 AM
  • That's first time I hear that NULL is ASCII code 2. It would mean that I would not be able to store characters with ASCII code 2. NULL is not specific character, but more like a state of the value. You can use same REPLACE function in your SELECT statement and convert string to NULL in a case if it is empty, something like

     

    SELECT NULLIF(REPLACE(MyColumnName, CHAR(2),"),") FROM   .....

     

    Friday, September 12, 2008 9:36 AM
    Moderator
  • ;-)) I can't tell you why "MS Dynamics C5" is using Ascii 2 for null - I can just that it's a fact !

     

    Some years ago Microsoft bought a danish software product C5 and converted it to "MS Dynamics C5" - maybe it's some "left overs" from danish developers - I don't know !

     

    Best regards

    KSor, Denmark

     

    Friday, September 12, 2008 9:47 AM
  • Maybe. But does my latest posted solution help you in this case?

    Friday, September 12, 2008 9:49 AM
    Moderator
  • Yes

     

     

    Friday, September 12, 2008 9:59 AM