Home » SQL & PL/SQL » SQL & PL/SQL » group by error (oracle 9i)
group by error [message #339496] Thu, 07 August 2008 11:38 Go to next message
veljobs
Messages: 6
Registered: July 2008
Junior Member
Hi all ,
My query throws group by error , i tried all possible ways to solve it .But could not


this is the query

any help is really appreciated
thanks

CREATE OR REPLACE FORCE VIEW TEST AS
SELECT
a.lid
,b.lkey
,c.pkey

,(case b.ee2k when 'Y' then 0
else sum(nvl(sales,0))
end
)doctor_sales


,sum(nvl(traffic,0)) TRAFFIC
,sum(nvl(visioncare_sales,0)) VISOIN

FROM
lb_als a
,geo b
,di c
,di d
WHERE b.lkey = a.lKey
AND b.lrg_type = 'LENS'
AND c.pkey = a.pkey

GROUP BY
b.lkey
,c.pkey
,a.lid;


Re: group by error [message #339497 is a reply to message #339496] Thu, 07 August 2008 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


CREATE OR REPLACE FORCE VIEW Test
AS
  SELECT   a.Lid,
           b.lKey,
           c.pKey,
           (CASE b.ee2k 
              WHEN 'Y' THEN 0
              ELSE SUM(Nvl(Sales,0))
            END) Doctor_Sales,
           SUM(Nvl(Traffic,0)) Traffic,
           SUM(Nvl(VisionCare_Sales,0)) vIsoIn
  FROM     lb_als a,
           geo b,
           di c,
           di d
  WHERE    b.lKey = a.lKey
           AND b.lrg_Type = 'LENS'
           AND c.pKey = a.pKey
  GROUP BY b.lKey,
           c.pKey,
           a.Lid;
Re: group by error [message #339500 is a reply to message #339497] Thu, 07 August 2008 11:55 Go to previous messageGo to next message
veljobs
Messages: 6
Registered: July 2008
Junior Member
Was that about number lines or me searching . ..Iam not sure


CREATE OR REPLACE FORCE VIEW TEST AS
SELECT
a.lid
,(case b.ee2k when 'Y' then 0
else sum(nvl(sales,0))
end
)doctor_sales

FROM
lb_als a
,geo b

WHERE b.lkey = a.lKey

GROUP BY
a.lid;

Re: group by error [message #339505 is a reply to message #339496] Thu, 07 August 2008 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
we don't know what are your tables
we don't know what is your data
we don't know exactly you desire the solution to be.

We know you have a problem spelling S-Q-L correctly.

we know you don't know how to read & follow posting guidelines.
Re: group by error [message #339506 is a reply to message #339500] Thu, 07 August 2008 12:04 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Start by checking if the SELECT works, always copy and paste your session with the error, don't describe it.

By the way, if you try to explain the following line you will see what is wrong:
"case b.ee2k when 'Y' then 0 else sum(nvl(sales,0))"


Regards
Michel
Previous Topic: Integrity problem with FK
Next Topic: What leads to server crash?
Goto Forum:
  


Current Time: Sun Dec 04 20:32:23 CST 2016

Total time taken to generate the page: 0.07923 seconds