Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2017
    Posts
    2

    Unanswered: Question on a NUMERIC function in the SQL Query

    Hi,

    I am working on a SQL Query that was written by a former employee some years ago. This Query is using a Numeric function in a Left Outer Join condition at 2 places. The syntax of the function is given below:

    AND ("NUMERIC"(TABLE_A.DEF_VENDORID, 655372) = TABLE_X.DEF_VENDORID)

    Note:
    1. Most importantly, this SQL Query is running in production on an IBM Netezza database.
    2. In TABLE_A, the column DEF_VENDORID is a Text field and can hold a maximum of 4000 bytes.
    3. In TABLE_X, the column DEF_VENDORID is a Numeric field and can hold a maximum of 10 digits.

    My task is to make any changes necessary to this Query so that it runs on an Oracle database.

    Now, I have never come across this type of NUMERIC function usage in Oracle. And what I do want to know is 2 things:

    1. Looking at the above syntax, what kind of SQL language is this?
    2. What is the function doing? Is it setting a Constant Value of 655372 to the DEF_VENDORID column from TABLE_A after converting it to a Number?

    Please help.

    Thank you !!

  2. #2
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    Are you sure this isn't a user defined function?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,993
    Provided Answers: 23
    I have no idea what the second parameter to NUMERIC is doing in Netezza, but to convert a varchar to a number you use to_number() in Oracle:

    https://docs.oracle.com/database/121...htm#SQLRF06140
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    The second parameter in the "NUMERIC" function looks like an IBM characterset ID, if that's helpful to know.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  5. #5
    Join Date
    Jun 2017
    Posts
    7
    2. What is the function doing? Is it setting a Constant Value of 655372 to the DEF_VENDORID column from TABLE_A after converting it to a Number?
    We cannot tell much from here what the function is doing, you can found out by running the command and look what rows are shown and compare them with other rows that are not shown and see what is the difference in vendor id.

  6. #6
    Join Date
    Jun 2017
    Posts
    2

    Are you sure this isn't a user defined function?

    Thanks for responding back. I will check on that and get back to you on what I find.

    To add some more, there are CASE statements where the "NUMERIC" keyword is being used to cast the datatype. See example below:

    WHEN (-1 NOTNULL) THEN '-1'::"NUMERIC"
    WHEN (0 NOTNULL) THEN '0'::"NUMERIC"

    I found that PostgreSQL uses double colons in queries for casting from one datatype to another. This is another example of how "NUMERIC" is being used at some places in the Query.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,993
    Provided Answers: 23
    In Oracle you have two way to convert a varchar to a number. to_number() (as already mentioned) and the CAST operator: cast ('-1' as number)

    See the manual for details: https://docs.oracle.com/database/121...htm#SQLRF00613

    Unrelated, but:

    "WHEN (-1 NOTNULL)" does not make any sense to me. -1 can never be null, so that is essentially the same as WHEN (TRUE)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,520
    While researching your problem I came across the following site that has tools to directly convert oracle to ibm netezza database. While that is the wrong direction it has reference material showing the syntax differences between the 2 database

    http://www.sqlines.com/oracle-to-netezza
    Last edited by beilstwh; 06-22-17 at 09:58.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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