Results 1 to 9 of 9

Thread: Query errors

  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Query errors

    Hi, I'm working on VB.net and I'm always worked with SQL but now the new db is in oracle, I have the next query but this sends me the next errors:

    SELECT
    NVL(FISCAL_YEAR,0) AS 'FISCAL_YEAR',
    NVL(KPMG_NUM_BIS,0) AS 'KPMG_NUM_BIS',
    NVL(KPMG_SOC_GER,'') AS 'KPMG_SOC_GER',
    NVL(KPMG_SOC_NAME2,'') AS 'KPMG_SOC_NAME2',
    NVL(KPMG_TOTGAS_3,0) AS 'KPMG_TOTGAS_3',
    NVL(KPMG_HON_VAL,0) AS 'KPMG_HON_VAL',
    NVL(KPMG_PORC_VAL2,0) AS 'KPMG_PORC_VAL2',
    NVL(KPMG_ING_CTBL,0) AS 'KPMG_ING_CTBL',
    NVL(KPMG_PORC_ING,0) AS 'KPMG_PORC_ING',
    NVL(KPMG_INT_IMP,0) AS 'KPMG_INT_IMP',
    NVL(KPMG_CONT_NETA,0) AS 'KPMG_CONT_NETA',
    NVL(KPMG_PORC_CNET,0) AS 'KPMG_PORC_CNET',
    NVL(KPMG_DIAS_CART,0) AS 'KPMG_DIAS_CART'
    FROM PS_KPMG_GTE_CNETVW

    The error is the next:

    ORA-00923: FROM keyword not found where expected

    After, I change the query to this

    SELECT
    NVL(FISCAL_YEAR FROM PS_KPMG_GTE_CNETVW,0) AS 'FISCAL_YEAR',
    NVL(KPMG_NUM_BIS FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_NUM_BIS',
    NVL(KPMG_SOC_GER FROM PS_KPMG_GTE_CNETVW,'') AS 'KPMG_SOC_GER',
    NVL(KPMG_SOC_NAME2 FROM PS_KPMG_GTE_CNETVW,'') AS 'KPMG_SOC_NAME2',
    NVL(KPMG_TOTGAS_3 FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_TOTGAS_3',
    NVL(KPMG_HON_VAL FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_HON_VAL',
    NVL(KPMG_PORC_VAL2 FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_PORC_VAL2',
    NVL(KPMG_ING_CTBL FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_ING_CTBL',
    NVL(KPMG_PORC_ING FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_PORC_ING',
    NVL(KPMG_INT_IMP FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_INT_IMP',
    NVL(KPMG_CONT_NETA FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_CONT_NETA',
    NVL(KPMG_PORC_CNET FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_PORC_CNET',
    NVL(KPMG_DIAS_CART FROM PS_KPMG_GTE_CNETVW,0) AS 'KPMG_DIAS_CART'
    FROM PS_KPMG_GTE_CNETVW

    The error was the next:

    ORA-00909: invalid number of arguments


    Please I hope you can help me.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think column aliases should be in double quotes, not single quotes.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2009
    Posts
    4
    Thank you, I try it but it doesnt solve the problem

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for table PS_KPMG_GTE_CNETVW
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by fogonacho
    I try it but it doesnt solve the problem
    I think you'd better post the sql that you ran and the error message.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT Nvl(fiscal_year,0)     AS fiscal_year,
           Nvl(kpmg_num_bis,0)    AS kpmg_num_bis,
           Nvl(kpmg_soc_ger,'')   AS kpmg_soc_ger,
           Nvl(kpmg_soc_name2,'') AS kpmg_soc_name2,
           Nvl(kpmg_totgas_3,0)   AS kpmg_totgas_3,
           Nvl(kpmg_hon_val,0)    AS kpmg_hon_val,
           Nvl(kpmg_porc_val2,0)  AS kpmg_porc_val2,
           Nvl(kpmg_ing_ctbl,0)   AS kpmg_ing_ctbl,
           Nvl(kpmg_porc_ing,0)   AS kpmg_porc_ing,
           Nvl(kpmg_int_imp,0)    AS kpmg_int_imp,
           Nvl(kpmg_cont_neta,0)  AS kpmg_cont_neta,
           Nvl(kpmg_porc_cnet,0)  AS kpmg_porc_cnet,
           Nvl(kpmg_dias_cart,0)  AS kpmg_dias_cart
    FROM   ps_kpmg_gte_cnetvw
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2009
    Posts
    4
    When I start the project, the query in SQL was this:

    SELECT ISNULL(FISCAL_YEAR,0) AS 'FISCAL_YEAR',
    ISNULL(KPMG_NUM_BIS,0) AS 'KPMG_NUM_BIS',
    ISNULL(KPMG_SOC_GER,'') AS 'KPMG_SOC_GER',
    ISNULL(KPMG_SOC_NAME2,'') AS 'KPMG_SOC_NAME2',
    ISNULL(KPMG_TOTGAS_3,0) AS 'KPMG_TOTGAS_3',
    ISNULL(KPMG_HON_VAL,0) AS 'KPMG_HON_VAL',
    ISNULL(KPMG_PORC_VAL2,0) AS 'KPMG_PORC_VAL2',
    ISNULL(KPMG_ING_CTBL,0) AS 'KPMG_ING_CTBL',
    ISNULL(KPMG_PORC_ING,0) AS 'KPMG_PORC_ING',
    ISNULL(KPMG_INT_IMP,0) AS 'KPMG_INT_IMP',
    ISNULL(KPMG_CONT_NETA,0) AS 'KPMG_CONT_NETA',
    ISNULL(KPMG_PORC_CNET,0) AS 'KPMG_PORC_CNET',
    ISNULL(KPMG_DIAS_CART,0) AS 'KPMG_DIAS_CART'
    FROM PS_KPMG_GTE_CNETVW

    This query doesnt have problems, It Runs perfect, the problem began when I try to convert this query to Oracle because the new server works with oracle.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by fogonacho
    This query doesnt have problems, It Runs perfect, the problem began when I try to convert this query to Oracle because the new server works with oracle.
    I guess you mean "Microsoft SQL Server" when you say SQL.

    Well Microsoft violates the standard SQL and allows single quotes for column aliases (and a lot more dumb syntax that I have never understood).

    As Nick has pointed out, in Oracle a column alias - if you do want to quote it - needs to be quoted with double quotes.

    So your SQL should look like this:
    Code:
    SELECT NVL(FISCAL_YEAR,0) AS "FISCAL_YEAR"
    ...
    Or you can simply leave out the quotes alltogether as anacedent has shown.

    The next time you have any error post the exact query you were running and the exact error message and please do use [code] tags to format the SQL.
    For details on how to use the [code] tags see here:
    http://www.dbforums.com/misc.php?do=bbcode#code

  9. #9
    Join Date
    Aug 2009
    Posts
    4
    Thanks a Lot!!!

    anacedent, with your example I can solve the problem, shammat, I dont know how to post, this is my first time, but thanks to you too, the next time I hope I can help you too.

    Regards

Posting Permissions

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