Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

what is wrong in this query

Posted on 2014-12-23
11
Medium Priority
?
195 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 60

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 77

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 60

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 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 60

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 60

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.

597 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