How Can I Replace The Minus-Statement

Mar 23, 2006

Hi,

I've to translate this SQL-Statement from ORACLE to SQL-Server. But I'm missing the Minus-Statement on SQL-Server.
select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
minus
select tab_name from data_dic
)
minus
select tab_name, col_name from data_dic
what can I do to run it on SQL-Server.
Thanks in advance
Raimund


View 4 Replies


ADVERTISEMENT

Replace In Select Statement???

Jun 13, 2004

Hi ;)

I have a select statement where i need to Replace some Chars
Maybe someone can help

SELECT * FROM reguser WHERE REPLACE(" & whereTable & ",'-','')

I want to Replace the "-" and the "/"

Thanks in advance

View 4 Replies View Related

Replace And Trim Statement

Jan 2, 2002

I have a SQL table which has field data like below

field a field b
123 KB 246 kb

How can I strip out just the numbers(there will always be a space between the number and KB?
Thanks

View 3 Replies View Related

Replace Function In Sql Statement

Oct 15, 2007

how should i use replace function in sql statement??

in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.

my stored procedure accepts two parameters.
Is there any way to solve my problem??


Jaimin

View 4 Replies View Related

Replace() In Update Statement

Jan 22, 2008

Hi,

there is a table "attribute_definitions"

Field Name Type Size Key--------------------------------------------------
attr_name char 25 PK
attr_value nvarchar 250 PK
comment nvarchar 100

the records in the table are

attr_name attr_value comment------------------------------------------------------------------
mail dasari.kanth@gmail.com nothing
mail xyz@gmail.com nothing
UID test123 nothing
--------------------------------------------------------------------
now i am trying to update this table with the below UPDATE command

update attribute_definitions set attr_value=replace(attr_value,'gmail','yahoo') where attr_name='mail'

but the above update statement giving the error like
"Violation of PRIMARY KEY constraint 'PK_attribute_definitions'. Cannot insert duplicate key in object 'attribute_definitions'."

please can any one help me [?]

View 3 Replies View Related

SQL Statement To Replace Texts In A Cell

Jul 27, 2006

When I import my tables from MySQL, it put "
" with the contents in
the field. Will someone show me a SQL statement that I can use to clean
all the "
" out of the table's contents?

Thanks!

View 4 Replies View Related

SQL 2012 :: Replace Statement On IP Address

May 19, 2015

I am having trouble using the replace statement on IP Addresses. I want to replace the ending with ".0" - not matter what the IP Address starts with.

175.139.45.127
175.139.45.12
175.139.45.1
10.10.10.100
10.10.10.10
10.10.10.1

View 9 Replies View Related

Updating Trigger Problem : Trying To Replace The Values From The Upadate Statement

Apr 25, 2007

I am having an issue when trying to do something that looks simple on the face of it.. I want to replace the value during update in the same table.



Here's my situation



tst_update

ID int

Col1 varchar(10



I want to ensure that everytime Col1 is updated to "A" I want to set it to "X". Otherwise leave it as is.



Can someone help.



Thanks,

Ashish

View 6 Replies View Related

SQL Server 2012 :: How To Replace Multiple Values In Single Select Statement

Aug 18, 2015

how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.

DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))

[code]....

View 7 Replies View Related

Minus The Weekend

Jan 18, 2005

If I was asked "How many days passed" the query is (below) and the answer is 33. But, then the curve ball is "can you subtract out the weekends?" and I said....well, I said I think so, but not sure how. So far, I have had no luck. Any advise?



SELECT
DATEDIFF ( dd , dbo.table_case.creation_time , dbo.table_close_case.close_date ) AS no_of_days,
dbo.table_case.id_number,
dbo.table_case.creation_time CreateTime,
dbo.table_close_case.close_date CloseDate

FROM
dbo.table_case,
dbo.table_close_case

WHERE
dbo.table_close_case.last_close2case=dbo.table_case.objid AND
dbo.table_case.id_number = '969382'

ORDER BY no_of_days ASC

View 2 Replies View Related

Minus Sign ()

Jun 2, 2007

Hello:



I need to change minus brackets () with - sign in SSRS. How i can do this?



Thanks

Amit

View 11 Replies View Related

SELECT * Minus A Field

Jul 26, 2007

Hi,

I was wondering if anyone knows a command that will select all fields in a table except one?

Thanks!
Lee

View 6 Replies View Related

Minus And Intersect Functionality

Jul 20, 2005

Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth

View 1 Replies View Related

Oracle MINUS Equivalent In Sql Server

Apr 22, 2003

Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks

View 11 Replies View Related

SQL Server Express Edition - Plus And Minus

Jul 25, 2005

I would like to get a feed back from people who using Express edition.

What are the Plus and Minus points which you can list agenst Express Edition of MsSQL !

Please list them from your experiance rather than the documentation!

NB:- Specify : OS - Development Platforms - If any installation problems found in your Desk.

Thank you.

Thank you.

View 5 Replies View Related

