ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

sql error

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sql error

    hi all::

    1. double, 3ble check on liblist : only one file called "FC.CTSD" on qs36f : it has 4 keys

    2. & NO file called "BC.CTSD"

    3. cpyf FC.CTSD TO BC.CTSD

    4. check total records : both file are same

    what i going to do is find (count) the duplicate records in "FC.CTSD"

    Code:
    SELECT COUNT(*) FROM QS36F/"FC.CTSD" A                        
    WHERE rrn(A) > (select min(rrn(B))                            
    from QS36F/"FC.CTSD" b WHERE A.CTSCNO = B.CTSCNO AND A.CTSCAT 
    =B.CTSCAT AND A.CTSSHE = B.CTSSHE AND A.CTSSSQ =B.CTSSSQ)
    i think above stmt is correct

    it shows incorrect figure actually i end the process bcos it selected records more than i hope

    but when i use same qry to 'BC.CTSD' ?? (use F9 to retrive statment & change F to B) it shows correct result???

    check again and again with 2 senior PGMRs

    wondering!!!

    what's the wrong ????

    thanks
    dhanuxp

  • #2
    Re: sql error

    Regards

    Kit
    http://www.ecofitonline.com
    DeskfIT - ChangefIT - XrefIT
    ___________________________________
    There are only 3 kinds of people -
    Those that can count and those that can't.

    Comment


    • #3
      Re: sql error

      thanks kit

      but i still confuse why this statment Generate different result ??

      before 2month ago i had same confusion over sql stmt?? !!our server

      thanks

      Comment


      • #4
        Re: sql error

        Originally posted by dhanuxp View Post
        thanks kit

        but i still confuse why this statment Generate different result ??

        before 2month ago i had same confusion over sql stmt?? !!our server

        thanks
        You may find the files are not exactly the same. Although both files contain the same records, they may not be the same RRN's. For example, deleted records use up a RRN, but generally do not get copied. So the RRN in the A file may not match up to the RRN in the B file. Use the method suggested in the other thread and don't bother with making a copy of the file.

        Comment


        • #5
          Re: sql error

          happy to hear your answer arrow, i had big doubt about why it gives 2 result
          i think its the problem....

          1st file shows
          Code:
          Total number of members  . . . . . . . . . :                 1
          Total number of members not available  . . :                 0
          Total records  . . . . . . . . . . . . . . :            565773
          Total deleted records  . . . . . . . . . . :              2271
          2nd file shows
          Code:
          Total number of members  . . . . . . . . . :                 1
          Total number of members not available  . . :                 0
          Total records  . . . . . . . . . . . . . . :            565773
          Total deleted records  . . . . . . . . . . :             48462
          this may be the problem

          thanks
          dhanuxp
          Last edited by dhanuxp; September 29, 2008, 09:24 PM.

          Comment


          • #6
            Re: sql error

            Hi,

            if you just want to make sure that all colums in rows in both tables are identical, just execute the following SQL-Statement:

            PHP Code:
               (Select from Table1 
                   Except 
                Select 
            from Table2)
            Union All
               
            (Select from Table2
                   Except
                Select 
            from Table1
            If no row is returned, both tables contain exactly are identical.
            If rows are returned you may execute the first full-Select (all before Union), to determine the rows that are in table1 and not in table2. After you execute the second full-Select (all after UNION ALL) to determine the rows that are in table2 and not in table1.

            Deleted rows are not compared!

            Birgitta

            Comment


            • #7
              Re: sql error

              hi Birgitta ,
              If rows are returned you may execute the first full-Select (all before Union), to determine the rows...? yes i have

              pls give a example or explanation about (first full-Select - all before Union)
              or do u means like this??

              Code:
              2007-FEB>>>
               With .... (0 to n Commontable expressions) 
                 Full-Select (consists of 1 + n Sub-Selects combined with either UNION, EXCEPT or INTERSECT) 
                    Sub-Select ... 
                        SELECT ... (Field-Selection) including additional Sub-Selects 
                           FROM ... (Table-Selection) with all available JOIN-Clauses 
                           WHERE ... (Conditions) 
                           GROUP BY ... (Grouping) 
                           HAVING ... (Conditions for Grouped Values) 
                    Union / Except / Intersect 
                    Sub-Select ... 
              Order By ... (Sorting)
              thanks
              dhanuxp

              Comment

              Working...
              X