| View previous topic :: View next topic |
| Author |
Message |
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
 Posted: Wed Aug 24, 2011 9:19 am Post subject: How to modify the query |
 |
|
HI
Please see the below query,I have passed the current year and plus two year to the working variables and fetched the CALEN_YEAR
| Code: |
SELECT DISTINCT
T24.CALEN_YEAR
,T24.IN_LOC
,T24.PART_NO
,T24.PART_DES
,T16.EFF_DT
FROM PROC_TABLE T24
LEFT OUTER JOIN
DEV_TABLE T16
ON T16.IN_LOC = T24.IN_LOC
AND T16.PART_NO = T24.PART_NO
AND T16.PART_DES= T24.PART_DES
WHERE
T24.PART_NO =' 11'
AND T24.IN_LOC ='XXX'
AND T24.PART_DES =' EAST'
AND T24.CALEN_YEAR IN (:WS-CURR-YR,:WS-NXT-YR,:WS-NXT-NXT-YR)
ORDER BY
T24.IN_LOC
,T24.PART_NO
,T24.PART_DES
,T16.EFF_DT
DESC
|
THE QUERY RETURNS
| Code: |
CALEN_YEAR IN_LOC PART_NO PART_DES EFF_DT
2011 XXX 11 EAST 28-02-2011
2012 XXX 11 EAST 28-02-2011
2013 XXX 11 EAST 28-02-2011
|
The year 2012 and 2013 should not have the same records if the year does not have prescribed records
But i need the query should return like the below values
| Code: |
CALEN_YEAR IN_LOC PART_NO PART_DES EFF_DT
2011 XXX 11 EAST 28-02-2011
2012
2013
|
All columns are CHAR type (EFF_DT_IN is a Date type),
Please let me know,how to modify the query ?
Thanks |
|
| Back to top |
|
 |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Aug 24, 2011 9:30 am Post subject: |
|
|
At a quick glance, it seems T24.CALEN_YEAR should be part of your join.
Maybe year = year portion of EFF_DT?
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Aug 24, 2011 10:30 am Post subject: |
|
|
shyamsaravan,
 How many topics are you going to open on the same question. This is the 3rd topic on the same issue. Your earlier topics on the same issue
