Compare two varchar-fields with two others

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
KongtaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SashPCommented:
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
SashPCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SashPCommented:
If this does not help post the entire query that you are using.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KongtaAuthor Commented:
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
SashPCommented:
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
SashPCommented:
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
SQL_StuCommented:
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
KongtaAuthor Commented:
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
SQL_StuCommented:
Are you asking a question?

Is this not what you wanted?

Stu
0
KongtaAuthor Commented:
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
KongtaAuthor Commented:
SashP, I got it worked as I changed various datatypes. Thx.
0
KongtaAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.