|
Hi,
I have written a query to extract data from a table whose created date lies between current date and 30 days ago.in PL/SQL
I have written query as follows:
select creat_dt from shpt
where shpt.creat_dt between trunc(sysdate) and trunc(sysdate-30)
Although it is having records for which created date falls between current date and 30 days prior to current date, it still shows me no records extracted.
Help me with this.
|
|
|
|
|
Don't know about Oracle, but in SQL Server that wouldn't work, you have to put the earliest date first.
where shpt.creat_dt between trunc(sysdate-30) and trunc(sysdate)
It's not logical, but thats how it works in SQL Server and I suspect Oracle!
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Yap, this applies to Oracle also.
Based on ANSI standard between is converted to:
Field >= StartValue AND Field <= EndValue
Logical, isn't it 
|
|
|
|
|
hi,
try swap the dates
between 20-10-2000 and 25-10-2001
between 25-10-2001 and 20-10-2000
it has to work no reason for otherwise
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi friends,
When i schedule my jobs they fail after executing for few seconds,but when i execute the same job manually its executed.I am unable to find the reason ..can any one help..
Regards,
Annu
|
|
|
|
|
User permissions?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
no...i don't hv any thing like user permission..
Actually i am new to SQL and Database..
So any thing else i am missing out...????
|
|
|
|
|
When you run it its running with your security settings and permissions, when it runs as a scheduled job it isn't. So, does the account the scheduler is running under have the necessary pemissions in the database to perform what ever it is your sql does?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
No we dont hv any security settings and permission.when run manually or scheduled.
|
|
|
|
|
Can u let me know the possible reasons so that i can have a cross check on it.
|
|
|
|
|
annu08 wrote: No we dont hv any security settings and permission.
Sorry, but you do, even if they are only the default ones - and this is not recommended.
Have a look to see what account your scheduled job runs under, then have a look in the security details under SQL Server Management to see what rights they have, at a server, database and table level in relation to yourself.
Other tahn that I have no suggestions as to why this is happening.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
k, will check the details and get back with the exact problem.
Anyway thank u.
Regards,
Annu
|
|
|
|
|
Hi All,
I am writing a query for a daily report generation. It involves data from 4 tables. Am so confused about which joins to use. It also makes use of GROUP BY CLAUSE on Cust_NBR and make use of agrregate function Count.
I have written a query as follows but it's not working.
Plz help me...as it is urgent.
select SH.ARBL_NBR Airbill,S.cust_NBR Account_Number,S.Shpt_Id,S.City_Nam sender_City,
S.St_Prvnc_Cd Sender_State,s.cntry_id Sender_Country,S.Postl_Cd sender_postal,
S.Svc_Lvl_Cd Sender_Service_Area_Code,
R.City_Nam Receiver_City,R.St_Prvnc_Cd Receiver_State,R.cntry_id Reciever_Country,
R.Postl_Cd Receiver_Postal, R.Svc_Lvl_Cd Receiver_Service_Area_Code,
U.Frst_Nam||' ' ||U.last_nam Name,
S.STR_ADDR_1 Sender_Street_Address1, S.Str_Addr_2 Sender_Street_Address2
from shpt SH
Right Outer Join shpt_addr S
on sh.shpt_id = S.shpt_id
Right Outer Join shpt_addr R
ON S.shpt_id = R.shpt_id
Right Outer Join Users U
ON SH.User_Id = U.User_Id
Right Outer Join Cust C
On S.Cust_Nbr = C.Cust_Nbr
where S.ADDR_TYP_CD = 1 and R.Addr_Typ_Cd = 2
and c.creat_dt between trunc(sysdate) and trunc(sysdate-90)
group By S.Cust_Nbr Desc
Having Count(sh.shpt_id >= 25)
|
|
|
|
|
Hi, what kind of Error do u get??
|
|
|
|
|
Error i get is ORA-00933: SQL command not properly ended.
There is some mistake in the query which i am not getting.
Plz help me out.
|
|
|
|
|
Hi, try this please maybe the syntax problem is in the Having count-->
select R.City_Nam Receiver_City,R.St_Prvnc_Cd Receiver_State,R.cntry_id Reciever_Country,
R.Postl_Cd Receiver_Postal, R.Svc_Lvl_Cd Receiver_Service_Area_Code,
U.Frst_Nam||' ' ||U.last_nam Name,
S.STR_ADDR_1 Sender_Street_Address1, S.Str_Addr_2 Sender_Street_Address2
from shpt SH
Right Outer Join shpt_addr S
on sh.shpt_id = S.shpt_id
Right Outer Join shpt_addr R
ON S.shpt_id = R.shpt_id
Right Outer Join Users U
ON SH.User_Id = U.User_Id
Right Outer Join Cust C
On S.Cust_Nbr = C.Cust_Nbr
where S.ADDR_TYP_CD = 1 and R.Addr_Typ_Cd = 2
and c.creat_dt between trunc(sysdate) and trunc(sysdate-90)
group By S.Cust_Nbr Desc
Having Count(sh.shpt_id) >= 25
|
|
|
|
|
Hi,
I didn't work on Access for a very long time.A customer gave me an Access application.He wants me to make a User/Administrator web application depending on that Access file.How could I see the code reading from the tables?It works as an EXE application.
Another problem is Iam trying to migrate it to SQL database.Some tables have more than 20000 records so their migration take a very long time(3 hours at least).Is there any wayy to finish this faster?
I am too late but i will never give up
|
|
|
|
|
TheEagle wrote: Some tables have more than 20000 records so their migration take a very long time(3 hours at least).Is there any wayy to finish this faster?
Is it possible the database is not normalized?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
hi,
i have a table, i query it and i get a row(or more).OK
how can i return a value in the left or right of a particular column(cell)?
for exemple, i have table that has some columns with same kind of data ( race standings: 1st,2nd,3rd,4th...), so i query for Hamilton in any of the columns(standings).
if hamilton is in column 3rd, how can i get the value in the column in the left or right? is it possible?
i am trying to avoid typing larger code here!!! 
(this is an exemple it has nothing to do with F1 ok  )
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
When you query for "Hamilton" you should get the columns ordinal position/index. The simply get the previous/next column
Set the column index to i and:
Dataset.Tables(0).Rows(2)(i + 1)
[edit]
Oops - wrong forum.
Assuming you know the name/index of the column with "hamilton"
You can apply the same logic to a sql construct. Look into the schema information views (system views), this will give you the ordinal position of the column you found and simply increment the ordinal position to get the column name. This will require a lot of dynamic SQL
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No oops, you are in the right forum.
No C#!!!
I usually do "everything" in sql and trigger in c# events, i d´ont filter/sort in datagridview (just small things), i just throw info msg´s in c# like OK:row added,deleted,edited ERROR:whatever...
I thought on that idea during a work, didn ´t need it but...maybe i will need on later works.
Thanks
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hamilton will always be in the 1st column, so simply query for the 2nd. 
OK, OK...
It sounds like you need a more flexible database design; rather than
Race, 1stDriver, 2ndDriver, 3rdDriver, ...
Have something more like:
Race, Place, Driver
|
|
|
|
|
the design/strucure is fine, all queries get what i need.
this was an exemple like i said before, sql is finished.
But during the work i thought on that idea, maybe i could/need it on later works. 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes FROM TableName start, TableName end WHERE start.ID = end.ID AND start.Status = 'Ready' AND end.Status = 'Completed'
Ready-Complete ok, but I want one more condition added to it, I need time between Date_Timeworked when status is Ready and Rejected?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