How to get the records of current year and plus two year
Query changes
You never provide any details. If you continue doing this you will not be able to post any more on this board.
Thanks
kolusu |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Wed Sep 14, 2011 7:56 am Post subject: |
|
|
Hi;
Please find below is the existing query running in the system,it returns the result to front end screen
| Code: |
SELECT DISTINCT
,T24.IN_LOC
,T24.PART_NO
,T24.CALEN_YEAR
,T16.EFF_DT
FROM PROC_TABLE T24
LEFT OUTER JOIN
DEV_TABLE T16
ON T16.IN_LOC = T24.IN_LOC
AND T16.PART_NO = T24.PART_NO
AND T24.CALEN_YEAR =
LEFT(CHAR(YEAR(T16.EFF_DT)),4)
WHERE
T24.PART_NO ='RDS7FG'
AND T24.IN_LOC ='0131W'
AND T24.CALEN_YEAR IN (:cur-year,:nxt-year,:next-year)
|
cur-year is 2011
nxt-year is 2012
next-year is 2013
In the PROC_TABLE having CALEN_YEAR CHAR(4) values such as 2005 to 2014
In the DEV_TABLE having T16.EFF_DT DATE (10) values such as 2005-01-01 to 2014-12-31
PROC_TABLE
| Code: |
IN_LOC PART_NO CALEN_YEAR
0131W RDS7FG 2005
0131W RDS7FG 2006
0131W RDS7FG 2007
0131W RDS7FG 2008
0131W RDS7FG 2009
0131W RDS7FG 2010
0131W RDS7FG 2011
0131W RDS7FG 2012
0131W RDS7FG 2014
|
DEV_TABLE
| Code: |
IN_LOC PART_NO EFF_DT
0131W RDS7FG 28-02-2008
0131W RDS7FG 28-02-2009
0131W RDS7FG 28-02-2010
0131W RDS7FG 28-02-2011
0131W RDS7FG 10-12-2012
0131W RDS7FG 12-12-2013
0131W RDS7FG 12-11-2013
0131W RDS7FG 11-11-2014
0131W RDS7FG 09-11-2014
|
I am getting the result like below
| Code: |
IN_LOC PART_NO CALEN_YEAR EFF_DT
0131W RDS7FG 2011 28-02-2011
0131W RDS7FG 2011 --------
0131W RDS7FG 2012 10-12-2012
0131W RDS7FG 2013 12-12-2013
0131W RDS7FG 2013 ---------
|
My query should return the values like
| Code: |
IN_LOC PART_NO CALEN_YEAR EFF_DT
0131W RDS7FG 2011 28-02-2008
0131W RDS7FG 2011 28-02-2009
0131W RDS7FG 2011 28-02-2010
0131W RDS7FG 2011 28-02-2011
0131W RDS7FG 2011 ---------
0131W RDS7FG 2012 10-12-2012
0131W RDS7FG 2013 12-12-2013
0131W RDS7FG 2013 11-11-2014
|
My query need to compare the value of EFF_DT year filed to CALEN_YEAR ,If the EFF_DT year
having <=2011 and before means that record should display CALEN_YEAR value 2011 rows
If the EFF_DT year
having 2012 means that record should display CALEN_YEAR value 2012 rows
If the EFF_DT year
having >=2013 means that record should display CALEN_YEAR value 2013 rows
Thanks; |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Sep 14, 2011 12:00 pm Post subject: |
|
|
shyamsaravan,
Sigh. On first look Your required output data does NOT match with input data you have shown. How did this row get into the output?
| Code: |
0131W RDS7FG 2011 ---------
|
There are only 4 rows (2008, 2009,2010, 2011) Eff_Dt records. Where did you find the additional 5th row with null value for Eff_Dt?
Also why is this row dropped from the output ? You picked the 11-11-2014 record , why not 09-11-2014 record?
| Code: | | 0131W RDS7FG 09-11-2014 |
You keep going in circles and you already have wasted a month on this and haven't yet learned the art of explaining the requirement.
Kolusu |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Thu Sep 15, 2011 6:02 am Post subject: |
|
|
apology for the confusion..I have pasted the result like different set of records
| Code: |
EXEC SQL
DECLARE USERS_CURSOR CURSOR WITH RETURN FOR
SELECT DISTINCT
T24.IN_LOC
,T24.PART_NO
,T24.CALEN_YEAR
,T16.EFF_IN
FROM PROCCPVT_L_Y_P T24
LEFT OUTER JOIN
CPVT_CP_L_P_D T160A
ON T16.IN_LOC = T24.IN_LOC
AND T16.PART_NO = T24.PART_NO
AND T24.CALEN_YEAR =
LEFT(CHAR(YEAR(T16.EFF_IN)),4)
WHERE
T24.CALEN_YEAR
IN (:CUR-YEAR,:NXT-YEAR,:NEXT-YEAR)
AND
T24.IN_LOC ='ALDDD'
AND T24.PART_NO =' 6065'
END-EXEC.
|
Input PROC_table
| Code: |
IN_LOC PART_NO CALEN_YEAR
----------- ------------ --------------
ALDDD 6065 2010
ALDDD 6065 2011
ALDDD 6065 2012
ALDDD 6065 2013
ALDDD 6065 2014
|
Input DEV_table
| Code: |
IN_LOC PART_NO DT_EFF_IN
----------- ------------ --------------
ALDDD 6065 2009-10-22
ALDDD 6065 2011-07-31
ALDDD 6065 2013-01-04
|
Left outer join returns below result set
| Code: |
IN_LOC PART_NO CALEN_YEAR EFF_IN
----------- ------------ -------------- --------------
ALDDD 6065 2010 2009-10-22
ALDDD 6065 2010 2011-07-31
ALDDD 6065 2010 2013-01-04
ALDDD 6065 2011 2009-10-22
ALDDD 6065 2011 2011-07-31
ALDDD 6065 2011 2013-01-04
ALDDD 6065 2012 2009-10-22
ALDDD 6065 2012 2011-07-31
ALDDD 6065 2012 2013-01-04
ALDDD 6065 2013 2009-10-22
ALDDD 6065 2013 2011-07-31
ALDDD 6065 2013 2013-01-04
ALDDD 6065 2014 2009-10-22
ALDDD 6065 2014 2011-07-31
ALDDD 6065 2014 2013-01-04
|
Getting THE Below as Output
| Code: |
IN_LOC PART_NO CALEN_YEAR EFF_IN
----------- ------------ -------------- --------------
ALDDD 6065 2011 2011-07-31
ALDDD 6065 2012 -
ALDDD 6065 2013 2013-01-04
|
But output should be
| Code: |
IN_LOC PART_NO CALEN_YEAR EFF_IN
----------- ------------ -------------- --------------
ALDDD 6065 2011 2009-10-22
ALDDD 6065 2011 2011-07-31
ALDDD 6065 2012 -
ALDDD 6065 2013 2013-01-04
|
Please help |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Sep 15, 2011 1:28 pm Post subject: |
|
|
shyamsaravan,
Woah you keep changing your specifications at will? How did the EFF_DT magically change its format from DD-MM-CCYY to CCYY-MM-DD format?
Assuming that your EFF_DT is of the format CCYY-MM-DD , try this untested sql
| Code: |
EXEC SQL
DECLARE USERS_CURSOR CURSOR WITH RETURN FOR
SELECT C.*
FROM (SELECT DISTINCT
A.IN_LOC
,A.PART_NO
,A.CALEN_YEAR
,CASE WHEN (A.CALEN_YEAR = :CUR-YEAR AND
CHAR(YEAR(B.EFF_DT)) <= :CUR-YEAR)
THEN B.EFF_DT
WHEN (A.CALEN_YEAR = :CUR-YEAR AND
CHAR(YEAR(B.EFF_DT)) > :CUR-YEAR)
THEN CHAR('0001-01-01')
WHEN (A.CALEN_YEAR = :NXT-YEAR AND
CHAR(YEAR(B.EFF_DT)) = :NXT-YEAR)
THEN B.EFF_DT
WHEN (A.CALEN_YEAR = :NXT-YEAR AND
CHAR(YEAR(B.EFF_DT)) > :NXT-YEAR)
THEN CHAR('0001-01-01')
WHEN (A.CALEN_YEAR = :NEXT-YEAR AND
CHAR(YEAR(B.EFF_DT)) = :NEXT-YEAR)
THEN B.EFF_DT
WHEN (A.CALEN_YEAR = :NEXT-YEAR AND
CHAR(YEAR(B.EFF_DT)) <> :NEXT-YEAR)
THEN CHAR('0001-01-01')
END AS EFF_DT
FROM PROC_TABLE A
,DEV_TABLE B
WHERE A.IN_LOC = B.IN_LOC
AND A.PART_NO = B.PART_NO
AND A.CALEN_YEAR IN (:CUR-YEAR,:NXT-YEAR,:NEXT-YEAR)) C
WHERE C.EFF_DT > '0001-01-01'
OR C.EFF_DT IS NULL
END-EXEC |
Kolusu |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Fri Sep 16, 2011 6:18 am Post subject: |
|
|
Hi;
Great !  Thanks for the prompt reply...I am having the one of the columns before and after the EFF_DT column
| Code: |
EXEC SQL
DECLARE USERS_CURSOR CURSOR WITH RETURN FOR
SELECT C.*
FROM (SELECT DISTINCT
T24.IN_LOC
,T24.PART_NO
,T24.CALEN_YEAR
,T16.WKLY_PR
,CASE WHEN (CHAR(T24.CALEN_YEAR)=CHAR(:WS-CUR-YEAR) AND
CHAR(YEAR(T160.EFF_DT)) <=CHAR(:WS-CUR-YEAR))
THEN T16.EFF_DT
:
:
END EFF_DT
,T16.USER_NAME
:
:
END-EXEC
|
Getting the output like below (USER NAME and WKLY_PR repeated)
I have inserted 3 new rows in the DEV_TABLE ,so that 3 differrent rows displayed while comparing to the previous post
| Code: |
IN_LOC PART_NO CALEN_YEAR WKLY_PR EFF_DT USERNMAE
----------- ------------ -------------- ---------- ---------------- ------------
ALDDD 6065 2011 100 2011-07-31 JOHN
ALDDD 6065 2011 621 2009-10-22 PETER
ALDDD 6065 2012 100 - JOHN
ALDDD 6065 2012 621 - PETER
ALDDD 6065 2011 0 2009-01-13 RAMESH
ALDDD 6065 2011 111 2011-07-28 GOPI
ALDDD 6065 2011 5500 2009-07-16 FAROOK
ALDDD 6065 2012 111 - GOPI
ALDDD 6065 2012 5500 - FAROOK
ALDDD 6065 2013 333 2013-01-04 SAGAR
|
Output should be
| Code: |
IN_LOC PART_NO CALEN_YEAR WKLY_PR EFF_DT USER_NAME
----------- ------------ -------------- ---------- ---------------- ------------
ALDDD 6065 2011 100 2011-07-31 JOHN
ALDDD 6065 2011 621 2009-10-22 PETER
ALDDD 6065 2012 - - -
ALDDD 6065 2012 - - -
ALDDD 6065 2011 0 2009-01-13 RAMESH
ALDDD 6065 2011 111 2011-07-28 GOPI
ALDDD 6065 2011 5500 2009-07-16 FAROOK
ALDDD 6065 2012 - - -
ALDDD 6065 2012 - - -
ALDDD 6065 2013 333 2013-01-04 SAGAR
|
Thanks for your help |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Sep 16, 2011 10:15 am Post subject: |
|
|
shyamsaravan,
This will be my last post on this topic as you seem to change the requirements at will and I simply can't help.
Change the following statement in my query
to (Untested)
| Code: |
SELECT C.IN_LOC
,C.PART_NO
,C.CALEN_YEAR
,CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.WKLY_PR END
,C.EFF_DT
,CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.USER_NAME END
|
Kolusu |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Mon Sep 19, 2011 8:06 am Post subject: |
|
|
Thank you kolusu;Great thanks;
Only one questiion,Please answer..
If the EFF_DT is not available for the prescribed calendar year ,The row is not displaying
output
| Code: |
IN_LOC PART_NO CALEN_YEAR WKLY_PR EFF_DT USER_NAME
----------- ------------ -------------- ---------- ---------------- ------------
ALDDD 7000 2012 600 2012-10-22 PETER
ALDDD 7000 2012 700 2012-11-11 SAGAR
ALDDD 7000 2012 - - -
|
but the calendar years 2011,2012 and 2013 should be displayed.if the EFF_DT is not availble for that also.
See the example of output..in the example IN_LOC =ALDDD and PART_NO=7000 doesnt have 2011 and 2013 EFF_DT records ,but the year 2011,2012 and 2013 should be displayed
| Code: |
IN_LOC PART_NO CALEN_YEAR WKLY_PR EFF_DT USER_NAME
----------- ------------ -------------- ---------- ---------------- ------------
ALDDD 7000 2011 - - -
ALDDD 7000 2012 600 2012-10-22 PETER
ALDDD 7000 2012 700 2012-11-11 SAGAR
ALDDD 7000 2013 - - -
|
i have changed code like below what you suggest
SELECT DISTINCT C.IN_LOC
,C.PART_NO
,C.CALEN_YEAR
,CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.WKLY_PR END
,C.EFF_DT
,CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.USER_NAME END
Please help me! Please  |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Tue Sep 20, 2011 6:42 am Post subject: |
|
|
Hi Kolusu;
| Quote: |
CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.WKLY_PR END
,C.EFF_DT
,CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.USER_NAME END
|
the above CASE statement eleminates the entire row,if the EFF_DT is NULL, But we should have all the columns values upto CALEN_YEAR and remaining columns should not have the values
I am anticipating the answer very eagerly..Please help me..It is very very urgent requirement..please |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Tue Sep 20, 2011 12:42 pm Post subject: |
|
|
| Quote: |
It is very very urgent requirement..
|
Very, very urgent has no place in a forum. There is no way to be sure of an answer quidkly. . . Your dba or some senior on your project would be able to answer immediately. . .
From what you have poste it is not clear just what you want - making it difficult / impossible to provide a quick answer. . .
_________________
All the best,
di |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Tue Sep 20, 2011 1:02 pm Post subject: |
|
|
| shyamsaravan wrote: |
the above CASE statement eleminates the entire row,if the EFF_DT is NULL, But we should have all the columns values upto CALEN_YEAR and remaining columns should not have the values |
Unless you wrote your own query, the sql I showed does NOT eliminate the NULL rows.
| shyamsaravan wrote: |
I am anticipating the answer very eagerly..Please help me..It is very very urgent requirement..please |
You have been dealing with this question for past month and you change your requirements at will. Either way, I think I made it pretty clear here
| kolusu wrote: |
This will be my last post on this topic as you seem to change the requirements at will and I simply can't help. |
Kolusu |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Wed Sep 21, 2011 8:54 am Post subject: |
|
|
Many Thanks Kolusu;
As part of your suggestion is good for me..It never happens again...I am trying my level best to solve it.
Thank you very much for the kind co-operation.  |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Mon Oct 03, 2011 8:12 am Post subject: |
|
|
Hi
Here i have copied the code as per the direction..it doesnt give the prober output
| Code: |
SELECT DISTINCT
C.IN_LOC
,C.PART_NO
,C.MATT_DE
,C.CALEN_YEAR
,CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.WKLY_PR END
,C.EFF_DT
,CASE WHEN C.EFF_DT IS NULL
THEN NULL ELSE C.USER_NAME END
FROM (SELECT DISTINCT
A.IN_LOC
,A.PART_NO
,A.MATT_DE
,A.CALEN_YEAR
,CASE WHEN (A.CALEN_YEAR = :CUR-YEAR AND
CHAR(YEAR(B.EFF_DT)) <= :CUR-YEAR)
THEN B.EFF_DT
:
:
END AS EFF_DT
FROM PROC_TABLE A
LEFT OUTER JOIN
DEV_TABLE B
ON A.IN_LOC = B.IN_LOC
AND A.PART_NO = B.PART_NO
AND A.MATT_DE = B.MATT_DE
AND A.CALEN_YEAR IN (:CUR-YEAR,:NXT-YEAR,:NEXT-YEAR)
WHERE
A.IN_LOC ='ALDDD'
AND A.PART_NO =' 6065'
) C
WHERE C.EFF_DT > '0001-01-01'
OR C.EFF_DT IS NULL
|
I have added the Left outer join for the use of unmatched rows should return from PROC_table to the result set..
If the CALEN_YEAR and EFF_DT is matched for any row means and the empty row also returns as next row...It should not return..Please see the output below which i have given
Below as my getting output
| Code: |
IN_LOC PART_NO MATT_DE CALEN_YEAR WKLY_PR EFF_DT USER_NAME
----------- ------------ --------- ------------ ---------- -------------- ------------
ALDDD 6065 AB 2011 100 2011-07-31 JOHN
ALDDD 6065 AB 2011 621 2009-10-22 PETER
ALDDD 6065 AB 2011 - - - <---should not return
ALDDD 6065 AB 2012 - - -
ALDDD 6065 AB 2013 0 2013-01-13 RAMESH
ALDDD 6065 AB 2013 - - - <---should not return
<---2011 rec should return
ALDDD 6065 DE 2012 111 2012-07-28 GOPI
ALDDD 6065 DE 2012 - - - <---should not return
ALDDD 6065 DE 2013 210 2013-07-28 GOPI
ALDDD 6065 DE 2013 - - - <---should not return
ALDDD 6065 FF 2011 - - -
ALDDD 6065 FF 2012 - - -
ALDDD 6065 FF 2013 - - -
<---2011 rec should return
<---2012 recshould return
ALDDD 6065 GG 2013 333 2013-01-04 SAGAR
ALDDD 6065 GG 2013 - - - <---should not return
<---2011 rec should return
ALDDD 6065 JJ 2012 300 2012-02-04 SAGAR
ALDDD 6065 JJ 2012 - - - <---should not return
ALDDD 6065 JJ 2013 430 2013-01-04 SAGAR
ALDDD 6065 JJ 2013 - - - <---should not return
|
Expected output
| Code: |
IN_LOC PART_NO MATT_DE CALEN_YEAR WKLY_PR EFF_DT USER_NAME
----------- ------------ --------- ------------ ---------- -------------- ------------
ALDDD 6065 AB 2011 100 2011-07-31 JOHN
ALDDD 6065 AB 2011 621 2009-10-22 PETER
ALDDD 6065 AB 2012 - - -
ALDDD 6065 AB 2013 0 2013-01-13 RAMESH
ALDDD 6065 DE 2011 - - -
ALDDD 6065 DE 2012 111 2012-07-28 GOPI
ALDDD 6065 DE 2013 210 2013-07-28 GOPI
ALDDD 6065 FF 2011 - - -
ALDDD 6065 FF 2012 - - -
ALDDD 6065 FF 2013 - - -
ALDDD 6065 GG 2011 - - -
ALDDD 6065 GG 2012 - - -
ALDDD 6065 GG 2013 333 2013-01-04 SAGAR
ALDDD 6065 JJ 2011 - - -
ALDDD 6065 JJ 2012 300 2012-02-04 SAGAR
ALDDD 6065 JJ 2013 430 2013-01-04 SAGAR
|
I was trying for the past two weeks but i could not solve it...Any one please help....
Thanks in a advance  |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
