| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
12-13-11, 08:28
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 9
|
|
|
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}
|
|
12-13-11, 09:52
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
... 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"?
|
|
12-13-11, 09:55
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
Quote:
|
AND COLEACE("QFR"."RETURN_RECEIVED_DATE" BETWEEN {?Start_Date} AND {?End_Date}
|
COLEACE?
Left parenthesis has no corresponding right parenthesis.
|
|
12-13-11, 10:14
|
|
Registered User
|
|
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 10:36.
|
12-13-11, 10:59
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
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}
|
|
12-13-11, 11:34
|
|
Registered User
|
|
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.
|
|
12-13-11, 13:14
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
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}
|
|
12-13-11, 13:17
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 9
|
|
Quote:
Originally Posted by dav1mo
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??
|
|
12-13-11, 13:31
|
|
Registered User
|
|
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}
|
|
12-13-11, 14:33
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
Originally Posted by shamrock31
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.
|
|
12-13-11, 14:36
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 9
|
|
Sorry...new to the DB2 world, totally new to it. Come from an oracle background
|
|
12-13-11, 14:56
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
Originally Posted by tonkuma
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.
Quote:
|
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.
|
|
12-13-11, 15:09
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
"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 15:13.
|
12-13-11, 15:19
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 9
|
|
Quote:
Originally Posted by tonkuma
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
|
|
12-13-11, 15:27
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Query Question
