|
That's why you need a index on the column you order by, it's already sorted. The optimizer will take care of the inner query.
Do you have an index on the column you're sorting by?
|
|
|
|
|
Hi,
If you do have an ascending surrogate key, one efficient way would be to use that. For example if you have a field called Id and the values are coming from ordered sequence your query could be something like:
select *
from ProductList a,
(select b.Id,
count(*) over (order by b.Id) as runcount
from productlist b
where b.Id > :lastFetchedId) subq
where a.Id = subq.Id
and subq.runcount <= 10
|
|
|
|
|
Hi Mika,
I done similarly in View. Add a new column unique ID and I am adding rownum. In this case I am executing query without any delay.
But one problem is there. In case if order by condition added the output of query is coming wrong(Bug) or a block of rows is order.
Regards,
Nanda
|
|
|
|
|
Hi,
Nanda_MR wrote: I am adding rownum
Just to check: Did you notice that I didn't use rownum at all because it's unreliable in this context (for many different reasons). The example I created shouldn't encounter any of the problems rownum has since the example is based on a running total count on an unique and ascending field.
|
|
|
|
|
Dear All
I have a form "report_test", and a single button "rptstart", when i click that button, i wrote a code, to start a report in printing preview with some viewing some records.
When i click print (printer icon) on the left top corner, the printer prints the form "report_test" with the button but not the actual report am viewing any body can can guide how to avoid this and print the report itself ???
regards,
0 will always beats the 1.
|
|
|
|
|
I am currently trying to code a variable into a SQL string that is used with ADO to create a record within a database. I though am having problems with the proper syntax for a variable. The string works fine with a number but when I try to code a variable I have problems. I need to find out how to code an array as well.
hr = rec2->Open("INSERT INTO mytable(id)VALUES("+ text +")",con2.GetInterfacePtr(), ADODB::adOpenForwardOnly, ADODB::adLockReadOnly, ADODB::adCmdText);
|
|
|
|
|
in SQL string literals need quotes, so I would suggest you try:
...VALUES('"+ text +"')"...
^ ^
Warning: make sure not to insert anything between the single and double quotes there!
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Luc,
Thank you for your reply.
|
|
|
|
|
You're welcome.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Luc,
I am getting the error message: "error C2110: '+' : cannot add two pointers"
|
|
|
|
|
one cannot add pointers together, it doesn't make sense; adding house numbers wouldn't make sense either.
What is your code, and what is the exact line where the error occurs? and what language and IDE are you using?
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Luc,
The error is occuring at VALUES('"+ text + "'). My VS2008 is giving me this error. I am trying to send a record to MS Access.
|
|
|
|
|
You need to enclose your varchar value in single quotes.
hr = rec2->Open("INSERT INTO mytable(id)VALUES('"+ text +"')",con2.GetInterfacePtr(), ADODB::adOpenForwardOnly, ADODB::adLockReadOnly, ADODB::adCmdText);
I find string.Format() to be VERY useful!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Suppose some one purchased a ticket online. [Here the value of a column is pending]
If the person does not pay the cash money within 24 hours than the ticket will be automatically cancelled. [The value of the status column will be cancelled after 24 hours]
So,
How can I do automatic update of a table after 24 hours???
|
|
|
|
|
Easiest way would be to run a job[^] at a specified interval that checks the table and does whatever it needs to do.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
You have done this using sql server. But How can I do this for oracle??
|
|
|
|
|
AhmedMasum wrote: Suppose some one purchased a ticket online. [Here the value of a column is pending]
If the person does not pay the cash money within 24 hours than the ticket will be automatically cancelled. [The value of the status column will be cancelled after 24 hours]
It's not pending, it's reserved from that moment forward. Otherwise you could run into the problem that there's more pending than there's actually available.
AhmedMasum wrote: How can I do automatic update of a table after 24 hours???
I'd run through it once an hour, processing the valid payments (sending out a ticket to an address) and the invalid ones.
I are Troll
|
|
|
|
|
I wouldn't; I'd design the system so that that isn't required. The Pending column should be a date column that is set to the time when it will expire if not Purchased. Then, when seeking available tickets, look for any where the Pending field is null or less than the current time and Purchased (another date field) is also null.
Or name them Ordered and Purchased or something, but I wouldn't put them in a single Status column, Status could be calculated from a number of fields, including Shipped.
|
|
|
|
|
Thanks for your nice suggestion!!!.
My above case is just an example.
Actually I need something that will do insert/update/delete on a table every week or after a specific period of time.
Is there any type of trigger can do this type of operation??
|
|
|
|
|
Use a Windows Service (for very frequent actions) or a Windows Scheduled Task (for less-frequent actions).
|
|
|
|
|
Hi
I have not installed mysql on my own, its the default one that is coming with fedora14 at /usr/local/mysql
I am giving you some screenshots, please check if you can find out something.
*********************************************************************************************************************************************************
The first issue is while I login using command
mysql -u root -p
it asks for password I enter, root123
then gives the error
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
let me give you the full flow:
I am using fedora 14
_________________________________________________________________________________________________________
in root I fire the below commands:
[starting the mysql]
[root@chandan init.d]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
now from my local account I try to login to sql,
[chadan@chandan ~]$ mysql -u root -p
Enter password:
[i give password as root123
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
But when I login using command
[chadan@chandan ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.51 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[here I am able to login to server but when I try to create database then it gives error]
mysql> create database dummy;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'dummy'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
**********************************************************************************************************************************
NOW THE SECOND ISSUE WHEN I START THE SERVER IN DIFFERENT WAY.
[root@chandan init.d]# mysqld_safe --skip-grant-tables &
[1] 29006
[root@chandan init.d]# 110219 18:33:25 mysqld_safe Logging to '/var/log/mysqld.log'.
110219 18:33:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Now from my account I try to login
[chadan@chandan ~]$ mysql -u root -p
Enter password: fsklfjfljf
here whatever password I give I am able to login
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
now when I check mysql database
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.01 sec)
mysql> select host,user,password,Insert_priv,update_priv from user;
+---------------------- +------+---------- +-------------+-------------+
| host | user | password | Insert_priv | update_priv |
+---------------------- +------+---------- +-------------+-------------+
| localhost | root | root123 | Y | Y |
| username.corp.org.in | root | root123 | Y | Y |
| 127.0.0.1 | root | root123 | Y | Y |
| localhost | | | N | N |
| username.corp.org.in | | N | N |
+----------------------+------+----------+-------------+-------------+
5 rows in set (0.00 sec)
[here you can see that the password for root user is root123,
now here if i try to create database i am able to do that
mysql> create database dummy;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dummy |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
I dont know how to solve this also I tried to create another user but it denied
mysql> CREATE USER 'seen' IDENTIFIED BY 'seen123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
|
|
|
|
|
Check this[^], looks like it could be that the root password could be incorrect. The MySQL root password could be different to your OS root password.
It could also be that there is a port conflict which is why it works when you start it in a different way.
|
|
|
|
|
hi..
i've created an report in which i want to change the color of
the particular word i've four words which i want to change the
color
john,steve,mike,and danial..
for these name where ever they appear in the row the color should change
is it possible..
??
|
|
|
|
|
Hi,
You can use BackgroundColor-property to change the color and define an expression how the color is set. What comes to the color itself, you can for example resolve the color in the formula or add an extra field to your query which defines the color, like:
SELECT ...
CASE
WHEN LOWER(table.NameField) LIKE '%john%' THEN 'Red'
WHEN LOWER(table.NameField) LIKE '%steve%' THEN 'Blue'
...
ELSE 'White'
END AS BackgroundColor,
...
FROM ...
Now each row should have a color field which you can use in the expression for the BackgroundColor-property. If you want to do this in more elegant way you can create a table for the name/color pairs and use that table for lookups and possibly create a small T-SQL function for the logic.
|
|
|
|