Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. pascalsql

    Combining 17 tables to check for duplicates

    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...
  2. pascalsql

    insert into query problem

    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...
  3. pascalsql

    T-SQL Problem with Mileages and Dates

    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...
  4. pascalsql

    Insert w/ multiple conditions...

    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 =...
  5. pascalsql

    SUBSTRING / TRIM help?

    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...
  6. pascalsql

    SQL Server and VB.Net text file manipulation

    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...
  7. pascalsql

    Seprating Records With A Comma

    >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...
  8. pascalsql

    wtf, a simple tsql if isn't right...

    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...
  9. pascalsql

    How to format numbers (int) to separate hundreds, thousands etc with c

    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
  10. pascalsql

    Will I need vb script or SQL does it?

    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...
  11. pascalsql

    How to get INSERT &amp; UPDATE statements??

    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...
  12. pascalsql

    Hi all, I have a table (Table1)

    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...
  13. pascalsql

    Hi all, I have a table (Table1)

    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.
  14. pascalsql

    Archiving and Deleting Tables

    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...
  15. pascalsql

    Creating an input prompt in SQL Server

    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
  16. pascalsql

    dopey beginner stuck with stored procedure and job creation

    In query analyser type the following drop procedure stuff go create procedure stuff as begin insert your_table values (your_values) delete your_table where your_colomn = xxx end go Execute it (press F5) type: exec stuff Higlight this line and execute it (F5) It should run and returns...
  17. pascalsql

    Counting recordsets with no rows returned

    If there is NO record with a specific date, say june 9 2003, sql server cannot invent it. You need a table containing all the dates. This can be done with a utility calendar table that is left joined or with a sub-query. select calendar_date, (select count(*) from xxx where date =...
  18. pascalsql

    problem with dateadd sql 6.5

    Use ISO format: yyyymmdd it's unambiguous and it sorts nicely. BTW it should be the ONLY way to talk about dates, even in everyday language. As a test date, I often use Christmas : 25-12-2003 ... or 12/25/03 according on where in the world you happen to live.
  19. pascalsql

    copy Stored procedures

    Another possibility is to clone your database 1) detach it with sp_detach 2) duplicate .mdf and .ldf files in windows file explorer with different names 3) re-attach the original database 4) attach the new one with a different name and the new files. Be sure you understand how to use sp_attach...
  20. pascalsql

    Using field data to generate SQL Aliases

    You must use a dynamic query declare ... select @my_alias = <retrieve alias here> select @sql_query = 'select field1 as ' + @my_alias + ' from mytable' execute (@sql)

Part and Inventory Search

Back
Top