Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Compare two varchar-fields with two others

Posted on 2004-11-04
12
Medium Priority
?
521 Views
Last Modified: 2008-03-06
I need to set a UNLIKE filter in a procedure where I have to compare the combination of

dbo.ABRECHNUNG.symbol  (varchar, 200) &   dbo.Depot.DepotNummer   (varchar, 53)


with the combination of


dbo.StopLoss.Symbol  (varchar, 200)   &   dbo.StopLoss.DepotNummer (varchar, 53)



But by creating the first combination like

dbo.ABRECHNUNG.Symbol & dbo.ABRECHNUNG.DepotNummer AS Expr1

I get already an error saying that unvalid operator for this datatyp, Operator is boolean AND, typ is float


How to solve so I get a filter like

dbo.ABRECHNUNG.symbol &  dbo.Depot.DepotNummer  UNLIKE  dbo.StopLoss.Symbol  &   dbo.StopLoss.DepotNummer

Thx
Kongta
0
Comment
Question by:Kongta
  • 5
  • 5
  • 2
12 Comments
 
LVL 8

Expert Comment

by:SashP
ID: 12492699
Hi Kongta

dbo.ABRECHNUNG.Symbol + dbo.ABRECHNUNG.DepotNummer AS Expr1   -- & is a Bitwise logical operator

Then

dbo.ABRECHNUNG.symbol + dbo.Depot.DepotNummer  !=  dbo.StopLoss.Symbol + dbo.StopLoss.DepotNummer

Cheers Sash
0
 
LVL 8

Accepted Solution

by:
SashP earned 400 total points
ID: 12492709
Hi Kongta

dbo.ABRECHNUNG.Symbol + dbo.ABRECHNUNG.DepotNummer AS Expr1   -- & is a Bitwise logical operator

Then

dbo.ABRECHNUNG.symbol + dbo.Depot.DepotNummer  <>  dbo.StopLoss.Symbol + dbo.StopLoss.DepotNummer

Cheers Sash
0
 
LVL 8

Expert Comment

by:SashP
ID: 12492727
If this does not help post the entire query that you are using.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Kongta
ID: 12492770
Hi SashP

I am just at the beginning of my query and started with this first field and got this error. Using

dbo.ABRECHNUNG.Symbol + dbo.ABRECHNUNG.DepotNummer AS Expr1  

shows me the error 'error on converting from datatyp varchar into float' ?
0
 
LVL 8

Expert Comment

by:SashP
ID: 12492784
Can you post the table schema? Go to query analyser and type sp_help ABRECHNUNG then post the results of the first result set.

Sash
0
 
LVL 8

Expert Comment

by:SashP
ID: 12492795
Kongta,

The error that you are getting suggests that one of  dbo.ABRECHNUNG.Symbol or dbo.ABRECHNUNG.DepotNummer is not a varchar but a float datatype.

0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12492805
Why don't you try:

Select ABRECHNUNG.*, Depot.*
from ABRECHNUG, Depot
where cast(dbo.ABRECHNUNG.symbol as varchar) + cast(dbo.Depot.DepotNummer as varchar)
not in (Select cast(dbo.StopLoss.Symbol as varchar)  +  cast(dbo.StopLoss.DepotNummer as varchar)
           from StopLoss)

0
 

Author Comment

by:Kongta
ID: 12492847
ABRECHNUNG      dbo      user table      2004-10-22 10:04:23.247


Symbol      nvarchar      no      100                            yes      (n/a)      (n/a)      Latin1_General_CI_AS

IX_ABRECHNUNG_3      nonclustered located on PRIMARY      Symbol

UNIQUE (clustered)      IX_ABRECHNUNG_1      (n/a)      (n/a)      (n/a)      (n/a)      TN



this is what I get ?
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12492857
Are you asking a question?

Is this not what you wanted?

Stu
0
 

Author Comment

by:Kongta
ID: 12492994
This was the answer to SashP, but I don't know if this is what he was looking for.

SQL_Stu, with your solution I'll get more than 1 million rst back. I have following problem to solve where I have the first two stops already and now looking for the third.

I have a table 'stopLoss' where I have to delete (works), update (works) and insert into (). The two first codes look like:

ALTER PROCEDURE StopLossCalc
AS
delete a
FROM StopLoss a
INNER JOIN (
SELECT dbo.DEPOT.DepotNummer, dbo.ABRECHNUNG.Symbol
FROM dbo.WERTSCHRIFTENFUNDAMENTALDATEN RIGHT OUTER JOIN
dbo.WERTSCHRIFTENBASISINFO ON
dbo.WERTSCHRIFTENFUNDAMENTALDATEN .Symbol = dbo.WERTSCHRIFTENBASISINFO .Symbol RIGHT OUTER JOIN
dbo.ABRECHNUNG ON dbo.WERTSCHRIFTENBASISINFO .Symbol = dbo.ABRECHNUNG.Symbol RIGHT OUTER JOIN
dbo.DEPOT ON dbo.ABRECHNUNG.DepotNummer = dbo.DEPOT.DepotNummer
GROUP BY dbo.DEPOT.DepotNummer, dbo.ABRECHNUNG.Symbol, dbo.ABRECHNUNG.ValorenNummer
HAVING (dbo.ABRECHNUNG.ValorenNummer > 20) AND SUM(dbo.ABRECHNUNG.Anzahl) = 0
) b ON b.DepotNummer = a.DepotNummer AND b.Symbol = a.Symbol




update a
set a.StopLoss = case when b.StopLossNew > a.StopLoss then b.StopLossNew else a.StopLoss end
FROM StopLoss a
INNER JOIN (
SELECT dbo.DEPOT.DepotNummer, dbo.ABRECHNUNG.Symbol , SUM(dbo.ABRECHNUNG.Anzahl) AS Position, dbo.WERTSCHRIFTENBASISINFO.Kurs,
dbo.DEPOT.StopLoss, dbo.WERTSCHRIFTENFUNDAMENTALDATEN.[lange Volatilität],
dbo.WERTSCHRIFTENFUNDAMENTALDATEN.[kurze Volatilität],
CASE WHEN dbo.DEPOT.StopLoss = 1 THEN dbo.WERTSCHRIFTENBASISINFO.Kurs * dbo.WERTSCHRIFTENFUNDAMENTALDATEN.[lange Volatilität]
ELSE NULL
END as StopLossNew
FROM dbo.WERTSCHRIFTENFUNDAMENTALDATEN RIGHT OUTER JOIN
dbo.WERTSCHRIFTENBASISINFO ON
dbo.WERTSCHRIFTENFUNDAMENTALDATEN.Symbol = dbo.WERTSCHRIFTENBASISINFO.Symbol RIGHT OUTER JOIN
dbo.ABRECHNUNG ON dbo.WERTSCHRIFTENBASISINFO.Symbol = dbo.ABRECHNUNG.Symbol RIGHT OUTER JOIN
dbo.DEPOT ON dbo.ABRECHNUNG.DepotNummer = dbo.DEPOT.DepotNummer
GROUP BY dbo.DEPOT.DepotNummer, dbo.ABRECHNUNG.Symbol, dbo.WERTSCHRIFTENBASISINFO.Kurs, dbo.ABRECHNUNG.ValorenNummer,
dbo.DEPOT.StopLoss, dbo.WERTSCHRIFTENFUNDAMENTALDATEN.[lange Volatilität],
dbo.WERTSCHRIFTENFUNDAMENTALDATEN.[kurze Volatilität]
HAVING (dbo.ABRECHNUNG.ValorenNummer > 20)
) b ON b.DepotNummer = a.DepotNummer AND b.Symbol = a.Symbol


I would like now to INSERT INTO the recordsets, whith the same calculation above but ABRECHNUNG.Anzahl > 20 and

dbo.ABRECHNUNG.symbol + dbo.Depot.DepotNummer  UNLIKE  dbo.StopLoss.Symbol + dbo.StopLoss.DepotNummer


0
 

Author Comment

by:Kongta
ID: 12493457
SashP, I got it worked as I changed various datatypes. Thx.
0
 

Author Comment

by:Kongta
ID: 12497008
SashP, can I ask you:

Comparing does not work, when I use

dbo.ABRECHNUNG.Symbol + dbo.ABRECHNUNG.DepotNummer = dbo.StopLoss.Symbol + dbo.StopLoss.DepotNummer

then I only get the 9 recordsets which is correct and indicates that all should work, but if I use

dbo.ABRECHNUNG.Symbol + dbo.ABRECHNUNG.DepotNummer <> dbo.StopLoss.Symbol + dbo.StopLoss.DepotNummer

then it shows me everything, including the 9 above. How can I solve this issue?
Thx
Kongta
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question