Home » SQL & PL/SQL » SQL & PL/SQL » here is my silly title because i am too lazy to come up with a meaningful title
here is my silly title because i am too lazy to come up with a meaningful title [message #239643] Tue, 22 May 2007 23:09 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

Please help out with the following requirements.

DESC ANSWER_TABLE

gtablecode1  	  varchar2(10)
gtablecode2  	  varchar2(10)
ans_returned_chr3 varchar2(10)

select gtablecode1,gtablecode2,ANS_RETURNED_CHR3 
from answer_table 
where gtablecode1 = 'DP_DEC_PAGE'


And the Output is :

GTABLECODE1	GTABLECODE2	ANS_RETURNED_CHR3

DP_DEC_PAGE	INT_POLICY_JKT	        ZZ
DP_DEC_PAGE	INT_DEC_PAGE	        ZZ
DP_DEC_PAGE	GM_POL_BOOK	        ZZ
DP_DEC_PAGE	INT_CAUSE_LOSS	        ZZ
DP_DEC_PAGE	SERVICE_OF_SUIT	        ZZ
DP_DEC_PAGE	IN_WITNESS_CLAUSE	ZZ
DP_DEC_PAGE	SYS_BREAKDOWN	        ZZ
DP_DEC_PAGE	SERVICE_OF_SUIT	        IL


And My requierment is that I will pass the ans_returned_chr3 at run time .If it is 'IL' then it should show

GTABLECODE1	GTABLECODE2	ANS_RETURNED_CHR3

DP_DEC_PAGE	INT_POLICY_JKT	        ZZ
DP_DEC_PAGE	INT_DEC_PAGE	        ZZ
DP_DEC_PAGE	GM_POL_BOOK	        ZZ
DP_DEC_PAGE	INT_CAUSE_LOSS	        ZZ
DP_DEC_PAGE	IN_WITNESS_CLAUSE	ZZ
DP_DEC_PAGE	SYS_BREAKDOWN	        ZZ
DP_DEC_PAGE	SERVICE_OF_SUIT	        IL


Note: If ans_returned_chr3 = 'IL' then SERVICE_OF_SUIT whose ans_returned_chr3 should be taken plus all other forms whose state is 'ZZ'

If the answer_return is other than 'IL' let's say e.g.,'CA','NY','NV'... then the output will be

GTABLECODE1	GTABLECODE2	ANS_RETURNED_CHR3

DP_DEC_PAGE	INT_POLICY_JKT	        ZZ
DP_DEC_PAGE	INT_DEC_PAGE	        ZZ
DP_DEC_PAGE	GM_POL_BOOK	        ZZ
DP_DEC_PAGE	INT_CAUSE_LOSS	        ZZ
DP_DEC_PAGE	SERVICE_OF_SUIT	        ZZ
DP_DEC_PAGE	IN_WITNESS_CLAUSE	ZZ
DP_DEC_PAGE	SYS_BREAKDOWN	        ZZ


Note: SYS_BREAKDOWN whose ans_returned_chr3 ='ZZ' is taken plus other.

I want to achieve this requirement without using MInus Operator.
Is there any way to do that ?

Please help in this

[Updated on: Wed, 23 May 2007 08:17] by Moderator

Report message to a moderator

Re: Plz help urgent [message #239684 is a reply to message #239643] Wed, 23 May 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First:
- Change your topic title to a more explicit one
- read and apply How to format your posts
- post your Oracle version

Regards
Michel
Re: Plz help urgent [message #239800 is a reply to message #239643] Wed, 23 May 2007 06:04 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

Oracle Version is :

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

Regards,
venkat

[Updated on: Wed, 23 May 2007 06:05]

Report message to a moderator

Re: Plz help urgent [message #239803 is a reply to message #239800] Wed, 23 May 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why the two first outputs are different?
Why do you absolutly use MINUS?

Regards
Michel
Re: here is my silly title because i am too lazy to come up with a meaningful title [message #239862 is a reply to message #239643] Wed, 23 May 2007 08:31 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select gtablecode1,gtablecode2,ANS_RETURNED_CHR3
from answer_table
where gtablecode1 = 'DP_DEC_PAGE'
and ans_returned_chr3 in ('ZZ','&WHAT_ANS');
Re: Plz help urgent [message #239883 is a reply to message #239803] Wed, 23 May 2007 09:44 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi Michel,

1)The first two outputs are different with the form_code
In the above mentioned table i have another column in that i store the form_code against each entry.(column ans_returned_chr2).First preference is given to the state if it is not present then only the preference goes to the 'ZZ'.

The scenario is You can have either a state specific form or for all states.
If u have a state specific form then the preority is given to that form (In our case ans_return_chr3 ='IL',and gtablecode2 = 'SYS_BREAKDOWN') THen this form is given the preority and other output forms to be selected (Except ans_return_chr3 ='ZZ',and gtablecode2 = 'SYS_BREAKDOWN')

1) All states i.e.,'ZZ' stands for all the states
2) Ana a state specific forms like 'IL'.

i can't suppose to use "Minus" as the data in these tables are very huge and even My PM is not happy to use such thing .so, is there any other way to achieve the same

Regards,
venkat

[Updated on: Wed, 23 May 2007 09:44]

Report message to a moderator

plz help urgent [message #239885 is a reply to message #239862] Wed, 23 May 2007 09:46 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi ,

Thanks for u r suggestion .But the above query is won't work
bcz it selects both the outputs from the answer_table.
i.e., it selects entries with 'ZZ' and also 'IL'.
Please go/look at my requirements.

Thanks,
venkat
Re: here is my silly title because i am too lazy to come up with a meaningful title [message #239894 is a reply to message #239643] Wed, 23 May 2007 10:19 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I am having a real problem understanding your requirements. For example you say that if you specify ZZ it returns all states, but if you specify IL it returns IL and most of the ZZ lines. What determines which ZZ lines get returned if you don't specify ZZ?
Re: here is my silly title because i am too lazy to come up with a meaningful title [message #239895 is a reply to message #239894] Wed, 23 May 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was also my question.
Why there is not the same number of ZZ lines in the 2 first outputs?

Regards
Michel
Re: here is my silly title because i am too lazy to come up with a meaningful title [message #239896 is a reply to message #239643] Wed, 23 May 2007 10:29 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Cancelled answer, I still don't understand

[Updated on: Wed, 23 May 2007 10:30]

Report message to a moderator

Re: here is my silly title because i am too lazy to come up with a meaningful title [message #240096 is a reply to message #239896] Thu, 24 May 2007 02:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
My understanding:
- If you pass in 'IL', you want to see all of the IL and ZZ rows showing their respective codes unaltered.
- If you pass any other values, you want to see the same rows, but this time the IL values should display as ZZ.

select gtablecode1
,      gtablecode2
,      decode('&WHAT_ANS', 'IL', ans_returned_chr3, 'ZZ')
from answer_table 
where gtablecode1 = 'DP_DEC_PAGE'
and ans_returned_chr3 in ('ZZ','IL', '&WHAT_ANS');


Ross Leishman
Re: here is my silly title because i am too lazy to come up with a meaningful title [message #240145 is a reply to message #240096] Thu, 24 May 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not fit the first posted result for the posted content.
They are less ZZ lines in the result and we still don't know why.

Until say why, we can't help him but maybe he made a mistake and already got his answer and let us work for him for nothing.

Regards
Michel
Re: here is my silly title because i am too lazy to come up with a meaningful title [message #240220 is a reply to message #240145] Thu, 24 May 2007 07:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yeah, you're right. But I love guessing games. Here's another random query:
select gtablecode1
,      gtablecode2
,      min(decode('&WHAT_ANS', 'IL', ans_returned_chr3, 'ZZ'))
from answer_table 
where gtablecode1 = 'DP_DEC_PAGE'
and ans_returned_chr3 in ('ZZ','IL');


Ross Leishman
Re: here is my silly title because i am too lazy to come up with a meaningful title [message #241048 is a reply to message #240145] Mon, 28 May 2007 04:30 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi rleishman ,

That's it.It worked.Thanks a lot.

Regards,
Venkat

Re: here is my silly title because i am too lazy to come up with a meaningful title [message #241125 is a reply to message #241048] Mon, 28 May 2007 07:22 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So what do I win?
Previous Topic: analytical sql statements
Next Topic: Errbuf and retcode...
Goto Forum:
  


Current Time: Wed Dec 07 20:14:31 CST 2016

Total time taken to generate the page: 0.07388 seconds