OraFAQ Forum: SQL & PL/SQL » here is my silly title because i am too lazy to come up with a meaningful title

Home » SQL & PL/SQL » SQL & PL/SQL » here is my silly title because i am too lazy to come up with a meaningful title

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

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

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Plz help urgent [message #239800 is a reply to message #239643]

Wed, 23 May 2007 06:04

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

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why the two first outputs are different?
Why do you absolutly use MINUS?

Regards
Michel

Report message to a moderator

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

Bill B
Messages: 1971
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');

Report message to a moderator

Re: Plz help urgent [message #239883 is a reply to message #239803]

Wed, 23 May 2007 09:44

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

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

Report message to a moderator

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

Bill B
Messages: 1971
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?

Report message to a moderator

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

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Bill B
Messages: 1971
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

rleishman
Messages: 3728
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

Report message to a moderator

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

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

rleishman
Messages: 3728
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

Report message to a moderator

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

ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD

Senior Member

Hi rleishman ,

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

Regards,
Venkat

Report message to a moderator

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

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

So what do I win?

Report message to a moderator

Previous Topic:	analytical sql statements
Next Topic:	Errbuf and retcode...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 18:35:06 CST 2024