Alternative solution of quer !!!

  •  

        I'm trying to execute a certain query for only one column, it works fine. But when i try to execute with three column it shows exception. Could anyone provide my the alternate solutions for this query.....

    the query is :

    INSERT

    INTO PHONE_NUMBER_LIST (PHONE_NUMBER,PHONE_MODEL,LANG_PREF)

    SELECT

    PHONE_NUMBER,MIN(PHONE_MODEL),MIN(LANG_PREF) FROM TEMP_TABLE AS s GROUP BY PHONE_NUMBER

    WHERE

    NOT EXISTS (SELECT * FROM PHONE_NUMBER_LIST As t WHERE t.PHONE_NUMBER = s.PHONE_NUMBER)

    --------------

    it's working fine when i'd only one column, i want to try it with my table(consisting of three columns)......

    Please help me in this regard......

    in advance millions of thank.......

  • what kind of error? and i don't see where you are inserting the data into a temp_table anywhere

  •  

       my TEMP_TABLE consisting of values with three columns(ph_no,ph_mod,lang_pref). First i'll get the distinct ph_no with their respective ph_mod and lang_pref then i want to check in the PHONE_NUMBER_LIST for the existing of ph_no(in TEMP_TABLE), if it is same then neglect otherwise insert the row. So, in this way my PHONE_NUMBER_LIST will always be consisting of unique ph_no (irrespective of other two columns). So according to that i wrote a query but i'm getting exception as:

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'WHERE'.

    ----------------------------

    Please help me out.......

  • The 'group by' clause should go after the 'where' clause.

     

    INSERT INTO PHONE_NUMBER_LIST (PHONE_NUMBER,PHONE_MODEL,LANG_PREF)

    SELECT

    PHONE_NUMBER,MIN(PHONE_MODEL),MIN(LANG_PREF) FROM TEMP_TABLE AS s

    WHERE

    NOT EXISTS (SELECT * FROM PHONE_NUMBER_LIST As t WHERE t.PHONE_NUMBER = s.PHONE_NUMBER) GROUP BY PHONE_NUMBER


    And then again, I might be wrong ...
    David Webb

  • David,

    I agree with you, this is what I came up with:

    INSERT INTO PHONE_NUMBER_LIST (

        PHONE_NUMBER,

        PHONE_MODEL,

        LANG_PREF

    )

    SELECT

        PHONE_NUMBER,

        MIN(PHONE_MODEL),

        MIN(LANG_PREF)

    FROM

        TEMP_TABLE AS s

    WHERE

        NOT EXISTS (SELECT

                        *

                    FROM

                        PHONE_NUMBER_LIST As t

                    WHERE

                        t.PHONE_NUMBER = s.PHONE_NUMBER)

    GROUP BY

        PHONE_NUMBER

     

  • Here is another alternative solution as well:

    INSERT INTO PHONE_NUMBER_LIST (

        PHONE_NUMBER,

        PHONE_MODEL,

        LANG_PREF

    )

    SELECT

        PHONE_NUMBER,

        MIN(PHONE_MODEL),

        MIN(LANG_PREF)

    FROM

        TEMP_TABLE AS s

        LEFT OUTER JOIN PHONE_NUMBER_LIST AS t

            on (s.PHONE_NUMBER = t.PHONE_NUMBER)

    WHERE

        t.PHONE_NUMBER IS NULL

    GROUP BY

        PHONE_NUMBER

  •  

      thanks............

      it worked............

Viewing 7 posts - 1 through 7 (of 7 total)

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