Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword [message #304922] Fri, 07 March 2008 03:19 Go to next message
mailtonagaraja
Messages: 9
Registered: June 2007
Junior Member
Hi,
I got the above error while running this sql. can any one please let me know why i am getting this error.


create table mo1175_f2_txns
nologging parallel(degree 4)
as
select
member_number,
trading_date,
sum(decode(sign(total_spend), -1, -1, 0, 0, 1)) as total_txns,
sum(total_spend) as total_spend
from
(select /*+ parallel (a 4) (b 4) */
substr(a.mbc_card_number, 7, Cool as member_number,
trading_date,
transaction_id,
sum(total_sales_amount - nvl(total_discount_markdown_amt, 0) - nvl(total_discount_mkdn_gst_amt,0)) as total_spend
from abc.fact_transaction_product_vw a
,(select /*+ parallel (x 4) */ distinct
keycode_number
from abc.dim_product
-- ** usually not required for rep 2
-- ALTER AS REQUIRED
where
(

(replace(trim(upper(brand_name)),'&','') in ('COVERS') and tpc_description in ('CO Concessions') and (replace(trim(category_description),'&','') in ('Womens Concession'))) OR
(replace(trim(upper(brand_name)),'&','') in ('JUICY COUTURE') and tpc_description in ('WW Womenswear') and (replace(trim(category_description),'&','') in ('Contemporary'))) OR
(replace(trim(upper(brand_name)),'&','') in ('KITTE') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Jewellery'))) OR
(replace(trim(upper(brand_name)),'&','') in ('D G') and tpc_description in ('CS Consignment') and (replace(trim(category_description),'&','') in ('Accessory Consignmnt'))) OR
(replace(trim(upper(brand_name)),'&','') in ('CHANEL') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Sunglasses'))) OR
(replace(trim(upper(brand_name)),'&','') in ('BETTINA LANO') and tpc_description in ('WW Womenswear') and (replace(trim(category_description),'&','') in ('Contemporary'))) OR
(replace(trim(upper(brand_name)),'&','') in ('DIOR') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Sunglasses'))) OR
(replace(trim(upper(brand_name)),'&','') in ('PREVAGE') and tpc_description in ('CM Cosmetics') and (replace(trim(category_description),'&','') in ('Prestige Cos/Frag'))) OR
(replace(trim(upper(brand_name)),'&','') in ('ATMOSPHER') and tpc_description in ('CM Cosmetics')) and category_description is null --and (replace(trim(category_description),'&','') in ('')))
OR
(replace(trim(upper(brand_name)),'&','') in ('DANIELLE') and tpc_description in ('CM Cosmetics') ) and category_description is null --and (replace(trim(category_description),'&','') in ('')))
OR
(replace(trim(upper(brand_name)),'&','') in ('GUCCI') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Sunglasses'))) OR
(replace(trim(upper(brand_name)),'&','') in ('OROTON') and tpc_description in ('IA Intimate Apparel') and (replace(trim(category_description),'&','') in ('Lingerie'))) OR
(replace(trim(upper(brand_name)),'&','') in ('LE COUVENT DES MNMS') and tpc_description in ('CM Cosmetics') and (replace(trim(category_description),'&','') in ('Essentials'))) OR
(replace(trim(upper(brand_name)),'&','') in ('MACHER') and tpc_description in ('CM Cosmetics') and (replace(trim(category_description),'&','') in ('Essentials'))) OR
(replace(trim(upper(brand_name)),'&','') in ('FRED BARE') and tpc_description in ('FO Footwear') and (replace(trim(category_description),'&','') in ('Kids Shoes'))) OR
(replace(trim(upper(brand_name)),'&','') in ('AGR') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Jewellery'))) OR
(replace(trim(upper(brand_name)),'&','') in ('RED ROBIN') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Hosiery Socks'))) OR
(replace(trim(upper(brand_name)),'&','') in ('SONNY') and tpc_description in ('WW Womenswear') and (replace(trim(category_description),'&','') in ('Contemporary'))) OR
(replace(trim(upper(brand_name)),'&','') in ('NANNINI') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Handbags/Sml Leather'))) OR
(replace(trim(upper(brand_name)),'&','') in ('GREGORY LADNER') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Fashion Accessories'))) OR
(replace(trim(upper(brand_name)),'&','') in ('FRESHJIVE') and tpc_description in ('YA Youth Apparel') and (replace(trim(category_description),'&','') in ('Youth Mens Nat Brds'))) OR
(replace(trim(upper(brand_name)),'&','') in ('LACOSTE') and tpc_description in ('WW Womenswear') and (replace(trim(category_description),'&','') in ('Contemporary'))) OR
(replace(trim(upper(brand_name)),'&','') in ('KENNETH COLE') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Handbags/Sml Leather'))) OR
(replace(trim(upper(brand_name)),'&','') in ('LADYLUCK') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Fashion Accessories'))) OR
(replace(trim(upper(brand_name)),'&','') in ('ROBY') and tpc_description in ('AC Accessories') and (replace(trim(category_description),'&','') in ('Fashion Accessories'))) OR
(replace(trim(upper(brand_name)),'&','') in ('BAUHAUS') and tpc_description in ('YA Youth Apparel') and (replace(trim(category_description),'&','') in ('Youth Mens Nat Brds'))) OR
(replace(trim(upper(brand_name)),'&','') in ('MOSAIC') and tpc_description in ('FO Footwear') and (replace(trim(category_description),'&','') in ('Ladies Classic Shoes')))
)
) b,
(select to_number(y.member_number) as member_number
from mo1175_validate_mbrs y
-- , xyz.rr192_segments s
where ((y.validate_family_name = 'Y'
and y.validate_first_name = 'Y')
or (y.validate_family_name = 'Y'
and y.validate_title = 'Y'))
and y.age between 25 and 40 --** Change / Review this Code
and (y.gender = 'F' or y.sec_gender = 'F')
-- ADD TIER CODE
and tier_code in (2,3)
-- ADD SEGMENT DATA
-- and s.member_number = y.member_number
-- ** always required for rep 2
-- and current_brand_segment in (1,3,5)
minus
select to_number(y.member_number) as member_number
from mo1175_rank_group y
) c
where a.trading_date between to_date(to_char(to_date('&today_dt','DD-MON-YYYY')-1,'DD-MON-') ||
to_char(to_number('2'|| to_char(to_date('&today_dt','DD-MON-YYYY')-1,'YYY'))-1),'DD-MON-YYYY')
and to_date('&today_dt','DD-MON-YYYY')-1
and a.mbc_card_indicator = 1
and a.keycode_number = b.keycode_number
and substr(a.mbc_card_number, 7, Cool = c.member_number
group by substr(a.mbc_card_number, 7, Cool,
trading_date,
transaction_id
)
group by member_number
,trading_date
/


Thanks and regards
Nag
Re: ORA-00928: missing SELECT keyword [message #304923 is a reply to message #304922] Fri, 07 March 2008 03:27 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
did you learn nothing from THIS POST

{edit: spalling errir}

[Updated on: Fri, 07 March 2008 03:28]

Report message to a moderator

Re: ORA-00928: missing SELECT keyword [message #304930 is a reply to message #304922] Fri, 07 March 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Follow them and also for about formatting.

Regards
Michel

[Edit: @pablolee, some never learns. Sad }

[Updated on: Fri, 07 March 2008 04:11]

Report message to a moderator

Re: ORA-00928: missing SELECT keyword [message #304933 is a reply to message #304930] Fri, 07 March 2008 04:14 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Very true Michel. Hopefully the OP will (I have to have faith, otherwise I'd never attempt to answer any questions)
Previous Topic: How to call a .exe or(.bat etc..) file from Pl/Sql
Next Topic: Referential integrity between 2 tables in different schema
Goto Forum:
  


Current Time: Sat Dec 10 05:28:22 CST 2016

Total time taken to generate the page: 0.08781 seconds