Home » Developer & Programmer » Reports & Discoverer » Between clause to Compare two String values (Oracle forms 10g,XP)
Between clause to Compare two String values [message #587457] Sat, 15 June 2013 00:56 Go to next message
SandeepS
Messages: 15
Registered: April 2013
Junior Member
Hi All,

I have a problem with Between clause used in where statement to compare two string variable.

Query is like this,

select item_code, item_deacrption
from itm_master, invoce_det
where im_code = item_code
AND invd_item_number BETWEEN (:startNum) AND (:endNum)

Here invd_item_number is a DB field and is of type varchar2(41), and (:startNum),(:endNum) are of same type.

now invd_item_number has one value '001003002001'
if we give :startNum = '001003001002' and :endNum = '001003004006'

:startNum and :endNum is composed of separate field values (ie, 1st 3 character shows color code, next 3 for catagory, next 3 for size etc). These codes are entered separately and are combined at run time.

it is still fetching the invd_item_number with value '001003002001'. (the last set of character(type code) in the :startNum is greater than invd_item_number's type code value. But it is smaller than the previous code (size code), that's why it is fetching).

But how can i get around this as i don't need that value to be fetched.


Please Please help Me.


Thanks
Sandeep



---Every negative event will have with in it a seed of an equal or greater benefit

[Updated on: Sat, 15 June 2013 00:58]

Report message to a moderator

Re: Between clause to Compare two String values [message #587463 is a reply to message #587457] Sat, 15 June 2013 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
 
startNum = 001003001002
           001003002001  --> invd_item_number really is between these two
endNum   = 001003004006
                   ^
                   |
        this is a number that puts INVD_ITEM_NUMBER between (1-2-4)

Quote:
But how can i get around this as i don't need that value to be fetched.

Is it that value only, or are there some other values as well? If it is only that value, add
and invd_item_number <> '001003002001'
into conditions. If there are other values as well, either specify them all (using NOT IN operator), or - more likely - your theory is wrong and you'll have to develop a different one.

[Updated on: Sat, 15 June 2013 04:24]

Report message to a moderator

Re: Between clause to Compare two String values [message #587469 is a reply to message #587463] Sat, 15 June 2013 06:27 Go to previous messageGo to next message
SandeepS
Messages: 15
Registered: April 2013
Junior Member
Hi Littlefoot,

Thank you for the reply,

Not only that value (001003002001) there will be many data in that range in the DB.

So, i guess i can't use 'BETWEEN' in this case, or have to find some other work arounds


Thanks and Regards
Sandeep




--Every negative event will have within itself the seed of an equal or greater benefit

Report message to a moderator

Re: Between clause to Compare two String values [message #587477 is a reply to message #587469] Sat, 15 June 2013 15:03 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe you can use BETWEEN, but using different values. You said that this "001003002001" strings consist of several parts (001 is color code, 003 is category, 002 is size, 001 is something else). So I was thinking that maybe you could use substring instead of the whole string, for example
where substr(invd_item_number, 6, 7) between substr(:startNum, 6, 7) AND substr(:endNum, 6, 7)
If you find something like that useful (it's just an idea, I don't know what values would satisfy your condition), note that - if there's a lot of data - you might benefit from function based index on that column.

Report message to a moderator

Re: Between clause to Compare two String values [message #587534 is a reply to message #587477] Sun, 16 June 2013 23:40 Go to previous messageGo to next message
SandeepS
Messages: 15
Registered: April 2013
Junior Member
Hi Littlefoot,

Thanks for the Reply,

Problem for me with substr() is that the item_code field may not contain codes(size code, color code, type code) in correct order always. if any one code (say color code) is not selected, then after size code, type code will be attached.
So i cannot necessarily say the exact position of each code.It will not be same always.


Is there any way to compare two null values using between

ex:
where c.class_code(+) = im_class_code and c.class_code between :frm_class and :to_class
and sub_class_code(+) = im_sub_class_code and sub_class_code between :frm_sub and :to_sub
and catg_code(+) = im_catg_code and catg_code between :frm_catg and :to_catg

Here, :frm_catg and to_catg may be null, but i want records that matches class_code and sub_class_code values

is there any way to get these values (Tried with OR but it's showing error)

ERROR at line 16:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Thanks for any help

Sandeep


--Every negative event has within itself the seed of an equal or greater benefit.

Report message to a moderator

Re: Between clause to Compare two String values [message #587538 is a reply to message #587534] Mon, 17 June 2013 00:15 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You're in deep trouble. You are dealing with "codes" which can not be uniquely understood and, from what you described so far, it is an impossible task. You said that some codes can be missing. How do you know which ones? How do you interpret code that looks like 001002? What are these triples (001 and 002)? Are they size code + color code? Or size code + type code? Or color code + type code? There's no use in comparing anything because you don't know what you are comparing.

Unless you make sense out of that model, I don't see a way to get out of problem.

Report message to a moderator

Re: Between clause to Compare two String values [message #587899 is a reply to message #587538] Wed, 19 June 2013 04:55 Go to previous messageGo to next message
SandeepS
Messages: 15
Registered: April 2013
Junior Member
Hi LittleFoot,

Thank You for the Reply..

Yeah , I understood i have done it wrong, But a bit later Sad .
I corrected it by changing the entire where clause as a Dynamic Query, So only needed conditions will be passed from the form.

My sample WHERE clause will look like,

IF GET_ITEM_PROPERTY('FRM_SIZE', ENABLED) = 'TRUE' THEN
WHERE_CLAUSE := WHERE_CLAUSE || ' NVL(SIZE_CODE,0) BETWEEN '||:FRM_SIZE_CODE ||' AND '|| :TO_SIZE_CODE;
ELSE
WHERE_CLAUSE := WHERE_CLAUSE || ' AND SIZE_CODE(+) = IM_SIZE_CODE ';
END IF;



Now I am able to make it work as user requirement, and thanks a lot Littlefoot for helping me in my troubles and giving me valuable replies. Hopefully your help may continue.

Thanks Again

Sandeep


--Every negative event has within itself the seed of an equal or greater benefit.

Report message to a moderator

Re: Between clause to Compare two String values [message #587901 is a reply to message #587899] Wed, 19 June 2013 04:57 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, I'm glad you made it work!

Report message to a moderator

Previous Topic: Case Statement In Oracle Reports Query
Next Topic: problem generating dos reports in 10g while migrating 6i to 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 02 17:27:54 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.