Import Data Minus Duplicates

Feb 20, 2008

Hi

I am currently trying to import data from a table in 1 database into a table of the same name in another database. This in it's self is simple, however to add a twist to the proceedings there is data that exists in both tables. I just want to import the data that doesn't exist in the table I am importing into.

Please can you advise as to the best method to use

Many thanks

Paul

View 5 Replies View Related

Transact SQL :: Can Add 1 To Charindex Function But Can't Minus 1

Aug 25, 2015

The charindex can run this

LEFT([Description], CHARINDEX('(', [Description]) + 1)  as NewDesc,

But I can't run

LEFT([Description], CHARINDEX('(', [Description]) - 1)  as NewDesc,

View 5 Replies View Related

Add A Minus To Two Column Values If Criteria Met

Mar 7, 2008



Hi I was hoping you could help,


I have a query that pulls back a customers account statement, however the credits are coming back as positives as well, So I am getting wrong figures, how can I correct the code below so that VAT, Goods Value and Total are negative if the data in column "trans reference" begins with a C for example C123456789 a invoice would be 123456789



Thanks








Code Snippet
SELECT
SLCUSA as [Compny Number],
SLCUSB as [Customer Suffix],
SLDELN as [Deliber To Number],
SLCNAM as [Customer Name],
SLDATE as Date,
SLTREF as [Trans Reference],
SLGDSV as [Goods Value],
SLVATV as [VAT],
SLTOTV as Total
FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR
WHERE (SLCUSA = 2) AND (SLCUSB = 1007)
END







View 11 Replies View Related

SQL Server 2012 :: Adding Certain Rows And Minus Value With A Row

Nov 25, 2014

I got a table where i need to add certain rows and minus the valu with a row.

IDC1C2C3C4C5C6
1551557775
266201452
345222266
441727582
532556951
6022022
7182344142
8

Result in the 8 Row

Sum(id(2,3,4)-sum(id(6,7))

View 2 Replies View Related

Transact SQL :: Does Server Support MINUS Keyword

Mar 22, 2006

I have two tables CarType & Cars.  Table CarType has a column CarTypeId which is the primary key (int, identity).  Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).I read that there is an SQL "MINUS" keyword that you can use like this:

SELECT CarTypeId FROM CarTypeMINUSSELECT CarTypeId FROM Cars..So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}.  (Note:  I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).I tried it in SQL Server 2005 Express.  The result is just {1, 2, 3, 4, 5}.

Is the MINUS capability supported by SQL Server?  I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server..

Here's a reference to the website where I initially found out about MINUS: [URL] ....

View 19 Replies View Related

SQL Server 2012 :: If Minus Figure Pull Back 0

Feb 6, 2015

Have the following in my SELECT Stataement

CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date)
ELSE NULL
END AS 'DaysOverTarget'

Some of the figures coming back are minus figures. How could I get the minus figures reported to be 0.00?

Below is the full TSQL

SELECT DISTINCT com.comm_reference AS 'Referance'
,com.crt_date AS 'CreatedDate'
,com.current_task_target_date AS 'TargetDate'
,com.completion_date AS 'CompletionDate'
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.crt_date,com.completion_date) - non.NoWorkDays

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Keep A Running Total Of OnHandQty Minus OrderQty

Aug 26, 2015

I have been tasked with writing a report that shows all open orders for an item and their quantities, along with a running total of what is left in stock. We start by building these two tables:

IF OBJECT_ID('tempdb..#Orders', 'U') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Warehouse', 'U') IS NOT NULL DROP TABLE #Warehouse;
CREATE TABLE #Orders (OrderDate DATETIME, JobNumber NVARCHAR(10), Item NVARCHAR(20), QtyOrdered NUMERIC(10, 2))
INSERT INTO #Orders SELECT '20150801', 'JOB1', 'Widget1', 5
INSERT INTO #Orders SELECT '20150802', 'JOB2', 'Widget1', 3

[code]....

View 8 Replies View Related

Using Expression Builder To Access A Dialy File Minus One Day

Jan 23, 2008

Hello,

I am using the following expression to access a daily file.

"D:\importdata\peregrinedata\ACD_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

output: D:importdataperegrinedataACD_DATA_2008123.txt

I need to have this file by current day -1. I've tried dateadd and can't figure out how to get it to work.

Thanks

View 3 Replies View Related

How To Create Parameter: =Today() Minus Some Number Of Days

Jan 3, 2008

SSRS 2005 on XP SP2

How does one format a date time parameter as, for example, today's date minus 7? Attempting to use:
=Today() - 7 throws the error "Operator '-' is not defined for types 'Date' and 'Integer'

Thanks in advance

View 6 Replies View Related

Data Access :: Order By And Minus In Char Fields

Apr 29, 2015

I have a test query that i have ordered by the expected result order.

