| |
|
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.
|
 |
|
09-27-11, 08:37
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
How to match the year in Date field
|
|
Hi
Please find the below query,The query should return the matched and unmatched rows and need to matching the IN_DT column to PER_YEAR field having the less than or equal to 2011 and equal to 2012 and >=2013
Datatypes
CD_PLT char(6)
PRE_NAME char(6)
BASE_NAME char(6)
SUF_NAME char(6)
WKLY_CA integer
IN_DT Date 10bytes
MAX_IN_DT Date 10bytes not null default is 0001-01-01
PER_YEAR is not a column in the table,but the PER_YEAR should have the values and should display like 2011,2012 and 2013( that is Current year and two future year) for every row.
If the rows having IN_DT as less than or equal to 2011 means,that record should display as 2011 rows
If the rows having IN_DT as less equal to 2012 means,that record should display as 2012 rows
If the rows having IN_DT as greater than or equal to 2013 means,that record should display as 2013 rows
If the rows IN_DT column does not having value means the row should repeat for all three years like 2011,2012,2013(See the output of last row)
Code:
EXEC SQL
DECLARE SER_CRR CURSOR WITH RETURN FOR
SELECT DISTINCT
SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
FROM TABLE1 T1
LEFT OUTER JOIN
TABLE2 T2
ON
AND T2.CD_PLT = T1.CD_PLT
AND T2.BASE_NAME = T1.BASE_NAME
AND T2.PRE_NAME = T1.PRE_NAME
AND T2.SUF_NAME = T1.SUF_NAME
WHERE
T1.BASE_NAME = :WS-BASE'
AND T1.PRE_NAME= :WS-PRE
AND T1.SUF_NAME = :WS-SUF
END-EXEC.
Code:
Table1
CD_PLT BASE_NAME PRE_NAME SUF_NAME
----------- ------------ -------------- ----------
ABACC 6065 REWS AE
ABACC 6065 REWS AE
ABACC 6000 ERWV DE
BDDER R443 THYR RE
BDDER 4565 RTY RT
E1111 1111 TRW HJ
FRGET ADER TRR FR
FRGET ADER TRR FR
FRGET 7777 GHYU FF
HYYYY 8888 ADDD WE
TABLE2
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA IN_DT MAX_IN_DT
----------- ------------ -------------- ---------- ------- ---------------- ------------
ABACC 6065 REWS AE 21 2009-01-13 2011-12-19
ABACC 6065 REWS AE 21 2010-01-12 2011-12-19
ABACC 6000 ERWV DE 46 2010-11-11 2011-12-19
BDDER R443 THYR RE 57 2012-12-22 2011-12-19
BDDER 4565 RTY RT 27 2011-01-16 2011-12-19
BDDER 4565 RTY RT 27 2014-03-25 2011-12-19
FRGET ADER TRR FR 26 2011-08-13 2011-12-19
FRGET ADER TRR FR 25 2011-09-19 2011-12-19
FRGET ADER TRR FR 29 2015-02-22 2015-03-25
FRGET 7777 GHYU FF 61 2012-10-13 2013-12-19
FRGET 7777 GHYU FF 61 2012-11-14 2013-12-19
Code:
output should be
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA PER_YEAR IN_DT MAX_IN_DT
----------- ------------ -------------- ---------- ------------ ------- -------------------------- ------------
ABACC 6065 REWS AE 21 2011 2009-01-13 2011-12-19
ABACC 6065 REWS AE 21 2011 2009-01-12 2011-12-19
ABACC 6065 REWS AE - 2012 - -
ABACC 6065 REWS AE - 2013 - -
ABACC 6000 ERWV DE 46 2011 2010-11-11 2011-12-19
ABACC 6000 ERWV DE - 2012 - -
ABACC 6000 ERWV DE - 2013 - -
BDDER R443 THYR RE - 2011 - -
BDDER R443 THYR RE 57 2012 2012-12-22 2011-12-19
BDDER R443 THYR RE - 2013 - -
BDDER 4565 RTY RT 27 2011 2011-01-16 2011-12-19
BDDER 4565 RTY RT - 2012 - -
BDDER 4565 RTY RT 27 2013 2014-03-25 2014-03-25
FRGET ADER TRR FR 26 2011 2011-08-13 2011-12-19
FRGET ADER TRR FR 25 2011 2011-09-19 2011-12-19
FRGET ADER TRR FR - 2012 - -
FRGET ADER TRR FR 29 2013 2015-02-22 2015-03-25
FRGET 7777 GHYU FF 61 2011 - -
FRGET 7777 GHYU FF 61 2012 2012-10-13 2013-12-19
FRGET 7777 GHYU FF - 2012 2012-11-14 2013-12-19
FRGET 7777 GHYU FF - 2013 -
HYYYY 8888 ADDD WE - 2011 - -
HYYYY 8888 ADDD WE - 2012 - -
HYYYY 8888 ADDD WE - 2013 - -
Please help
|
|
09-27-11, 08:41
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
I'm sure the answer will include the YEAR() function and the CASE expression somewhere.
|
|
09-27-11, 11:51
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
I think there is (at least) an inconsistency in your sample.
You specified WHERE clause in your query, then BASE_NAME, PER_NAME so on in output should be restricted to one value.
But, you showed multiple values in your output.
Anyhow, here are (simplified) examples:
Note:
Replace group_col with T1.CD_PLT, T1.PRE_NAME, T1.BASE_NAME and T1.SUF_NAME,
and replace other_col with T2.WKLY_CA and T2.MAX_IN_DT.
Example 1: It is neccesary to see bare_output twice.
Code:
WITH
sample_bare_output(group_col , in_dt , other_col) AS (
SELECT group_col
, DATE(in_dt)
, other_col
FROM (VALUES
('A1' , '2009-01-13' , 21)
, ('A1' , '2009-01-12' , 21)
, ('A2' , '2010-11-11' , 46)
, ('B1' , '2012-12-22' , 57)
, ('B2' , '2011-01-16' , 27)
, ('B2' , '2014-03-25' , 27)
, ('F1' , '2011-08-13' , 26)
, ('F1' , '2011-09-19' , 25)
, ('F1' , '2015-02-22' , 29)
, ('F2' , '2012-10-13' , 61)
, ('F2' , '2012-11-14' , 61)
) s(group_col , in_dt , other_col)
)
SELECT
q.group_col
, s.other_col
, p.per_year
, s.in_dt
FROM (SELECT DISTINCT
group_col
FROM sample_bare_output
) q
CROSS JOIN
(VALUES ( YEAR(current_date) )
, ( YEAR(current_date) + 1 )
, ( YEAR(current_date) + 2 )
) p(per_year)
LEFT OUTER JOIN
(SELECT s.*
, YEAR(current_date) + 1
+ SIGN( YEAR(in_dt) - YEAR(current_date) - 1 ) norm_year
FROM sample_bare_output s
) s
ON s.group_col = q.group_col
AND s.norm_year = p.per_year
ORDER BY
q.group_col
, p.per_year
;
------------------------------------------------------------------------------
GROUP_COL OTHER_COL PER_YEAR IN_DT
--------- ----------- ----------- ----------
A1 21 2011 2009-01-13
A1 21 2011 2009-01-12
A1 - 2012 -
A1 - 2013 -
A2 46 2011 2010-11-11
A2 - 2012 -
A2 - 2013 -
B1 - 2011 -
B1 57 2012 2012-12-22
B1 - 2013 -
B2 27 2011 2011-01-16
B2 - 2012 -
B2 27 2013 2014-03-25
F1 26 2011 2011-08-13
F1 25 2011 2011-09-19
F1 - 2012 -
F1 29 2013 2015-02-22
F2 - 2011 -
F2 61 2012 2012-10-13
F2 61 2012 2012-11-14
F2 - 2013 -
21 record(s) selected.
Example 2: Reference to bare_output is once.
Code:
------------------------------ Commands Entered ------------------------------
WITH
sample_bare_output(group_col , in_dt , other_col) AS (
SELECT group_col
, DATE(in_dt)
, other_col
FROM (VALUES
('A1' , '2009-01-13' , 21)
, ('A1' , '2009-01-12' , 21)
, ('A2' , '2010-11-11' , 46)
, ('B1' , '2012-12-22' , 57)
, ('B2' , '2011-01-16' , 27)
, ('B2' , '2014-03-25' , 27)
, ('F1' , '2011-08-13' , 26)
, ('F1' , '2011-09-19' , 25)
, ('F1' , '2015-02-22' , 29)
, ('F2' , '2012-10-13' , 61)
, ('F2' , '2012-11-14' , 61)
) s(group_col , in_dt , other_col)
)
SELECT
group_col
, CASE norm_year
WHEN per_year THEN
other_col
END AS other_col
, per_year
, CASE norm_year
WHEN per_year THEN
in_dt
END AS in_dt
FROM (SELECT s.*
, ROW_NUMBER()
OVER( PARTITION BY group_col ) rn
, COUNT( NULLIF(
LAG(norm_year , 1 , 0)
OVER( PARTITION BY group_col
ORDER BY norm_year )
, norm_year
)
)
OVER( PARTITION BY group_col ) count_distinct
, MIN( norm_year )
OVER( PARTITION BY group_col ) min_year
, MAX( norm_year )
OVER( PARTITION BY group_col ) max_year
FROM (SELECT s.*
, YEAR(current_date) + 1
+ SIGN( YEAR(in_dt) - YEAR(current_date) - 1 ) norm_year
FROM sample_bare_output s
) s
) s
INNER JOIN
(VALUES ( YEAR(current_date) , 1)
, ( YEAR(current_date) + 1 , 2)
, ( YEAR(current_date) + 2 , 3)
) p(per_year , k)
ON per_year = norm_year
OR
( k = 1
AND per_year < min_year
OR k = 2
AND ( per_year < min_year
OR per_year > max_year
OR per_year > min_year AND per_year < max_year AND count_distinct = 2
)
OR k = 3
AND per_year > max_year
)
AND rn = 1
ORDER BY
group_col
, per_year
;
------------------------------------------------------------------------------
GROUP_COL OTHER_COL PER_YEAR IN_DT
--------- ----------- ----------- ----------
A1 21 2011 2009-01-13
A1 21 2011 2009-01-12
A1 - 2012 -
A1 - 2013 -
A2 46 2011 2010-11-11
A2 - 2012 -
A2 - 2013 -
B1 - 2011 -
B1 57 2012 2012-12-22
B1 - 2013 -
B2 27 2011 2011-01-16
B2 - 2012 -
B2 27 2013 2014-03-25
F1 26 2011 2011-08-13
F1 25 2011 2011-09-19
F1 - 2012 -
F1 29 2013 2015-02-22
F2 - 2011 -
F2 61 2012 2012-10-13
F2 61 2012 2012-11-14
F2 - 2013 -
21 record(s) selected.
|
Last edited by tonkuma; 09-27-11 at 23:34.
Reason: Reduce redundancy in ON clause in Example 2. Remove "ORDER BY norm_year" from "ROW_NUMBER() OVER(...)"
|
09-27-11, 12:55
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
If your DB2 version/release supports LISTAGG function.
Example 3:
Code:
------------------------------ Commands Entered ------------------------------
WITH
sample_bare_output(group_col , in_dt , other_col) AS (
SELECT group_col
, DATE(in_dt)
, other_col
FROM (VALUES
('A1' , '2009-01-13' , 21)
, ('A1' , '2009-01-12' , 21)
, ('A2' , '2010-11-11' , 46)
, ('B1' , '2012-12-22' , 57)
, ('B2' , '2011-01-16' , 27)
, ('B2' , '2014-03-25' , 27)
, ('F1' , '2011-08-13' , 26)
, ('F1' , '2011-09-19' , 25)
, ('F1' , '2015-02-22' , 29)
, ('F2' , '2012-10-13' , 61)
, ('F2' , '2012-11-14' , 61)
) s(group_col , in_dt , other_col)
)
SELECT
group_col
, CASE norm_year
WHEN per_year THEN
other_col
END AS other_col
, per_year
, CASE norm_year
WHEN per_year THEN
in_dt
END AS in_dt
FROM (SELECT s.*
, LISTAGG( CHAR(norm_year) )
OVER( PARTITION BY group_col ) norm_year_list
, ROW_NUMBER()
OVER( PARTITION BY group_col ) rn
FROM (SELECT s.*
, YEAR(current_date) + 1
+ SIGN( YEAR(in_dt) - YEAR(current_date) - 1 ) norm_year
FROM sample_bare_output s
) s
) s
INNER JOIN
(VALUES ( YEAR(current_date) )
, ( YEAR(current_date) + 1 )
, ( YEAR(current_date) + 2 )
) p(per_year)
ON per_year = norm_year
OR LOCATE( VARCHAR(per_year) , norm_year_list ) = 0
AND rn = 1
ORDER BY
group_col
, per_year
;
------------------------------------------------------------------------------
GROUP_COL OTHER_COL PER_YEAR IN_DT
--------- ----------- ----------- ----------
A1 21 2011 2009-01-13
A1 21 2011 2009-01-12
A1 - 2012 -
A1 - 2013 -
A2 46 2011 2010-11-11
A2 - 2012 -
A2 - 2013 -
B1 - 2011 -
B1 57 2012 2012-12-22
B1 - 2013 -
B2 27 2011 2011-01-16
B2 - 2012 -
B2 27 2013 2014-03-25
F1 26 2011 2011-08-13
F1 25 2011 2011-09-19
F1 - 2012 -
F1 29 2013 2015-02-22
F2 - 2011 -
F2 61 2012 2012-10-13
F2 61 2012 2012-11-14
F2 - 2013 -
21 record(s) selected.
|
Last edited by tonkuma; 09-27-11 at 13:06.
Reason: Remove VARCHAR from "VALUES ( VARCHAR(YEAR(current_date) ) ) , ..."
|
09-28-11, 06:33
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Hi
Thanks for the reply.I just misplet the code, here i pasted the correct code instead of below
WHERE
T1.BASE_NAME = :WS-BASE'
AND T1.PRE_NAME= :WS-PRE
AND T1.SUF_NAME = :WS-SUF
Code:
WHERE
T1.CD_PLT BETWEEN :WS-PLT-L1 AND WS-PLT-H1
AND
T1.PRE_NAME BETWEEN :WS-PRE-L1 AND WS-PRE-H1
AND
T1.BASE_NAME BETWEEN :WS-BASE-L1 AND WS-BASE-H1
AND
T1.SUF_NAME BETWEEN :WS-SUF-L1 AND WS-SUF-H1
We can give number of CD_PLT in the screen
Below code i just modified based on my code.
Code:
EXEC SQL
DECLARE SER_CRR CURSOR WITH RETURN FOR
WITH
sample_bare_output(
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
) AS (
SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,DATE(T2.IN_DT)
,T2.MAX_IN_DT
FROM (VALUES
('ABACC','RFWS','6065','AE','2009-01-13',21)
, ('ABACC','RFWS','6065','AE','2009-01-12',21)
, ('ABACC','ERWV','6000','DE','2010-11-11',46)
, ('BDDER','THYR','R443','RE','2012-12-22',57)
) s(
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
)
)
SELECT
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
,S.WKLY_CA
,P.PER_YEAR
,S.IN_DT
FROM (SELECT DISTINCT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
FROM sample_bare_output
) q
CROSS JOIN
(VALUES ( YEAR(current_date) )
, ( YEAR(current_date) + 1 )
, ( YEAR(current_date) + 2 )
) p(per_year)
LEFT OUTER JOIN
(SELECT s.*
, YEAR(current_date) + 1
+ SIGN( YEAR(T2.IN_DT) - YEAR(current_date) - 1 ) norm_year
FROM sample_bare_output s
) s
on S.CD_PLT = Q.CD_PLT
AND S.BASE_NAME = Q.BASE_NAME
AND S.PRE_NAME = Q.PRE_NAME
AND S.SUF_NAME = Q.SUF_NAME
AND s.norm_year = p.per_year
ORDER BY
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
, p.per_year
;
Please let me know whether the above code is correct one or need to add some of the additional commands.
Here i didnot mentioned the table name and i dont know how to use and where can i do modify in the actual code..
This is Stored procedure and the result set return to Front end screen(User has to enter the all CD_PLT and BASE_NAME,PRE_NAME,SUF_NAME values)
we need to fetch the related rows fro the table and display in the screen.
PER_YEAR is not a column in the Table...It is used for display the year value in the screen,but we need to return the year value along with the query results to the screen
More over the FROM VALUES( ) should not hard coded...because
T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
are editable field and the user can change the value
Please let me know,if any
|
|
09-28-11, 09:00
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
Please let me know whether the above code is correct one or need to add some of the additional commands.
|
You can test the query by yourself on your environment.
I reviews your op again and realized...
a) You may want to replace sample_bare_output in my example with your TABLE2.
I thought that the example titled "output should be" in your op can be produced from TABLE2 only.
Briefly looking into your last code...
b) the qualifiers in there must be syntax error.
Code:
sample_bare_output(
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
) AS (
Code:
FROM (VALUES
...
) s(
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
)
c) Number of items in values list is not same as number of column names.
d) If you are using DB2 on z/OS, some syntax need to be changed.
For example:
d-1) "(VALUES ... )" should be replaced by "SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".
d-2) "CROSS JOIN" should be replaced by ","(traditional join syntax).
|
Last edited by tonkuma; 09-28-11 at 09:26.
Reason: Replace all note "a)".
|
09-28-11, 09:36
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Hi;
Thanx for the quick reply
Quote:
You can test the query by yourself on your environment.
|
I have pasted the whole query in the previous post.I dont konow ,WITH SAMPLE_BARE_OUTPUT will be after declare cursor statement
Quote:
a) It is unclear nested level of subselects.
In other word, it is unclear which subselect is subquery of which subselect.
|
i have just modified the with original code whatever you have given in the post
Quote:
b) the qualifiers in there must be syntax error.
|
I used qualifiers T1 for the Table1 and T2 for the Table2,Please let me know whether i have to use 'S' as qualifier instead T1 and T2
Quote:
c) Number of items in values list is not same as number of column names.
|
yes,apology for that,,I missed one column(T2.MAX_IN_DT) in the post
Quote:
d) If you are using DB2 on z/OS, some syntax need to be changed.
For example:
d-1) "(VALUES ... )" should be replaced by "SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".
d-2) "CROSS JOIN" should be replaced by ","(traditional join syntax).
|
we are using Version 9 Rel 1
Please let me know ,where to use "SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".
Please help me and need some clarity on that... 
|
|
09-29-11, 07:00
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Hi;
Here I have pasted the modified code as per your direction,Please check and post the correct one..Please
Code:
EXEC SQL
DECLARE SER_CRR CURSOR WITH RETURN FOR
SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
AS (
SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,DATE(T2.IN_DT)
,T2.MAX_IN_DT
FROM (VALUES(SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
FROM TABLE1 T1
LEFT OUTER JOIN
TABLE2 T2
ON
AND T2.CD_PLT = T1.CD_PLT
AND T2.BASE_NAME = T1.BASE_NAME
AND T2.PRE_NAME = T1.PRE_NAME
AND T2.SUF_NAME = T1.SUF_NAME
WHERE
T1.CD_PLT BETWEEN :WS-PLT-L1 AND WS-PLT-H1
AND
T1.PRE_NAME BETWEEN :WS-PRE-L1 AND WS-PRE-H1
AND
T1.BASE_NAME BETWEEN :WS-BASE-L1 AND WS-BASE-H1
AND
T1.SUF_NAME BETWEEN :WS-SUF-L1 AND WS-SUF-H1)
) s(
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
)
)
SELECT
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
,S.WKLY_CA
,P.PER_YEAR
,S.IN_DT
FROM (SELECT DISTINCT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
FROM
) q
INNER JOIN
(VALUES ( YEAR(current_date) )
, ( YEAR(current_date) + 1 )
, ( YEAR(current_date) + 2 )
) p(per_year)
LEFT OUTER JOIN
(SELECT s.*
, YEAR(current_date) + 1
+ SIGN( YEAR(T2.IN_DT) - YEAR(current_date) - 1 ) norm_year
FROM s
) s
on S.CD_PLT = Q.CD_PLT
AND S.BASE_NAME = Q.BASE_NAME
AND S.PRE_NAME = Q.PRE_NAME
AND S.SUF_NAME = Q.SUF_NAME
AND s.norm_year = p.per_year
ORDER BY
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
, p.per_year
The select statement also repeated multi times..
Please help me for regarding that... 
|
|
09-29-11, 15:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please see the Syntax (and Description) in manuals step by step
and construct your query conforming to syntax.
(1) DECLARE CURSOR
DB2 9 - DB2 SQL - DECLARE CURSOR
Code:
>>-DECLARE--cursor-name--+----------------------------------+--->
| .-ASENSITIVE------------. |
'-+-----------------------+-SCROLL-'
+-INSENSITIVE-----------+
| .-DYNAMIC-. |
'-SENSITIVE-+---------+-'
'-STATIC--'
.-----------------------------.
V (1) |
>--CURSOR---------+--------------------+-+---------------------->
+-holdability--------+
+-returnability------+
'-rowset-positioning-'
>--FOR--+-select-statement-+-----------------------------------><
'-statement-name---'
You should write select-statement in CURSOR, if not use dynamicaly prepared statement.
(2) select-statement
DB2 9 - DB2 SQL - select-statement
Code:
>>-+-----------------------------------+--fullselect------------>
| .-,-----------------------. |
| V | |
'-WITH----common-table-expression-+-'
.--------------------------.
V | (2)
>----+----------------------+-+--------------------------------><
+-update-clause--------+
| (1) |
+-read-only-clause-----+
+-optimize-clause------+
+-isolation-clause-----+
+-queryno-clause-------+
'-SKIP LOCKED DATA-----'
select-statement is
(optional) WITH common-table-expression and fullselect ...
(3) fullselect
DB2 9 - DB2 SQL - fullselect
Code:
>>-+-subselect----+--------------------------------------------->
'-(fullselect)-'
.---------------------------------------------------.
V |
>----+-----------------------------------------------+-+-------->
| .-DISTINCT-. |
'-+-UNION-----+--+----------+--+-subselect----+-'
+-EXCEPT----+ '-ALL------' '-(fullselect)-'
'-INTERSECT-'
>--+-----------------+--+--------------------+-----------------><
'-order-by-clause-' '-fetch-first-clause-'
You are not using UNION/EXCEPT/INTERSECT.
So, you should write subselect in your cursor.
(4) subselect
DB2 9 - DB2 SQL - subselect
Code:
>>-select-clause--from-clause--+--------------+----------------->
'-where-clause-'
>--+-----------------+--+---------------+----------------------->
'-group-by-clause-' '-having-clause-'
>--+-----------------+--+--------------------+-----------------><
'-order-by-clause-' '-fetch-first-clause-'
First clause is a select-clause
(5) select-clause
DB2 9 - DB2 SQL - select-clause
Code:
.-ALL------.
>>-SELECT--+----------+----------------------------------------->
'-DISTINCT-'
>--+-*----------------------------------------------+----------><
| .-,------------------------------------------. |
| V | |
'---+-expression-+-------------------------+-+-+-'
| | .-AS-. | |
| '-+----+--new-column-name-' |
'-+-table-name-------+-.*----------------'
+-view-name--------+
'-correlation-name-'
Repeat expression [AS new-column-name] after SELECT keyword.
You will find a syntax error, now.
Because, no parenthesis after AS according to syntax,
while you wrote "(SELECT" after ",T2.MAX_IN_DT AS"
Code:
DECLARE SER_CRR CURSOR WITH RETURN FOR
SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
AS (
SELECT
T1.CD_PLT
...
...
You might realized in step(2) that "WITH sample_bare_output (...) AS (" in my code was a common-table-expression.
And, you might also find a syntax error(that is qualify column names in parentheses), like
"(T1.CD_PLT ,T1.PRE_NAME ..." in your code in a post of two days ago,
according to the syntax of common-table-expression.
Code:
EXEC SQL
DECLARE SER_CRR CURSOR WITH RETURN FOR
WITH
sample_bare_output(
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
) AS (
...
(6) common-table-expression
DB2 9 - DB2 SQL - common-table-expression
Code:
>>-table-identifier--+---------------------------+--AS---------->
| .-,---------------. |
| V | |
'-(----+-------------+-+--)-'
'-column-name-'
>--(fullselect)------------------------------------------------><
|
Last edited by tonkuma; 09-29-11 at 17:47.
|
09-29-11, 17:41
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Briefly looking into your code,
you might go right direction.
But, I felt that you might be not understand the syntax of common-table-expression,
then you wrote unnecessary (repeated) select and unnecesary (out of syntax) qualifiers, so on.
Another issue (apart from syntax) might be in join condition of table1 and table2.
There are duplicated rows in table1 and duplicated matching rows in table2.
For example:
Code:
Table1
CD_PLT BASE_NAME PRE_NAME SUF_NAME
----------- ------------ -------------- ----------
ABACC 6065 REWS AE
ABACC 6065 REWS AE
and
Code:
TABLE2
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA IN_DT MAX_IN_DT
----------- ------------ -------------- ---------- ------- ---------------- ------------
ABACC 6065 REWS AE 21 2009-01-13 2011-12-19
ABACC 6065 REWS AE 21 2010-01-12 2011-12-19
If only join conditions were
Code:
ON
T2.CD_PLT = T1.CD_PLT
AND T2.BASE_NAME = T1.BASE_NAME
AND T2.PRE_NAME = T1.PRE_NAME
AND T2.SUF_NAME = T1.SUF_NAME
then matching combinations would be 4(result rows of the join were 4), like
Code:
table1 |table2 |
-------+-------+
(row1) |(row1) |
(row1) |(row2) |
(row2) |(row1) |
(row2) |(row2) |
|
|
09-30-11, 07:03
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Hi;
Please find the code below which i have modified as per recent updates
Code:
WITH
sample_bare_output(
CD_PLT
,PRE_NAME
,BASE_NAME
,SUF_NAME
,WKLY_CA
,IN_DT
,MAX_IN_DT
) AS (
SELECT
CD_PLT
,PRE_NAME
,BASE_NAME
,SUF_NAME
,WKLY_CA
,DATE(IN_DT)
,MAX_IN_DT
FROM(
SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
FROM TABLE1 T1
LEFT OUTER JOIN
TABLE2 T2
ON
T2.CD_PLT = T1.CD_PLT
AND T2.BASE_NAME = T1.BASE_NAME
AND T2.PRE_NAME = T1.PRE_NAME
AND T2.SUF_NAME = T1.SUF_NAME
)
) s(
CD_PLT
,PRE_NAME
,BASE_NAME
,SUF_NAME
,WKLY_CA
,IN_DT
,MAX_IN_DT
)
)
SELECT
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
,S.WKLY_CA
,P.PER_YEAR
,S.IN_DT
FROM (SELECT DISTINCT
CD_PLT
,PRE_NAME
,BASE_NAME
,SUF_NAME
FROM sample_bare_output
) q
INNER JOIN
(VALUES ( YEAR(current_date) )
, ( YEAR(current_date) + 1 )
, ( YEAR(current_date) + 2 )
) p(per_year)
LEFT OUTER JOIN
(SELECT s.*
, YEAR(current_date) + 1
+ SIGN( YEAR(T160A.DT_EFF_IN) - YEAR(current_date) - 1 ) norm_year
FROM sample_bare_output s
) s
on S.CD_PLT = Q.CD_PLT
AND S.BASE_NAME = Q.BASE_NAME
AND S.PRE_NAME = Q.PRE_NAME
AND S.SUF_NAME = Q.SUF_NAME
AND s.norm_year = p.per_year
ORDER BY
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
, p.per_year
;
I have compiled that code in the IBM Command Editor tool Version DB2v8.1.9.917 and got the error message like below
"SQL0104N An unexpected token "<EMPTY>" was found following "". Expected
tokens may include: "CORRELATION NAME". SQLSTATE=42601
SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME".
sqlcode : -104 "
Why we are using the LEFT OUTER JOIN is We should return the unmatched rows also from the Table1 in the result set..
If the unmatched rows return in the result set means..the Result set will be like below along with all matched rows in the first day post
Code:
XBSDF 6091 GHJH AE - 2011 - -
XBSDF 6091 GHJH AE - 2012 - -
XBSDF 6091 GHJH AE - 2013 - -
Please giude me,where is the erorr..and let me know where to use SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".
THANKS IN ADVANCE
|
|
09-30-11, 07:46
|
|
Registered User
|
|
Join Date: Apr 2006
Location: Belgium
Posts: 1,159
|
|
"Please giude me,where is the erorr..and let me know where to use SELECT"
some people really think this forum is the nirvana to all problems. they don't even check what they are executing just using cut/paste from what was proposed..
at least I would like to understand what I am doing and being able to re-use this in the future..
__________________
Best Regards, Guy Przytula
Database Software Consultant
DB2 UDB LUW Certified V7-V8-V9-V9.7 DB Admin - Dprop..
Information Server Datastage Certified
http://www.infocura.be
|
|
09-30-11, 09:19
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Hi;
I just modified the previous post code and compiled
Code:
WITH
sample_bare_output(CD_PLT,PRE_NAME,BASE_NAME,SUF_NAME,WKLY_CA,IN_DT,MAX_IN_DT) AS (
SELECT
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
FROM TABLE1 T1
LEFT OUTER JOIN
TABLE2 T2
ON
T2.CD_PLT = T1.CD_PLT
AND T2.BASE_NAME = T1.BASE_NAME
AND T2.PRE_NAME = T1.PRE_NAME
AND T2.SUF_NAME = T1.SUF_NAME
),
s(CD_PLT,PRE_NAME,BASE_NAME,SUF_NAME,WKLY_CA,IN_DT,MAX_IN_DT) as (
SELECT
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
,S.WKLY_CA
,P.PER_YEAR
,S.IN_DT
FROM (SELECT DISTINCT
CD_PLT
,PRE_NAME
,BASE_NAME
,SUF_NAME
FROM sample_bare_output
) q
INNER JOIN
(VALUES ( YEAR(current_date) )
, ( YEAR(current_date) + 1 )
, ( YEAR(current_date) + 2 )
) p(per_year)
LEFT OUTER JOIN
(SELECT s.*
, YEAR(current_date) + 1
+ SIGN( YEAR(T160A.DT_EFF_IN) - YEAR(current_date) - 1 )
norm_year
FROM sample_bare_output s
) s
on S.CD_PLT = Q.CD_PLT
AND S.BASE_NAME = Q.BASE_NAME
AND S.PRE_NAME = Q.PRE_NAME
AND S.SUF_NAME = Q.SUF_NAME
AND s.norm_year = p.per_year
ORDER BY
Q.CD_PLT
,Q.PRE_NAME
,Q.BASE_NAME
,Q.SUF_NAME
, p.per_year
;
got the error like "SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", )" "
But where ever i used the " ) " in that code means it will produce the same error..
Thnks in advance..
|
|
09-30-11, 09:38
|
|
Registered User
|
|
Join Date: Apr 2006
Location: Belgium
Posts: 1,159
|
|
but is s(CD_PLT,....... is this correct syntax ??
some pd/psi might help...
__________________
Best Regards, Guy Przytula
Database Software Consultant
DB2 UDB LUW Certified V7-V8-V9-V9.7 DB Admin - Dprop..
Information Server Datastage Certified
http://www.infocura.be
|
|
10-03-11, 09:23
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
|
| 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
>
How to match the year in Date field
