sql

Linq to NHibernate: Distinct

I am trying to get the following SQL output using Linq-to-NHibernate: SELECT DISTINCT Name, at.Year FROM MyTable mt INNER JOIN AnotherTable at ON at.Id = mt.AnotherTableId The Name and Year properties are going to be wrapped up in a new class, so the C# code will look something like this: Session.Linq() .Select(x => new FooBar { N...

cursor for sql server2005

This is my cursor procedure: DECLARE C1 CURSOR LOCAL FOR SELECT DISTINCT PPTR_MATCH_REF_NO, PPTR_LDGR_CODE, PPTR_SLMAST_ACNO, PPTR_PPN_STATUS FROM GLAS_PPN_TRANSACTIONS WHERE PPTR_COMP_CODE = @COMP_CODE AND ISNULL(PPTR_PPN_STATUS, 'X') = 'V' DECLARE @MATCH_REF_NO NUMERIC(10,0), @LDGR_CO...

SELECT all the newest record distinct keyword with a non null value in one column

Hello again... Following on from this question http://stackoverflow.com/questions/1740199/select-the-newest-record-with-a-non-null-value-in-one-column I know have a problem where I have this data id | keyword | count | date 1 | ipod | 200 | 2009-08-02 2 | ipod | 250 | 2009-09-01 3 | ipod | 150 | 2009-09-04 4 | ipod | NULL | 2009-09-07...

Inserting new columns in the middle of a table?

When one uses "ALTER TABLE tab ADD col", the new column gets added to the end of the table. For example: TABLE: TAB COL_1 COL_2 COL_4 ALTER TABLE TAB ADD COL_3 table will become TABLE: TAB COL_1 COL_2 COL_4 COL_3 However as the naming of my example columns suggests I'd actually like the table to end up like this: TABLE: TAB COL_1...

Combining tables in sql/qlikview

Is it possible to combine 2 tables with a join or similar construct so that all non matching field in one group. Some thing like this: All employees with a department name gets their real department and all with no department ends up in group "Other". Department: SectionDesc ID Dep1 500 Dep2 501 Employee: Name ...

What is the best approach to writing a stored procedure for a 1:N relationship performing an insert?

If I have two tables A and B: A(AId, a1, a2) B(BId, AId, b1, b2, b3) On first thought , I wanted to write a sp (stored procedure) incorporates the two insert statements. But, on a second I thought I felt it would be nicer if I had a separated sp to do an insert for the table B and another to do an insert for table A while calling the ...

Informix SQL text Blob wildcard search

I am looking for an efficient way to use a wild card search on a text (blob) column. I have seen that it is internally stored as bytes... The data amount will be limited, but unfortunately my vendor has decided to use this stupid datatype. I would also consider to move everything in a temp table if there is an easy system side function t...

Extending a stored procedure (adding a single part to it) by using a parameter in SQL Server

I have a 200 line long stored procedure, which gets a parameter 'prmtr', What I want to do is add an "sql part" to my stored procedure, according to my parameter. example: SELECT A.* FROM ( SELECT * FROM table1 ) A IF (my parameter) = a LEFT JOIN ( SELECT * FROM table2 ) B ON A.ID= B.ID ...

Fix ORA-02273: this unique/primary key is referenced by some foreign keys

Trying to drop a unique constraint I've got such error: ORA-02273: this unique/primary key is referenced by some foreign keys How to find the list of foreign keys by which my unique constraint is referenced? ...

How can i write stored procedures in my application using SqlHelper?

I'm looking at my options here. I would love to know how to write my stored procedures not in the SqlServer but in my app (DB Layer) using SqlHelper? Can anyone help? ...

A select question

There are groups like this; USER_ID SEQ_ID NAME 1 2 Armut 1 3 Elma 1 4 Kiraz 2 1 Nar 2 2 Uzum 4 3 Sheftali 4 4 Karpuz 4 5 Kavun After select query I want to see only; USER_ID SEQ_ID NAME 1 2 Armut 2 1 Nar 4 3 Karpu...

Do you use the OUTER keyword when writing left/right JOINs in SQL?

I often see people who write SQL like this: SELECT * from TableA LEFT OUTER JOIN TableB ON (ID1=I2) I myself write simply: SELECT * from TableA LEFT JOIN TableB ON (ID1=I2) To me the "OUTER" keyword is like line noise - it adds no additional information, just clutters the SQL. It's even optional in most RDBMS that I know. So... why...

How do I do a SQL BETWEEN where the date and time are stored seperatly as integers.

At the company I work for date and time values have always been stored separately in integer fields, so for example 8:30 this morning would be stored like this: date of 20091116 and time of 83000 (no leading zeros as it is an integer field) Whereas the time as I type this the time would be stored like this date 20091116 time 133...

OLEDB CASE WHEN in SELECT Query

how can use case when statement with oledb to excel file using vb.net 2.0 like select prodid, case prodid when 1 then 'fine' when 2 then 'good' end ...

Is there a faster way to count records on a join...

This query gets me the profiles with the most evidence records when the profile is the actor. Is there any way to make it faster in a mysql query? SELECT profiles.*, count(*) AS counted_profiles FROM `profiles` INNER JOIN (SELECT ev.actor_id FROM evidences AS ev WHERE ev.actor_type = 'Profile') AS ev2 ON ev2....

How to put row number for sql query in sql 2000 where rownumber() is not supporting?

How to put row number for sql query in sql 2000 where rownumber() is not supporting? ...

AUTO-Parametrized multiple SELECT

hi everybody i need to make a select to find out X then i need to use X to find out Y then i need to use Y to find out Z is there a way for making it with a single "auto-parametrized select"? like this fanta-pseudo-code: select Z from ( select Y from (select X from x_table) ) tnx a lot! ...

I want to import data from my sql proc to csv file

hi, I am using sql server 2005, I want create sql procedure will save data into csv file. Below is the qurey I am trying but it is not creating any file in my system. use PBMS_DocumationWorkflow go create proc s_bcpMasterSysobjects as select '"' + name + '"' + ',' + '"' + convert(varchar(8), crdate, 112) + '"' ...

SQL - Is there a better way of passing a list of keys, for use in a where clause, into a Stored Procedure?

Here's the scenario; I have a list of CustomerIds (1, 2, 3) that have relating OrderIds. I have one Stored Procedure Delete_OrdersByCustomerIds, which deletes all the orders that are related to the CustomerIds specified. Currently, the way I do this is to concatenate the CustomerIds into a string, i.e. "1,2,3". I then pass this string t...

MySQL - explode/split input to stored procedure

I have problem, I need to explode my input to my stored procedure, but don't know how I can do it. My stored procedure has a VARCHAR(256) input which I need to split and generate insert statements. i what to explode this varchar "1,2,3,7,8,9" so I need to split that string on "," and iterate through the result ...