Compare two varchar-fields with two others

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

If this does not help post the entire query that you are using.

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' ?

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

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.

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)

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 ?

Are you asking a question?

Is this not what you wanted?

Stu

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

SashP, I got it worked as I changed various datatypes. Thx.

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