Category_Status is a drop list field and its value will not be 'Enabled'.. it will be either 0 or 1 or 2 or whatever is appropriate depending on what your Category Status field attributes are.. Correct that and it will work for you.. Cheers Joe
----- Original Message ---- From: Rick Cook <[EMAIL PROTECTED]> To: arslist@ARSLIST.ORG Sent: Tuesday, September 16, 2008 4:24:46 PM 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
