what is wrong in this query

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
NiceMan331Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
i dont see any issue here...

how do you run this query? post the code you call this...
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HainKurtSr. System AnalystCommented:
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
 
slightwv (䄆 Netminder) Commented:
Thanks for correcting the typo!  ;)
0
 
NiceMan331Author Commented:
So, what I have to do ? I'm in urgent have to run it , I'm using toad ,
0
 
NiceMan331Author Commented:
When I removed the group by from the main query keeping only select unit it works
0
 
PortletPaulConnect With a Mentor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
NiceMan331Author Commented:
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
 
NiceMan331Author Commented:
ok , if any other problem i will post again
0
All Courses

From novice to tech pro — start learning today.