I'm guessing Category_Status is a Selection field and if so, then you need to
use the number associated with 'Enabled' (most likely 0 because Enabled is the
first item is the Selection list?). Since it is a number, you wouldn't use the
quotes - it should just be Category_Status = 0.
Mark
________________________________
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]
On Behalf Of Rick Cook
Sent: Tuesday, September 16, 2008 1:25 PM
To: [email protected]
Subject: Re: Help with Oracle select statement
**
Well, here's the statement I'm using:
SELECT Generic_Categorization_Tier_1, Usage FROM CIS_HPD_Menu_Item_Ranking
WHERE (Generic_Categorization_Tier_1 IS NOT NULL) AND (Category_Status =
'Enabled') AND (Category_Type = '$1000002257$') ORDER BY Usage desc
Now when I select the menu, if the Cat 1 field is NOT NULL, I'm getting this
error:
ARERR [552] Failure during SQL operation to the database : ORA-01722: invalid
number
If the field is NULL, I just get a (No entries in menu) message. I thought it
might have been objecting to NULL values or zeros not being numbers, so I do
have numbers in the Usage field.
Anyone have an idea of what I need to do to get around that error?
Rick
On Tue, Sep 16, 2008 at 12:20 PM, Joe DeSouza <[EMAIL PROTECTED]<mailto:[EMAIL
PROTECTED]>> wrote:
**
You may be right... I think however there is still the limitation when creating
a view and ordering that view by a field.. That field has to be a part of the
view.. for eg if you have a table with fields A B and C and you create a view
with fields A and B and ordered by C, I do not think Oracle would allow
Create View XYZ as Select A, B from ABC order by C
Do not really have the time to verify that but if anyone with a little more
time than me at hand wants to do so, please do post the results..
Cheers
Joe
----- Original Message ----
From: "Eaton, Mark (DCS)" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [email protected]<mailto:[email protected]>
Sent: Tuesday, September 16, 2008 3:08:10 PM
Subject: Re: Help with Oracle select statement
**
It must have been even further back in versions that you needed it. I am
currently on Oracle 9i and I don't need to have my "Order By" field in the
select statement.
Mark
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[email protected]<mailto:[email protected]>] On Behalf Of Joe
DeSouza
Sent: Tuesday, September 16, 2008 11:56 AM
To: [email protected]<mailto:[email protected]>
Subject: Re: Help with Oracle select statement
**
You did need that before but with Oracle 10 G I have noticed it is no longer
required to be in the select statement to order it by that..
Joe
----- Original Message ----
From: "Pargeter, Christie :CO IS" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [email protected]<mailto:[email protected]>
Sent: Tuesday, September 16, 2008 1:13:57 PM
Subject: Re: Help with Oracle select statement
**
my SQL for Dummies book says you need the order by field in the select part of
the statement too.
SELECT Generic_Categorization_Tier_1, Usage FROM CIS:HPD:Menu_Item_Ranking
WHERE (Generic_Categorization_Tier_1 != NULL) AND (Category_Status = 'Enabled')
AND (Category_Type = $1000002257$) ORDER BY Usage desc;
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]<mailto:[email protected]>]On Behalf Of Eaton,
Mark (DCS)
Sent: Tuesday, September 16, 2008 10:09 AM
To: [email protected]<mailto:[email protected]>
Subject: Re: Help with Oracle select statement
**
You should also put single quotes around $100002257$ and remove the semi-colon
at the end. Remedy will take care of that for you.
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[email protected]<mailto:[email protected]>] On Behalf Of Joe
DeSouza
Sent: Tuesday, September 16, 2008 10:04 AM
To: [email protected]<mailto:[email protected]>
Subject: Re: Help with Oracle select statement
**
And when comparing null you use
IS NOT NULL
Or
IS NULL
Joe
----- Original Message ----
From: Rick Cook <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [email protected]<mailto:[email protected]>
Sent: Tuesday, September 16, 2008 12:56:47 PM
Subject: Re: Help with Oracle select statement
**
OK, that makes sense, thanks. I think I'm close, but would like someone with
more knowledge than I to take a look at my conversion and tell me why it's not
working.
Here's the original in a Query menu: (Selecting Tier 1 of a Category/Type/Item)
( 'Generic Categorization Tier 2' = $1000000745$) AND ( 'Generic Categorization
Tier 1' = $1000000744$) AND ( 'Generic Categorization Tier 3' != $NULL$ ) AND
( 'Category Status' = "Enabled") AND ( 'Category Type' = $1000002257$)
I am attempting to add the Order by qualification and do it from a SQL menu.
Here's what I have so far:
SELECT Generic_Categorization_Tier_1 FROM CIS:HPD:Menu_Item_Ranking WHERE
(Generic_Categorization_Tier_1 != NULL) AND (Category_Status = 'Enabled') AND
(Category_Type = $1000002257$) ORDER BY Usage desc;
I get an error saying ORA-00933: SQL command not properly ended.
Rick
On Tue, Sep 16, 2008 at 7:35 AM, Wallace, Kelvin <[EMAIL
PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
**
Rick,
Try this:
SELECT field1 FROM form ORDER BY field2 DESC;
Kelvin
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[email protected]<mailto:[email protected]>] On Behalf Of Rick Cook
Sent: Tuesday, September 16, 2008 10:16 AM
To: [email protected]<mailto:[email protected]>
Subject: Help with Oracle select statement
**
Am looking to get some specific syntax on a SQL call ARS 7 to Oracle 9 that
would do the following:
Select field1 from Form order by field2 (field2 is int field. I want the sort
done highest value to lowest)
Thanks!
Rick
__Platinum Sponsor: www.rmsportal.com<http://www.rmsportal.com> ARSlist: "Where
the Answers Are" html___
__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