select t from (
select cast('1' as nvarchar(20)) as t
union all select '1---qa'
union all select '1q'
union all select '1q1'
union all select '1qz'

[Code] ....

and the question is... why is minus character (at 3rd line in '1---qa' string) ignored by "order by" clause? That row is put after '1q1' and not after '1'.

When you replace '-' with '+' or any other special char it works as expected but not with '-'. It is just ignored no matter if there is one or more of them.

Don't mind local chars like čž, they are for testing purposes with collate (thus commented) and with bin collation that is not doing proper sorting but minus order is ok in that case.

Tested on SQL 2008 R2 and SQL express 2012, database collation is 'Croatian_CI_AS'.

View 4 Replies View Related

Stored Procedure:calculating Minus Dependent On Type With Group By

Aug 6, 2004

Hi i am trying to get the following result:
CALCULATE THE CURRENT NUMBER OF SHARES on base of number of shares bought and number of shares sold

so the logic is
int total
if transactiontype is "buy", total = total + amount bought
if transactiontype is sell, total = total-amount sold
plus must be grouped by share

base data are stored in 1 table as
share name transaction type share amount
tesco buy 200
tesco sell 50

sounds rather easy does it not?
well sql syntax must be very foreign to me as i do not manage it
I tried a few options:
1 to write a database function as above and then call it from sproc
but i need to use group by share name or id so it calculates for each share and it will not let me to use function in select with group by
2 i tried to use if within sproc but i get complain incorrect syntax (I use MSDE database accessed via Visual) - I think it suppose to be possible but it throws me out with the most simple if or = is there some special syntax?
3 I created 2 views: 1 view: all sold shares, 2nd view: all bought shares and their bought/sold quantities
as in
select share name, sum(sharequantity)
where transaction = sell
from table
group by share name
and the same for purchases
then I did union of these 2 views with boughtquantity - soldquantity what worked but it only showed me shares which were sold, if share was never sold it was not shown I guess due to union
so I am still stuck!

View 7 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

Select A Date Minus Whatever Todays Date Is?

Feb 15, 2008

At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.

The statement in my query I have to use is:

MailDate <= '2008-01-05'

I am wondering if i could make that statement automatically take off 42 days from todays date?

View 8 Replies View Related

Table1 Data Minus Table2 Data

Mar 21, 2008

Hi,

I am using SQL Server 2005 and would like to get all the rows that exist in
(Table1 - Table2) without the answer being distinct, what do i do?


Example:

Table1:
Products: A, B, C, D, D, D, E


Table2:
Products: A, C


select * from Table1
except
select * from table2


will give me: B, D, E


But i want it to give me: B, D, D, D, E


Any way that would work will be helpful. Thanks in advance.

View 11 Replies View Related

Strange Result - Minus Result -1

Mar 2, 2008

help strange result whan i do this



Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList




i try to get next unit value to next month
why i get this -1
on date




01/01/2008
1
-1

unit_date unit new_unit



01/02/2008
2
1

01/02/2008
1
4

01/01/2008
1
-1

01/02/2008
1
4

21/01/2008
1
-1

21/01/2008
1
-1

01/02/2008
1
4


TNX

View 3 Replies View Related

Moved Stock Minus In Item Table To Stock In Itemmoment Table

Sep 11, 2007

 helo all...,i want to make procedure like:examplei have table: item (itemid,itemname,stock)orderdetail(no_order,itemid,quantity)itemmoment(itemid,itemname,stock)item table itemid    itemname    stock  c1        coconut         2  p1         peanut          2orderdetail tableno_order        itemid        quantity   1                  c1                5itemmoment tableitemid    itemname    stock  c1       coconut          0  p1       peanut            0 when customer paid, his quantity in orderdetail decrease stock in item table..so stock in item table became:itemid        itemname    stock  c1            coconut         -3  p1            peanut           2it's not good, because stock may not minus...so i want to move -3 to itemmoment table..so stock in item table became:itemid        itemname    stock  c1            coconut          0  p1            peanut           2and in itemmoment table became:itemid        itemname    stock  c1             coconut        3  p1             peanut          0my store procedure like:ALTER PROCEDURE [dbo].[orders](    @no_order as integer,    @itemid AS varchar(50),    @quantity AS INT)ASBEGIN    BEGIN TRANSACTION            DECLARE @currentStock AS INT                SET @currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @itemid)        UPDATE [item]        SET            [Stock] = @currentStock - @quantity        WHERE            [itemid] = @itemid    COMMIT TRANSACTIONENDit's only decrease stock with quantity. i want move stock minus from item to itemmoment..can anyone add code to my store procedure?plss.. helpp.thxx....

View 2 Replies View Related

SQL Replace '-' By ''

Oct 16, 2007

Hi all i have a question regarding sql, i want to replace some characters...
 
any knows simply how to do this?
 
I want to replace "999-25000-69" by "9992500069"
 
grtz

View 1 Replies View Related

Replace?

Nov 28, 2006

i got a 100k rows column contain first name + last name. but half of them are got comma between first and last name. how can i update and remove all the comma. can anyone provide a statment please thanks so much

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved