Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Replace Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 2:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
exactly.


if the user wants the reports for 1hr...

this query has to generate 1hr report..


Thanks,
Shobana
Post #1501063
Posted Thursday, October 3, 2013 3:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:37 AM
Points: 1,153, Visits: 83
If you have two different conditions like am.time <= and am.time>= then better to write if else statement and copy paste your query with required modifications.

I am not able to help you as I still could not understand your requirement.

Sorry
Post #1501067
Posted Thursday, October 3, 2013 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
Thanks a lot for your help

Replacement every thing over. Now if i run the below query its querying for long time due to huge datas in DB(PostgreSQL). if i run same query outside i am getting test data's quickly. Now tell me how to get quick datas.

Since here we are splitting,joining, comparing,with the the timestamp we have to give the result.

So it takes time to pull the datas.............


DROP TABLE IF EXISTS OwnerMessageList1;
CREATE TEMPORARY TABLE OwnerMessageList1 (
id serial,
name varchar(100)

);

INSERT INTO OwnerMessageList1
(name)
select unnest(string_to_array(replace(replace('$filter', 'itime >= ', ''),'itime <= ',''),'and'));

select distinct split_part(split_part(cp.msg, 'src=',2),',',1),split_part(split_part(cp.msg, 'user=''',2),',',1) as user ,am.itime,am.dtime,am.url from "FGT60B3908669009-wlog-1380091698" am join "SYSLOG-C0A80A41-glog-1380347892" cp on cast(am.src as character varying)=cast((split_part(split_part(cp.msg, 'src=',2),',',1) ) as character varying) where (cast(am.itime as varchar)<=(select name from OwnerMessageList1 where id=1) and cast(am.itime as varchar)>=(select name from OwnerMessageList1 where id=2));



Thanks,
Shobana




Post #1501071
Posted Thursday, October 3, 2013 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,864, Visits: 14,165
Is this PostgreSQL?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1501082
Posted Thursday, October 3, 2013 3:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
yup
Post #1501085
Posted Thursday, October 3, 2013 3:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
Is it possible to use webserver and get connected to DB to get quick results

Thanks,
Shobana
Post #1501087
Posted Thursday, October 3, 2013 3:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:37 AM
Points: 1,153, Visits: 83
You can directly write your value in below sub query.

This sub query may lead to some problem like multiple records in select statement error etc.


where (cast(am.itime as varchar[highlight=#ffff11])<=(select name from OwnerMessageList1 where id=1)[/highlight] and cast(am.itime as varchar)>=([highlight=#ffff11]select name from OwnerMessageList1 where id=2)[/highlight]);


Here I can suggest only this things


Hope this may help.
Post #1501089
Posted Thursday, October 3, 2013 3:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
ERROR: syntax error at or near "highlight".
LINE 12: ...character varying)where (cast(am.itime as


This is the error i am getting

Thanks
shobana
Post #1501091
Posted Thursday, October 3, 2013 3:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
what is highlight?

Thanks,
Shobana
Post #1501096
Posted Thursday, October 3, 2013 4:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:37 AM
Points: 1,153, Visits: 83
remove this from your code

[highlight=#ffff11]
and
[/highlight]

This is added by SQLServerCentral's editor

Use constants instead of subquery.
Post #1501100
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse