Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Unanswered: Help Please..problem with executing a Query in Sybase

    Hi all,

    I have a problem with executing a Query in Sybase. I am using Sybase version 11.5.1(Windows XP).

    1) The following Query executes in Sybase without generating any error providing output.

    ***) select * from <table name> group by <column1, column2> having count(1)>1.

    This Query shouldn’t have executed by generating an error that the columns in Select clause should be present in the Group by clause too. Even though it is not so here the query executes finely in Sybase.

    2) The following query should have been executed fine in Sybase (or in any other Database for that matter)

    ***) select * from <table name> where (<column1, column2>) in (select <column1, column2> from <table name> group by <column1, column2> having count(1)>1)

    But in Sybase it generates the following error

    Server Message: Number 102, Severity 15

    Line 1:

    Incorrect syntax near ','.

    But this Query works fine in DB2 and as expected the first query do generates an error in DB2 citing the same reason.


    What is the problem with that query in Sybase?? Please do help me on this situation.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Quote Originally Posted by itsmeGanesh
    ***) select * from <table name> group by <column1, column2> having count(1)>1.
    From what I recall this might be allowable. Search for FIPS-Flagger and Group By on Google, I seem to recall an article talking about setting fips-flagger on would produce an error with that SQL, or at least it says what is allowed by ASE.

    Quote Originally Posted by itsmeGanesh
    ***) select * from <table name> where (<column1, column2>) in (select <column1, column2> from <table name> group by <column1, column2> having count(1)>1)
    No, this SQL would not be executable in "every" SQL DBMS, especially not one as old as ASE 11.5. I'm not entirely sure that it exists in the ANSI SQL specifications either (that statement is imprecise and not at all clear as to what it would do).

    You would have to do an EXISTS subquery to accomplish that in ASE11.5, or two IN statements.
    Thanks,

    Matt

  3. #3
    Join Date
    Jul 2004
    Posts
    2
    Quote Originally Posted by itsmeGanesh
    ***) select * from <table name> group by <column1, column2> having count(1)>1.
    This is allowable on Sybase, but is an extension to the ANSI standard.
    It might be very slow on large tables!!!!!!!
    (It's a join between every group created and the base table on the group by column)

    Refer to the Transact SQL user's Guide.

    Quote Originally Posted by itsmeGanesh
    ***) select * from <table name> where (<column1, column2>) in (select <column1, column2> from <table name> group by <column1, column2> having count(1)>1)
    This is part of the ANSI standard and it is allowed on Oracle.
    Best Regards,
    Ale.

  4. #4
    Join Date
    Aug 2004
    Posts
    4

    Thanks.....another query??

    Thanks for the response. The following query

    ***) select * from <table name> where (<column1, column2>) in (select <column1, column2> from <table name> group by <column1, column2> having count(1)>1)
    is used for........
    Actually I have a table wherein i have to retrieve the entire row of data where there are duplicates. I say Duplicates only based on two columns...(here it is Column1 and column2). So i think the query which is given above will work.

    I also tried with two where in statements......but it does not provide the desired results....as a combination of values creeps into the result such that it doesnt have any duplicate......so only the above query will help i suppose.......


    The query select <column1, column2> from <table name> group by <column1, column2> having count(1)>1 is going to retrieve only those column values where there is duplicate as we r grouping by only those columns and filtering out where there are more than 1 values for it using the having clause........

    But this query is not working in Sybase generating error.

    AS far as i know SQL is an standard which all the databases should follow or adhere to.......and may have enhancements to that......

    Now my question is whether the above query is suitable only for certain Databases and not to others??? If so on what basis they demarcate it???

  5. #5
    Join Date
    Aug 2004
    Posts
    6
    I believe you need to break apart the query in this manner:

    select * from <table name> where
    <column 1> in (select <column 1>
    from FDWtms..tblPIPSGQTAdminProdMap group by
    <column 1>, <column 2> having count(1) > 1)
    and
    <column 2> in (select <column 2>
    from FDWtms..tblPIPSGQTAdminProdMap group by
    <column 1>, <column 2> having count(1) > 1)

  6. #6
    Join Date
    Aug 2004
    Posts
    4

    Query!!

    I suppose this wont work......because....

    Let the Data in the table be (for example)

    Col1 col2 col3 col4 .......
    ================================================== ==========
    1 2 asdf asdas ..........
    1 2 asd dsffg ..........
    2 3 asd dfsdfsdfd ..........
    2 5 ertsdfg ertwefdsf ..........
    2 5 vgsg sgs .............
    1 5 sdf fgfd ............

    In this case the duplicate columns are (based on col1 and col2) row 1,2,4 and 5. I want the output of these rows only....

    But the query U have sent....

    select * from <table name> where
    <column 1> in (select <column 1>
    from FDWtms..tblPIPSGQTAdminProdMap group by
    <column 1>, <column 2> having count(1) > 1)
    and
    <column 2> in (select <column 2>
    from FDWtms..tblPIPSGQTAdminProdMap group by
    <column 1>, <column 2> having count(1) > 1)

    first inner subquery will return 1(from 1 &2 combination) and 2(from 2 & 5 combination) only .....
    second sub query will return 2(from 1 &2 combination) and 5(from 2 & 5 combination) only ..... right!!!

    now then that query will return rows 1,2,3,4 and 5..........The problem is the fifth row
    1 5 sdf fgfd ............
    also creeps into the output with this query.....

    this query wont work i suppose.......

  7. #7
    Join Date
    Aug 2004
    Posts
    6
    Gotcha, I thought you were more concerned on the multiple columns used with the "IN" statement. By using a join to the same table, you can produce the result:

    create table tempsel
    (
    field1 int,
    field2 int,
    field3 varchar(5)
    )

    insert tempsel values (1,2,'Row 1')
    insert tempsel values (1,2,'Row 2')
    insert tempsel values (2,3,'Row 3')
    insert tempsel values (2,5,'Row 4')
    insert tempsel values (2,5,'Row 5')
    insert tempsel values (1,5,'Row 6')
    go

    select a.* from tempsel a, tempsel b
    where
    a.field1 = b.field1 and
    a.field2 = b.field2
    group by a.field1,a.field2 having count(1) > 1



    Result:

    field1 field2 field3
    1 2 Row 1
    1 2 Row 2
    2 5 Row 4
    2 5 Row 5

  8. #8
    Join Date
    Aug 2004
    Posts
    4
    I think even the following query should work....

    select * from <table> group by <col1>,<col2> having count(1)>1



Posting Permissions

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