Hi In a stored procedure (which has a date parameter named 'paramDate' ) I have a query like this one select id, name from customer where period_aded = to_char(paramDate,'mm/yyyy') will Oracle convert paramDate to string for each row? I was sure that Oracle wouldn't but I was told that Oracle will. In fact I thought that if the param...
I've got these columns [in a single table]: partnumber bin description store manufacturer I need to output the store and manufacturer as rows for each partnumber, bin, description column. ...
HiIs it possible to write SQL query that returns table rows in random order every time the query run? ...
I'm using MySQL 5.1 with PHP and I was wondering if it would be possible to run a query that will select a specific row using the primary key, but if it doesn't exist, to return a different one. For example: select * from table1 where id="a" else id="b" ...
when i remove primary key constraint then SQL automatically remove the cluster index and same for unique it will remove non-cluster index ? ...
create table A (id int(10) not null, val1 varchar(255), primary key (id)); [a] create table B (a_id int(10) not null, val2 varchar(255), foreign key (a_id) references A (id)); [b] create table B (id int(10) not null, a_id int(10) not null, val2 varchar(255), foreign key (a_id) references A(id), primary key (id)); By choosing [a], I ca...
We need to have a semi complex report in CRM that displays some accumulated lead values. The only way I see this report working is writing a stored procedure that creates a couple of temporary tables and calculates/accumulates data utilizing cursors. Then is the issue of getting the data from the stored procedure to be accessible from ...
On a new project I work on I have data in CSV format to import into a mysql table. One of the columns is a price field which stores currency in the european format ie. 345,83. The isssue I have is storing this decimal seperator. In most European currencies the decimal seperator is "," but when I try to insert a decimal number into a fie...
I have a table which have a single field. and it have a values like (3,7,9,11,7,11) Now I want a query which will pick the value that occurred least number of times and if there is a tie with minimum occurrences then use the smallest number In this case the answer will be 3. ...
I am trying to reduce fragmentation in all of the indexes for a database running on SQL Server 2005. Currently I am trying to use ALTER INDEX in conjunction with sp_MSforeachtable, to apply it to all of the indexes for all of the tables: sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD;" But for some reason this doesn’t always seem to ...
Hi I've got a single table, with a autonumber id (called id) ranging from 1 - 159270 so select max(id) from table1 returns 159270, but I think one of the records has been deleted, is there a simple way to find out which id is missing ? Thanks ...
A rather complicated SQL query I was working on got me thinking about a limitation of (ANSI) SQL: Is there a way to retrieve a record that is maximal or minimal with respect to an arbitrary ordering? In other words: Given a query like this: SELECT * FROM mytable WHERE <various conditions> ORDER BY <order clause> is it possible to w...
Please help me correct the following query: SQL = "insert into tblContract (Empid, Start1, Finish1, Store1, " & _ "Start2, Finish2, Store2 ) " & _ "values ('" & Me.txtEmpNo.Value & _ "',select max(testid) FROM tbltesting,'" & _ Me.txtContSunStart1.Value & "', '" & _ ...
The following code snippet on SQL server 2005 fails on the ampersand '&': select cast('<name>Spolsky & Atwood</name>' as xml) Does anyone know a workaround? Longer explanation, I need to update some data in an XML column, and I'm using a search & replace type hack by casting the XML value to a varchar, doing the replace and updating ...
I need a query to create a table which is the exact replica but with different table name and without any data from the source table using a sql query! ...
I need to strip leading spaces from a column in Oracle. I've Googled but haven't found any answers except to write my own function which I'd like to avoid. What's the easiest way to accomplish this? ...
How do I stop thinking every query in terms of cursors, procedures and functions and start using SQL as it should be? Do we make the transition to thinking in SQL just by practise or is there any magic to learning the set based query language? What did you do to make the transition? ...
I've got tables like this: Table Articles --------------- AID(PK) Name PublisherID Words Table Books --------------- BID(PK) Title PublisherID Date I want to merge them like: Table Total --------------- Title PublisherID In this new table, Name in Table.Articles will belong to Title column, because.. Articles.Name an...
Possible Duplicate: Query Syntax error I want insert value using the below command DoCmd.RunSQL = "insert into tblContract( Empid, Start1, Finish1, Store1, Start2, Finish2, Store2) values ('" & Me.txtEmpNo.Value & "','" & Me.txtContSunStart1.Value & "', '" & Me.txtContSunFinish1.Value & "','" & Me.txtContSunSto...
I need to add the current year as a variable in an SQL statement, how can I retrieve the current year using SQL? i.e. BETWEEN TO_DATE('01/01/**currentYear** 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AND TO_DATE('31/12/**currentYear** 23:59:59', 'DD/MM/YYYY HH24:MI:SS') ...