Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Unanswered: Help Needed on Db2 query

    Hi All,

    Urgent Help for a Db2 query .

    I have the below query for genrating a report


    db2 "select a0.familyname, a0.givenname,id.USERID, case a1.iscmpsysadmin when '1' then 'Administrator' when '0' then 'Non-Administrator' end case,case a1.isapprover when '1' then 'Approver' when '0' then 'Non-Approver' end case,case a1.isactive when '1' then 'Active' when '0' then 'Inactive' end case,a4.customerid, a5.name,
    case a4.isenrolled when '1' then 'Enabled' when '0' then 'Dis-abled' end case,a6.billacc, a6.CUSTCRSNUMBER as SUCTCRSNO, a7.classofsvcname,case a6.fundsauth when '1' then 'Funds Checking On' when '0' then 'Funds Checking Off' end case,a6.chargemod ,case a6.dualadmin when '1' then 'Dual Admin' when '0' then 'Single Admin' end case,a4.corcustomerky,char(date(SA.updatedttm),EUR ),a6.challengequestion from SONEDBA.corperson a0, SONEDBA.bnkemployee a1, SONEDBA.corpersonorg a2, SONEDBA.corcustomer a3,SONEDBA.bnkcustomer a4, SONEDBA.cororg a5, SONEDBA.wbccompanytd a6, SONEDBA.bnkclassofsvc a7,SONEDBA.ARCUSERAUTHNTICN SA,SONEDBA.ARCIDENTITY ID,SONEDBA.BNKUSER BK,SONEDBA.bnkperson BP
    where a0.corpersonky = a2.corpersonky
    and a0.corpersonky = a1.corpersonky
    and a2.cororgky = a3.cororgky
    and a3.corcustomerky = a4.corcustomerky
    and a2.cororgky = a5.cororgky
    and a2.cororgky = a6.objectid
    and a4.bnkclassofsvcky = a7.bnkclassofsvcky
    and BP.BNKUSERKY=BK.BNKUSERKY
    and a0.corpersonky=BP.corpersonky
    and SA.arcidentityky = ID.arcidentityky
    and BK.arcprincipalky = ID.arcprincipalky
    order by a4.customerid"

    and the above query is generating an output close to 100,000 records and now i have a requirment which is to add another column in the above query called usercrsno and i am taking the value for the new column from the below query and this new column should be the 4th column of the main (above query)

    (select a.NBR as USERCRSNO from CorPhone a, SONEDBA.corperson b Where PhoneTypeEnum = 'FAX' and a.corpersonky=b.corpersonky),

    Now the problem is the new column query is generating only 54,000 records and when i am using it the main query it thows below error and i know it is a syntax issue and need to use outer join but now sure how to use it

    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES
    INTO statement is more than one row. SQLSTATE=21000

    It should work like this , when i execute the query it should output me all the 100,000 records with the values on the new column as well.

    Can some one help me please

    Thanks
    Dilip

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a only a guess based on your original code. Try it to see if it does what you want/expect.
    Code:
    db2 "SELECT a0.familyname, a0.givenname, id.USERID
    ,  CASE a1.iscmpsysadmin
          WHEN '1' THEN 'Administrator' 
          WHEN '0' THEN 'Non-Administrator' 
       END CASE 
    ,  CASE a1.isapprover
          WHEN '1' THEN 'Approver' 
          WHEN '0' THEN 'Non-Approver' 
       END CASE 
    ,  CASE a1.isactive
          WHEN '1' THEN 'Active' 
          WHEN '0' THEN 'Inactive' 
       END CASE, a4.customerid, a5.name
    ,  CASE a4.isenrolled
          WHEN '1' THEN 'Enabled'
          WHEN '0' THEN 'Dis-abled'
       END CASE, a6.billacc, a6.CUSTCRSNUMBER as SUCTCRSNO
    ,  a7.classofsvcname
    ,  CASE a6.fundsauth
          WHEN '1' THEN 'Funds Checking On' 
          WHEN '0' THEN 'Funds Checking Off'
       END CASE, a6.chargemod 
    ,  CASE a6.dualadmin
          WHEN '1' THEN 'Dual Admin' 
          WHEN '0' THEN 'Single Admin'
       END CASE, a4.corcustomerky, char(date(SA.updatedttm), EUR)
    ,  a6.challengequestion
    , (SELECT Min(a.NBR)
          FROM CorPhone a
          WHERE a.PhoneTypeEnum  = 'FAX'
            AND a.corpersonky    = ao.corpersonky) AS USERCRSNO
       FROM SONEDBA.corperson a0, SONEDBA.bnkemployee a1
    ,     SONEDBA.corpersonorg a2, SONEDBA.corcustomer a3
    ,     SONEDBA.bnkcustomer a4, SONEDBA.cororg a5
    ,     SONEDBA.wbccompanytd a6, SONEDBA.bnkclassofsvc a7
    ,     SONEDBA.ARCUSERAUTHNTICN SA, SONEDBA.ARCIDENTITY ID
    ,     SONEDBA.BNKUSER BK, SONEDBA.bnkperson BP
       WHERE  a0.corpersonky     = a2.corpersonky
          and a0.corpersonky     = a1.corpersonky
          AND a2.cororgky        = a3.cororgky
          AND a3.corcustomerky   = a4.corcustomerky
          AND a2.cororgky        = a5.cororgky
          AND a2.cororgky        = a6.objectid
          AND a4.bnkclassofsvcky = a7.bnkclassofsvcky
          AND BP.BNKUSERKY       = BK.BNKUSERKY
          AND a0.corpersonky     = BP.corpersonky
          AND SA.arcidentityky   = ID.arcidentityky
          AND BK.arcprincipalky  = ID.arcprincipalky
       ORDER BY a4.customerid"
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Dilip,

    Did you realised that case in "end case" were interpretid as column aliases?

    For example, in these case expression columns
    Code:
    ...
         , CASE a1.iscmpsysadmin
           WHEN '1' THEN 'Administrator'
           WHEN '0' THEN 'Non-Administrator'
           END  case
         , CASE a1.isapprover
           WHEN '1' THEN 'Approver'
           WHEN '0' THEN 'Non-Approver'
           END  case
    ...
    and in other 4 case expression columns.


    So, I thought that it might be better to specify more specific names, like...
    Code:
    ...
         , CASE a1.iscmpsysadmin
           WHEN '1' THEN 'Administrator'
           WHEN '0' THEN 'Non-Administrator'
           END  is_sysadmin
         , CASE a1.isapprover
           WHEN '1' THEN 'Approver'
           WHEN '0' THEN 'Non-Approver'
           END  "Approver?"
    ...
    Last edited by tonkuma; 03-18-14 at 11:16. Reason: Add more example... Capitalized words "then" in an example.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Along the lines of Tonkuma's advice, which fax number do you want when there are more than one for a particular corpersonky? Pat just gave you the smallest one, but that may not be what is really wanted. Also, he did it as an implicit LEFT OUTER JOIN, since you didn't know what kind of OUTER to perform. The Implict would have looked like:

    Code:
    LEFT OUTER JOIN CorPhone a
          ON a.PhoneTypeEnum  = 'FAX'
            AND a.corpersonky    = ao.corpersonky
            AND a.any_other_criteria_to get_a_single_row_back
    One other thing on the explicit join like this, you would not get the -811, you would just receive the same row with a different fax number, if you didn't get it down to a single row in the above.
    Dave

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    FWIW, the original subquery was "raw", not correlated. It would have returned EVERY fax number instead of just those for this client!

    Quote Originally Posted by tonkuma View Post
    So, I thought that it might be better to specify more specific names, like...
    Code:
    ...
         , CASE a1.iscmpsysadmin
           WHEN '1' THEN 'Administrator'
           WHEN '0' THEN 'Non-Administrator'
           END  is_sysadmin
         , CASE a1.isapprover
           WHEN '1' THEN 'Approver'
           WHEN '0' THEN 'Non-Approver'
           END  "Approver?"
    ...
    I guess that I assumed that the CASE was intended as a column alias to show that a CASE statement had been used instead of a foreign key to decode the data. If that was not the intent, then I would agree.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Mar 2014
    Posts
    2
    Hi Dilip,
    If I correctly understand your question you have a query originally returning 100k records. By adding another column this result should no change. Since your new column is a result of joining your original query with another tables (CorPhone and corperson) you should join these tables using columns from the original query. I don't see that in your query.
    When you receive the error of multiple result set, it is because you are suppose to retrieve only one record from your query, instead you are receiving more than one.
    Once you join your original query with the second one using any field, you have to make sure that the second query has unique values by the field you are joining. Some time, if this is not the case, and it does not matter to you, can force db2 to retrieve only one record--the first one found-- by adding the clause: "fetch first 1 row only". Usually you do that when the values are duplicate but the integrity is not compromised.
    So in your case your clause for your 4th column would say:
    (select a.NBR as USERCRSNO from CorPhone a, SONEDBA.corperson b Where PhoneTypeEnum = 'FAX' and a.corpersonky=b.corpersonky fetch first 1 row only).

    When doing reports it is better to split your complex query like this in small queries using non logged global temporary tables. Even having to write more code seem to be not efficient, it is a much better approach in db2, especially in multi-partitioned databases..
    If you are interested in this topic let me know. I can help you find more about this.
    Reynaldo Mola

Posting Permissions

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