Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2008
    Posts
    17

    Unanswered: help with this query

    I have a table with 5 fields and need to get some values. I have created an example scenario table for explaining. See screenshot for table structure and scenarios.

    http://img357.imageshack.us/img357/9734/69408157pc6.jpg

    1: Populate all rows where DUT / ENG translation text is missing
    2: Populate all rows where % tag is used
    3: Populate all rows where DUT/ENG or ENG/DUT translation text are same
    4: Populate all rows where DUT or ENG row is missing

    These scenarios can be done using 1 or 2 (3) queries. In reality the table contains more than 4000 rows.

    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want us to come up with the values? what if we don't speak dutch?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    17
    No not with values, its for filtering rows that match does scenarios.

    I am trying this below for scenario 2, but its not working:
    SELECT * FROM `tbl_translation`
    WHERE Text like '%\%\%' Escape '\'
    Last edited by internationalist; 11-23-08 at 08:43.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "populate" in database terminology means either INSERT or UPDATE

    but if all you want to do is find them, that's easy

    1. ... WHERE COALESCE(text,'') = ''

    2. ... WHERE LOCATE('%',text) > 0

    3. requires a join (or a very nasty NOT EXISTS subquery)

    4. can be combined with 3.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2008
    Posts
    17
    By populate I meant 'display, view' but wasn't aware of DB terminology, thnx

    1 and 2 works fine, for 3 I used join same table but something goes wrong.

    SELECT *
    FROM tbl_translation DUT
    JOIN tbl_translation ENG
    ON ENG.ID = DUT.ID
    WHERE (
    DUT.TEXT = ENG.TEXT
    )
    I am testing on phpmyadmin (mysql) and at school we use DB2, which i noticed now. So will these query work on DB2 environment aswell? functions like LOCATE for instance?
    Last edited by internationalist; 11-23-08 at 10:34.

  6. #6
    Join Date
    Jan 2008
    Posts
    17

    Help needed for queries

    I have a table with 5 fields and need to get some values. I have created an example scenario table for explaining. See screenshot for table structure and scenarios.

    http://img357.imageshack.us/img357/9734/69408157pc6.jpg

    1: Get all rows where DUT / ENG translation text is missing
    2: Get all rows where % tag is used
    3: Get all rows where DUT/ENG or ENG/DUT translation text are same
    4: Get all rows where DUT or ENG row is missing

    These scenarios can be done using 1 or 2 (3) queries. In reality the table contains more than 4000 rows. Can anyone help me with these queries?

    Thank you

    ps. Will MySQL function like LOCATE work on DB2 environment aswell??

  7. #7
    Join Date
    Jan 2008
    Posts
    17
    I was assuming sql query will work on DB2 aswell, but functions as LOCATE might work only on MySQL? Therefore if anyone could help me with these queries.

    See screenshot for table structure information..

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, in general you have to look up each function in the SQL Reference Manual to find out how they work in each database system

    COALESCE should work in both

    LOCATE might work in DB2, but you need to test it

    i looked it up in the DB2 manual, and it says
    If the search-string has a length of zero, the result returned by the function is 1. Otherwise, if the source-string has a length of zero, the result returned by the function is 0. Otherwise:
    If the value of search-string is equal to an identical length of substring of contiguous positions within the value of source-string, the result returned by the function is the starting position of the first such substring within the source-string value.
    Otherwise, the result returned by the function is 0.
    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 supports LOCATE function.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Information about SQL functions for DB2 can be found in the SQL Reference Vol 1, avaialble for download from the IBM website.
    http://www-01.ibm.com/support/docvie...id=swg27009474
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jan 2008
    Posts
    17
    Thnx I will try that tomorrow, could you guys help me with queries for scenario 3 and 4?

    3: Get all rows where DUT/ENG or ENG/DUT translation text are same
    4: Get all rows where DUT or ENG row is missing

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query you had in post #5 was very close

    i've added a small adjustment, shown in red --
    Code:
    SELECT *
      FROM tbl_translation AS dut
    INNER
      JOIN tbl_translation AS eng 
        ON eng.Id = dut.Id
       AND eng.LanId = 'ENG'
       AND eng.text = dut.text
     WHERE dut.LanId = 'DUT'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2008
    Posts
    17
    Thnx that works, so for scenario 4 I use then this:

    SELECT *
    FROM tbl_translation AS dut
    INNER JOIN tbl_translation AS eng ON eng.Id = dut.Id
    AND eng.LanId = 'ENG'
    OR dut.lanId = 'DUT'
    AND eng.text = dut.text
    WHERE dut.text = ''

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you test that last suggestion?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can check scenarios 1 to 4 at once.
    Like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH translation(id, version, done, lanid, text) AS (
    VALUES
     (1, 1, 'N', 'DUT', 'Welkom')
    ,(1, 1, 'N', 'ENG', 'Welcome')
    ,(2, 1, 'N', 'DUT', 'Gebruikers online')
    ,(2, 1, 'N', 'ENG', '')                                 /* 1 */
    ,(3, 1, 'N', 'ENG', 'Your username is %USER-NAME')      /* 2 */
    ,(3, 1, 'N', 'DUT', 'Jouw gebruikernaam is %USER-NAME') /* 2 */
    ,(4, 1, 'N', 'DUT', 'Try to login again')               /* 3 */
    ,(4, 1, 'N', 'ENG', 'Try to login again')               /* 3 */
    ,(5, 1, 'N', 'DUT', 'Ga terug')                         /* 4 */
    ,(6, 1, 'N', 'ENG', 'Edit account')                     /* 4 */
    )
    /* End of test data */
    SELECT id
           /* 1: rows where DUT / ENG translation text is missing */
         , CASE WHEN dut_text = '' OR eng_text = '' THEN 'Y' END
             AS "1:"
           /* 2: rows where % tag is used */
         , CASE WHEN LOCATE('%',dut_text) > 0 OR LOCATE('%',eng_text) > 0 THEN 'Y' END
             AS "2:"
           /* 3: rows where DUT/ENG or ENG/DUT translation text are same */
         , CASE WHEN dut_text = eng_text THEN 'Y' END
             AS "3:"
           /* 4: rows where DUT or ENG row is missing */
         , CASE WHEN dut_text IS NULL OR eng_text IS NULL THEN 'Y' END
             AS "4:"
         , dut_text
         , eng_text
      FROM (SELECT id
                 , MAX(CASE WHEN lanid = 'DUT' THEN text END) AS dut_text
                 , MAX(CASE WHEN lanid = 'ENG' THEN text END) AS eng_text
              FROM translation
             GROUP BY id
           ) S;
    ------------------------------------------------------------------------------
    
    ID          1: 2: 3: 4: DUT_TEXT                         ENG_TEXT                        
    ----------- -- -- -- -- -------------------------------- --------------------------------
              1 -  -  -  -  Welkom                           Welcome                         
              2 Y  -  -  -  Gebruikers online                                                
              3 -  Y  -  -  Jouw gebruikernaam is %USER-NAME Your username is %USER-NAME     
              4 -  -  Y  -  Try to login again               Try to login again              
              5 -  -  -  Y  Ga terug                         -                               
              6 -  -  -  Y  -                                Edit account                    
    
      6 record(s) selected.

Posting Permissions

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