Solved

what is wrong in this query

Posted on 2014-12-23
11
193 Views
Last Modified: 2014-12-24
hi
i have this statement

select unit,to_number(to_char(dob + 1 ,'IW')) Wk,count(checkid) Cus_Cnt,modeid,period,revid,occasion,sum(amount) Value from
(select unit,dob,checkid,modeid,period,revid,occasion,sum(price) Amount
 from cr_gnditem
where dob > '31-dec-13'
and unit in(select de.str_no
 from cr_store de where de.opdate > to_date('2013-12-28', 'YYYY-MM-DD'))
 group by unit,dob,checkid,modeid,period,revid,occasion)
 group by unit,to_number(to_char(dob + 1 ,'IW')),modeid,period,revid,occasion
 order by unit,to_number(to_char(dob + 1 ,'IW')),modeid,period,revid,occasion;
 

Open in new window


yesterday it run well and gives correct result , but today i dpn't know what happened , it suddenly send error "sql not propeply ended"  may be by mistake i delete something but i don't know what
the sub query run well , starting line 3 till 8 is ok , so only the main query
i tried to remove some column from the main query , but same error
0
Comment
Question by:NiceMan331
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40515507
i dont see any issue here...

how do you run this query? post the code you call this...
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 150 total points
ID: 40515745
>>where dob > '31-dec-13'

First:  Always to explicit data type conversions:  where dob > to_date('31-dec-13','DD'MON-YY')

The SQL looks fine to me as well.

Maybe something in your environment changed.
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40515748
First:  Always to explicit data type conversions:  where dob > to_date('31-dec-13','DD'MON-YY')

>>>

First:  Always to explicit data type conversions:  where dob > to_date('31-dec-13','DD-MON-YY')
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40515804
Thanks for correcting the typo!  ;)
0
 

Author Comment

by:NiceMan331
ID: 40515947
So, what I have to do ? I'm in urgent have to run it , I'm using toad ,
0
 

Author Comment

by:NiceMan331
ID: 40515948
When I removed the group by from the main query keeping only select unit it works
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 150 total points
ID: 40515976
Not sure why you are using a subquery because it appears to be redundant. Please try:
SELECT
      unit
    , to_number(to_char(dob + 1, 'IW')) WK
    , COUNT(checkid) CUS_CNT
    , modeid
    , period
    , revid
    , occasion
    , SUM(price) AMOUNT
FROM cr_gnditem
WHERE dob > to_date('2013-12-31', 'YYYY-MM-DD')
AND unit IN (
      SELECT
            DE.str_no
      FROM cr_store DE
      WHERE DE.opdate > to_date('2013-12-28', 'YYYY-MM-DD')
)
GROUP BY
        unit
      , to_number(to_char(dob + 1, 'IW')) WK
      , modeid
      , period
      , revid
      , occasion
ORDER BY
        unit
      , to_number(to_char(dob + 1, 'IW')) WK
      , modeid
      , period
      , revid
      , occasion
;

Open in new window


{+edit:}
I would encourage you to ALWAYS use a 4 digit year, and I woudl also suggest you use one consistent date literal format like YYYY-MM-DD

i.e. As your original query already uses YYYY-MM-DD (2013-12-28) why introduce DD-MMM-YY (31-dec-13)?
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40515981
maybe issue is extra ";" at the end...
what do you get when you run this:

select unit, to_number(to_char(dob + 1 ,'IW')) Wk,count(checkid) Cus_Cnt, modeid, period, revid, occasion, sum(amount) Value 
  from (
       select unit, dob, checkid, modeid, period, revid, occasion, sum(price) Amount
         from cr_gnditem
        where dob > to_date('31-dec-13','dd-mon-yy')
          and unit in(select de.str_no from cr_store de where de.opdate > to_date('2013-12-28', 'YYYY-MM-DD'))
        group by unit, dob, checkid, modeid, period, revid, occasion
        )
 group by unit,to_number(to_char(dob + 1 ,'IW')), modeid, period, revid, occasion
 order by unit, to_number(to_char(dob + 1 ,'IW')), modeid, period, revid, occasion

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40516126
i don't know what happened
today ( which is my next day of the question ) , i didn't touch any thing
i just run the sql , it run
i don't know really what happened
any one know any idea ?
thanx
0
 
LVL 55

Accepted Solution

by:
Huseyin KAHRAMAN earned 200 total points
ID: 40516441
i didn't touch any thing
i just run the sql , it run

maybe your TOAD got some virus and it runs the queries on even days and giving error on odd days... today is 24, even, so fine... it means you should run it on even days... problem solved...
0
 

Author Comment

by:NiceMan331
ID: 40517334
ok , if any other problem i will post again
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question