Solved

what is wrong in this query

Posted on 2014-12-23
11
189 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 51

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 76

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 51

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 48

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 51

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 51

Accepted Solution

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

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 …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

839 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