YES! Thanks so much, Mark. That did the trick. Rick
On Tue, Sep 16, 2008 at 1:38 PM, Eaton, Mark (DCS) < [EMAIL PROTECTED]> wrote: > ** 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]>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]> >> To: [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] *On Behalf Of *Joe DeSouza >> *Sent:* Tuesday, September 16, 2008 11:56 AM >> *To:* [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]> >> To: [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] Behalf Of *Eaton, Mark (DCS) >> *Sent:* Tuesday, September 16, 2008 10:09 AM >> *To:* [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] *On Behalf Of *Joe DeSouza >> *Sent:* Tuesday, September 16, 2008 10:04 AM >> *To:* [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]> >> To: [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]> wrote: >> >>> ** >>> >>> Rick, >>> >>> >>> >>> Try this: >>> >>> >>> >>> SELECT field1 FROM form ORDER BY field2 DESC; >>> >>> >>> >>> Kelvin >>> >>> >>> ------------------------------ >>> >>> *From:* Action Request System discussion list(ARSList) [mailto: >>> [EMAIL PROTECTED] *On Behalf Of *Rick Cook >>> *Sent:* Tuesday, September 16, 2008 10:16 AM >>> *To:* [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 ARSlist: "Where the Answers Are" >> html___ >> > > __Platinum Sponsor: 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"
