Recent content by pascalsql

Another suggestion, ugly but acceptable if this is a one shot. Extract the distinct values of Nabp, RxNbr and RefillSeq Find out how many occurences each and which is less discriminant (ex Nabp has 100 distinct values) create a temp table with only two other columns ie RxNbr and RefillSeq and...

BTW, allways avoid what looks like a reserved word (here a table named [case] ). Some features, such as the one creating web pages (sql7) don't automatically generate the brackets, ending up in error hard to debug. Even the too-often seen column DATE should be avoided. There are so many...

Try this: select VB.vehicle_id, min(VB.date) beg_date, VB.odometer beg_odo, VE.date end_date, VE.odometer end_odo from vehicle_history VH join vehicle_history VE on VE.vehicle_id = VB.vehicle_id and VE.date = (select max(date) from vehicle_history V3...

comment out the insert line and replace the selected columns with count(*) That will indicate how many rows you are about to insert. SELECT COUNT(*) FROM aSph_IMSrawEquipImport AS NEW,RM00101,RM00102 WHERE NEW.NO_CONTRAT <> 0 OR NEW.NO_CONTRAT IS NOT NULL AND NEW.NO_CLIENT =...

Try this ??? SUBSTRING(column_reference or string constant FROM start_index [FOR length]) POS(string constant IN column_reference or string constant) REPLACE(string constant WITH new string constant IN column_reference or string constant) Tip: search on the web 'dbisam susbtring' to find a...

Hmmm, I'm not sure about my answer but as you don't get any yet... SQL Server is not designed to handle delete in text files, it would break it's own business model ;-) Assuming the AS400 fills the text file, it probably creates it if it does not exist, under a known name, say AS400file.txt...

>Why have you used IS NULL? Because null acts as a black hole for concatenation when you add something to null you get null. Just as if uou want to multiply all values in an excell column. If only one is zero, the result is zero. And at start the variable @clients is null (unless you...

Try this: ALTER PROCEDURE procTest ( @p_loan_number varchar(10) ) AS begin SET NOCOUNT ON if exists (SELECT 1 FROM dbo.t_loan WHERE dbo.t_loan.loan_number = @p_loan_number ) then select dbo.t_loan.loan_number from dbo.t_loan else...

We probably can do better using the seldom-used function reverse. Something like: reverse the string add a column every 3 character until the end of the string reverse again But this job is probably best done by the client interface

SQL can do all, including fair trade coffee ;-) You need two tables, one is you phone-book: create table phonebook ( name varchar(20), phone_number varchar(20), status varchar(10) -- private, prof... ) insert phonebook values 'sweetie', '12345', 'private' insert phonebook values 'Big Co...

The only possible way would be to set a trace in the profiler (or third party tools) and extract the queries. But it will turn out to be much more complicated than replication. Consider instead: - Implement a trigger - Encapsulate the changes in proc and deny access to anyone else - Search for...

Solution 1: Create a package in DTS to handle the changing layout. Solution 2: Create a temp table to import your data with an order_id identity field. This field is important to keep track of order as it is the only way to link type 6 rows with their corresponding type 5 rows. You can use...

Can you have many pairs of columns, such as cnt1 Type1 cnt2 Type2 cnt1 Type1 cnt2 Type2 ... ----- ---- ---- ----- ----- ---- ---- ----- 1 5 2 5 3 5 4 6 5 7 6 8 7 5 8 6 ... What is your goal? It seems your design is quite bizarre.

Use substring to extract the prefix of your table. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'tblArchive%' AND cast(SUSTRING(TABLE_NAME, 11, 3) as int) <= datepart(dy, getdate())-60 You still need a cursor... declare @sql_query varchar(20) DECLARE arc_cursor...

SQL Server is not a client interface. It's not its duty to create a nice prompt with blinking color and basic check. What is your client (access, vb, asp...)? Also search &quot;raiseerror&quot; to check the validity of the parameters and return an error