Improve company productivity with a Business Account.Sign Up

x
?
Solved

what is wrong in this query

Posted on 2014-12-23
11
Medium Priority
?
203 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 40515507
i dont see any issue here...

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 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 61

Expert Comment

by:HainKurt
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 79

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 50

Assisted Solution

by:Paul
Paul earned 600 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 61

Expert Comment

by:HainKurt
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 61

Accepted Solution

by:
HainKurt earned 800 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

608 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