Replace

Question

Post reply

Replace

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 1

Please provide me the condition on which your filter statement will be .

Lets say

condition 1

filter should be $filter = '1'

condition 2

filter should be $filter = '2'

then I can make the statement for you.

Thanks,

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 2

shobanapraveen

SSC-Addicted

Points: 459

October 3, 2013 at 12:13 am

#1655400

we go with condition 1..

Thanks

Shobana

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 3

Hi,

Sorry you didn't understood my question.

Just provide me original query and let me know what condition you want there in "Where" condition then I can help you.

Thanks,

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 4

select distinct split_part(split_part(cp.msg, 'src=',1),',',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 am.src='192.168.10.23' and replace('$filter', 'itime', 'am.itime')

This is my original query.................

Thanks,

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 5

Ok what you actually trying to do by this:

replace('$filter', 'itime', 'am.itime')

?

It may be some condition like itime>=something or item<=something right ?

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 6

exactly.

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

this query has to generate 1hr report..

Thanks,

Shobana

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 7

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 🙁

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 8

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:blink:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 10

shobanapraveen

SSC-Addicted

Points: 459

October 3, 2013 at 3:40 am

#1655471

yup

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 11

Is it possible to use webserver and get connected to DB to get quick results

Thanks,

Shobana

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 12

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.

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 13

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

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 14

shobanapraveen

SSC-Addicted

Points: 459

October 3, 2013 at 3:57 am

#1655482

what is highlight?

Thanks,

Shobana

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 15

remove this from your code

[highlight=#ffff11]

and

[/highlight]

This is added by SQLServerCentral's editor

Use constants instead of subquery.