4 Replies Latest reply on May 5, 2008 5:07 PM by Matt Wegner

    query problem

      Query #1 returns U_shpinv a user defined field in marketing documents title defined as alphameric 10
      SELECT DISTINCT T0.DocNum, T0.Address2, T1.TaxCode FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.CardCode =N'1' and T0.DocDate >=%1 and T0.Address2 >%2
      Query #2 is supposed to return another user defined field from the marketing document title which is U_shpnm alphameric 40. nothing is returned.
      If I change the query to be equal to an entered value, which is the same as the U_shpinv it works fine. What is wrong with query #2





        • 1. RE: query problem

          also tried with the second query = U_SHPINV without the T0.

          • 2. RE: query problem
            Matt Roberts


            I don't think you're providing enough details to answer your question...

            So: you have a user-defined field that has a number in it - a U_SHPNM (tracking number?). You're trying to get a list of all tracking numbers where the document number is equal to the number in U_SHPINV. Now, I have no idea what circumstances in your business processes cause U_SHPINV to equal the document number, so I really can't determine whether or not this is the right criteria for you to be using, and therefore whether or not this is the reason why you're getting no results.

            That aside: why are you using the CAST function on the document number, especially as VARCHAR? If the UD field is alphanumeric, then you should be using NVARCHAR if you're going to use CAST at all - which you really shouldn't have to.



            • 3. RE: query problem

              what i'm trying to do is populate a series of user fields that contain the ship to name and address city state and county and sales tax code from an invoice that has already been entered to a new order for the same job. in the first query i get the document number of the desired invoice then in the second query i want to get the name for the document selected in the first query. all this is because i think i can only get one field in each query. if i can get more than one field i'll get them all in one query. the first query does return the document number correctly and if i make the second query use a variable like %0 it works fine but i don't want to re-enter the document number for each part of the ship to address. if this makes any sense i too will be amazed


              thanks, i tried usinf nvarchar and it had the same results.


              • 4. RE: query problem

                the query should be

                SELECT T0.U_SHPNM FROM OINV T0 WHERE T0.DocNum = $[http://ORDR.U_SHPINV|http://ORDR.U_SHPINV]

                this works