Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Query Question

  1. #1
    Join Date
    Dec 2011
    Posts
    9

    Unanswered: Query Question

    Ii have the following query, which is creating a crystal report, pulling info from a DB2 database. The final statement is telling the statement that it only wants to pull records with any activity within the given parameters (start date and end date). However, if the account has no activity that month, I would like it to list the account name, and just 0's across the board. Any suggestions?

    SELECT
    'PE' AS "BASE_JURISDICTION",
    "C"."IFTANUM",
    (SELECT "P"."CODE" FROM "IFTA"."PROVINCE" "P" WHERE UPPER("P"."NAME") = UPPER("J"."NAME") ),
    (SELECT "P"."COUNTRY" FROM "IFTA"."PROVINCE" "P" WHERE UPPER("P"."NAME") = UPPER("J"."NAME") ),
    "J"."NAME" AS "TRAV_JURISDICTION",
    "JR"."TOTAL_KMS" AS "TOTAL_DISTANCE",
    "JR"."TAXABLE_KMS" AS "TAXABLE_DISTANCE",
    "JR"."TAX_PAID_LITRES_PURCHASED" AS "TAX_PAID_FUEL",
    "QFR"."KILO_PER_LITRE",
    "JTR"."TAX_RATE",
    "JR"."SYS_CALCULATED_INTEREST_DUE",
    SUBSTR("TA"."TRANS_REF_NUM",3,4) as "TRANS_REF_NUM",
    '' AS NULL_VALUE




    FROM "IFTA"."CARRIERS" "C"
    JOIN "IFTA"."IFTA_ACCT" "IA" ON "C"."CARRIERID" ="IA"."CARRIER_ID"
    JOIN "IFTA"."QUARTERLY_FUEL_RETURN" "QFR" ON "IA"."ID" = "QFR"."IFTA_ACCT_ID"
    JOIN "IFTA"."JURISDICTION_RETURN" "JR" ON "QFR"."ID"="JR"."QUARTERLY_FUEL_RETURN_ID"
    JOIN "IFTA"."JURISDICTION_TAX_RATE" "JTR" ON "JR"."TAX_RATE_ID" = "JTR"."ID"
    JOIN "IFTA"."JURISDICTION" "J" ON "JTR"."JURISDICTION_ID"= "J"."ID"
    JOIN "IFTA"."CARRIER_TRANSACTION_ACCT" "TA" ON "C"."CARRIERID" ="TA"."CARRIER_ID"


    WHERE
    "QFR"."STATUS" = 'Y'

    AND COLEACE("QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... if the account has no activity that month, I would like it to list the account name, and just 0's across the board.
    What are the column names of "month" and "account name"?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    AND COLEACE("QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}
    COLEACE?

    Left parenthesis has no corresponding right parenthesis.

  4. #4
    Join Date
    Dec 2011
    Posts
    9
    Sorry...that was something I was just trying.....the last statement actually looks like this at the moment...

    WHERE
    "QFR"."STATUS" = 'Y'

    AND "QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}

    And to answer your question about the month...it's actually a formula within crystal to pull the information the user puts into the parameter fields....
    TOTEXT({?Start_Date},"MMM-dd") + ' - ' + TOTEXT({?End_Date},"MMM-dd")
    Last edited by shamrock31; 12-13-11 at 11:36.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Keep in mind, I do not know your table structures or data, and I am not trying to correct the errors in your SQL, just giving you and idea of how to proceed. You need to make use of outer joins and you can easily use the VALUE or COALESCE clause to get your default value.
    Use code tags around your sql in the future to keep it formatted.

    Code:
    SELECT 'PE' AS BASE_JURISDICTION
          , C.IFTANUM
          ,(SELECT P.CODE FROM IFTA.PROVINCE P
            WHERE UPPER(P.NAME) = UPPER(J.NAME))
          ,(SELECT P.COUNTRY FROM IFTA.PROVINCE P
            WHERE UPPER(P.NAME) = UPPER(J.NAME) )
          , vALUE(J.NAME,'0')                         AS TRAV_JURISDICTION
          , vALUE(JR.TOTAL_KMS,'0')                   AS TOTAL_DISTANCE
          , vALUE(JR.TAXABLE_KMS,'0')                 AS TAXABLE_DISTANCE
          , vALUE(JR.TAX_PAID_LITRES_PURCHASED,'0')   AS TAX_PAID_FUEL
          , vALUE(QFR.KILO_PER_LITRE,'0')             AS KILO_PER_LITRE
          , vALUE(JTR.TAX_RATE,'0')                   AS TAX_RATE
          , vALUE(JR.SYS_CALCULATED_INTEREST_DUE,'0') AS SYS_CALCULATED_INTEREST_DUE
          , vALUE(SUBSTR(TA.TRANS_REF_NUM,3,4)        as TRANS_REF_NUM
          , '' AS NULL_VALUE
    
    
    FROM IFTA.CARRIERS C 
    JOIN IFTA.IFTA_ACCT IA
       ON C.CARRIERID =IA.CARRIER_ID 
    LEFT OUTER JOIN IFTA.QUARTERLY_FUEL_RETURN QFR
       ON IA.ID = QFR.IFTA_ACCT_ID 
      AND "QFR"."STATUS" = 'Y'
    LEFT OUTER JOIN IFTA.JURISDICTION_RETURN JR 
       ON QFR.ID=JR.QUARTERLY_FUEL_RETURN_ID 
    LEFT OUTER JOIN IFTA.JURISDICTION_TAX_RATE JTR
       ON JR.TAX_RATE_ID = JTR.ID 
    LEFT OUTER JOIN IFTA.JURISDICTION J
       ON JTR.JURISDICTION_ID= J.ID
    LEFT OUTER JOIN IFTA.CARRIER_TRANSACTION_ACCT TA
       ON C.CARRIERID =TA.CARRIER_ID
    WHERE 
    COLEACE("QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}

  6. #6
    Join Date
    Dec 2011
    Posts
    9
    No worries, thanks for the input.. the sql is not mine, it was just a project that was kind of dumped into my lap after a guy was let go here at work...Ii agree, it's a mess.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Keep in mind, I do not know your table structures or data, and I am not trying to correct the errors in your SQL, just giving you and idea of how to proceed. You need to make use of outer joins and you can easily use the VALUE or COALESCE clause to get your default value.
    Use code tags around your sql in the future to keep it formatted.

    Code:
    SELECT 'PE' AS BASE_JURISDICTION
          , C.IFTANUM
          ,(SELECT P.CODE FROM IFTA.PROVINCE P
            WHERE UPPER(P.NAME) = UPPER(J.NAME))
          ,(SELECT P.COUNTRY FROM IFTA.PROVINCE P
            WHERE UPPER(P.NAME) = UPPER(J.NAME) )
          , vALUE(J.NAME,'0')                         AS TRAV_JURISDICTION
          , vALUE(JR.TOTAL_KMS,'0')                   AS TOTAL_DISTANCE
          , vALUE(JR.TAXABLE_KMS,'0')                 AS TAXABLE_DISTANCE
          , vALUE(JR.TAX_PAID_LITRES_PURCHASED,'0')   AS TAX_PAID_FUEL
          , vALUE(QFR.KILO_PER_LITRE,'0')             AS KILO_PER_LITRE
          , vALUE(JTR.TAX_RATE,'0')                   AS TAX_RATE
          , vALUE(JR.SYS_CALCULATED_INTEREST_DUE,'0') AS SYS_CALCULATED_INTEREST_DUE
          , vALUE(SUBSTR(TA.TRANS_REF_NUM,3,4)        as TRANS_REF_NUM
          , '' AS NULL_VALUE
    
    
    FROM IFTA.CARRIERS C 
    JOIN IFTA.IFTA_ACCT IA
       ON C.CARRIERID =IA.CARRIER_ID 
    LEFT OUTER JOIN IFTA.QUARTERLY_FUEL_RETURN QFR
       ON IA.ID = QFR.IFTA_ACCT_ID 
      AND "QFR"."STATUS" = 'Y'
    LEFT OUTER JOIN IFTA.JURISDICTION_RETURN JR 
       ON QFR.ID=JR.QUARTERLY_FUEL_RETURN_ID 
    LEFT OUTER JOIN IFTA.JURISDICTION_TAX_RATE JTR
       ON JR.TAX_RATE_ID = JTR.ID 
    LEFT OUTER JOIN IFTA.JURISDICTION J
       ON JTR.JURISDICTION_ID= J.ID
    LEFT OUTER JOIN IFTA.CARRIER_TRANSACTION_ACCT TA
       ON C.CARRIERID =TA.CARRIER_ID
    WHERE 
    COLEACE("QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}

  8. #8
    Join Date
    Dec 2011
    Posts
    9
    Quote Originally Posted by dav1mo View Post
    Keep in mind, I do not know your table structures or data, and I am not trying to correct the errors in your SQL, just giving you and idea of how to proceed. You need to make use of outer joins and you can easily use the VALUE or COALESCE clause to get your default value.
    Use code tags around your sql in the future to keep it formatted.

    Code:
    SELECT 'PE' AS BASE_JURISDICTION
          , C.IFTANUM
          ,(SELECT P.CODE FROM IFTA.PROVINCE P
            WHERE UPPER(P.NAME) = UPPER(J.NAME))
          ,(SELECT P.COUNTRY FROM IFTA.PROVINCE P
            WHERE UPPER(P.NAME) = UPPER(J.NAME) )
          , vALUE(J.NAME,'0')                         AS TRAV_JURISDICTION
          , vALUE(JR.TOTAL_KMS,'0')                   AS TOTAL_DISTANCE
          , vALUE(JR.TAXABLE_KMS,'0')                 AS TAXABLE_DISTANCE
          , vALUE(JR.TAX_PAID_LITRES_PURCHASED,'0')   AS TAX_PAID_FUEL
          , vALUE(QFR.KILO_PER_LITRE,'0')             AS KILO_PER_LITRE
          , vALUE(JTR.TAX_RATE,'0')                   AS TAX_RATE
          , vALUE(JR.SYS_CALCULATED_INTEREST_DUE,'0') AS SYS_CALCULATED_INTEREST_DUE
          , vALUE(SUBSTR(TA.TRANS_REF_NUM,3,4)        as TRANS_REF_NUM
          , '' AS NULL_VALUE
    
    
    FROM IFTA.CARRIERS C 
    JOIN IFTA.IFTA_ACCT IA
       ON C.CARRIERID =IA.CARRIER_ID 
    LEFT OUTER JOIN IFTA.QUARTERLY_FUEL_RETURN QFR
       ON IA.ID = QFR.IFTA_ACCT_ID 
      AND "QFR"."STATUS" = 'Y'
    LEFT OUTER JOIN IFTA.JURISDICTION_RETURN JR 
       ON QFR.ID=JR.QUARTERLY_FUEL_RETURN_ID 
    LEFT OUTER JOIN IFTA.JURISDICTION_TAX_RATE JTR
       ON JR.TAX_RATE_ID = JTR.ID 
    LEFT OUTER JOIN IFTA.JURISDICTION J
       ON JTR.JURISDICTION_ID= J.ID
    LEFT OUTER JOIN IFTA.CARRIER_TRANSACTION_ACCT TA
       ON C.CARRIERID =TA.CARRIER_ID
    WHERE 
    COLEACE("QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}

    Isn't this the exact same posted you posted earlier??

  9. #9
    Join Date
    Dec 2011
    Posts
    9
    I'm thinking an IF THEN ELSE statement may be my only choice..

    Not proper syntax, just writing off the top of my head..

    Code:
    IF ("QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}) is NULL 
    THEN 0
    ELSE 
    "QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by shamrock31 View Post
    I'm thinking an IF THEN ELSE statement may be my only choice..
    Only there's no such statement. Look at the CASE expression in the manual.

  11. #11
    Join Date
    Dec 2011
    Posts
    9
    Sorry...new to the DB2 world, totally new to it. Come from an oracle background

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    What are the column names of "month" and "account name"?
    "account name":
    I want to ask again...
    What is the column name of "account name"?

    If I knew enough about the roles of each tables and relationships of tables,
    it must be great help to answer your question.
    Although table names/column names may suggest something,
    I'm not so well to guess the roles of each tables and relationships of tables
    based on names of tables/columns and real world facts/relations of them.

    IF the account has no activity ..., ... list the account name, and just 0's ...
    This suggested me (very simplified) like...
    (table in which "account name" belongs) LEFT OUTER JOIN (other tables) ON ...


    "month":
    Although you wrote "that month",
    I couldn't see a column name nor a expression nor a predicate which suggests month.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}
    Although this suggests the query is for a period,
    I couldn't find something related to month in the query.

    Some guesses:
    - The period may be part of a month.
    - The period may span some month.
    - If the period may span some month,
    did the query requested one result row for a combination of each month and each account name?
    OR one result row for each account name?
    (anyhow what is "account name"?)
    - and more ...
    Last edited by tonkuma; 12-13-11 at 16:13.

  14. #14
    Join Date
    Dec 2011
    Posts
    9
    Quote Originally Posted by tonkuma View Post
    Although this suggests the query is for a period,
    I couldn't find something related to month in the query.

    Some guesses:
    - The period may be part of a month.
    - The period may span some month.
    - If the period may span some month,
    did the query requested one result row for a combination of each month and each account name?
    OR one result row for each account name?
    (anyhow what is "account name"?)
    - and more ...
    The date in question is a range that the user choses (ex. April 1 to April 30)
    The account name is CODE.
    Query returns 57 rows, all 'CODE's' that have a return received date between April 1 and April 30.
    Query is missing 3 rows, which are "CODE's" that did not have any return received date between April 1 and April 30

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TOTEXT({?Start_Date},"MMM-dd") + ' - ' + TOTEXT({?End_Date},"MMM-dd")
    How did it relate with the query?
    I don't konw and don't interest in anything outside the query.
    (I think that DB2 also think same ways.)

    I hope you to clarify the roles of crystal report and the roles of DB2(the query) in your requirements.

Posting Permissions

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