Alternative for IN

[Dorine]

I have the following (summarized) code:

Select * from dbo.PRSEBA where tfvrnr IN (select tfvrnr from pfftpr where tfsoid = 40)

I am using a tool called AQT to run queries on a MySQL server. The code gets stuck on the IN statement. Does anybody know other code to replace IN ?

Actually my code is a lot more but it gets stuck on the IN statement so for that purpose I only printed this little part of the code.

I hope anybody can help me....

 

[JohnDTampaBay]

How about just JOINing to the table?

SELECT a.*
FROM dbo.PRSEBA a
INNER JOIN pfftpr b ON a.tfvrnr = b.tfvrnr
WHERE b.tfsoid = 40


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[esdee]

try to replace the 'IN' clause with an 'EXISTS' clause

BOL - "Subqueries with EXISTS"

 

[nigelrivett]

What do you mean by stuck?

You might need a distinct on the solution that AngelWPB has given.

the exists would be

Select * from dbo.PRSEBA where exists (select * from pfftpr where pfftpr.tfvrnr = PRSEBA.tfvrnr and pfftpr.tfsoid = 40)



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Dorine]

I tried your solutions but my code still gets stuck. I am using AQT to run my queries and the program will just not respond. Could it be that I have too many sub-queries?
Here is my complete code...maybe anybody sees something else I have done wrong...

Thanks again!!!


CREATE PROCEDURE dbo.sp_Portfolio_DAL3 @location varchar(3), @sec_type varchar(1), @long_short varchar(1), @portfolioDate smalldatetime, @sim int, @soid smallint, @RunDate smalldatetime, @ReportDate smalldatetime as

select tfclnr, ConsolExtractId = max (tfvrnr), tfclnm
into #TEMP
from Ephesus_Data..pfftpr T1 right join (select zzcomb from Ephesus_Data..prcmcm where tfsoid = @soid and tfvrnr = @sim) T2 on T1.tfclnr = T2.zzcomb
where tfrpdt = @ReportDate
and tfrudt < @RunDate
and tfexid = 'R'

group by tfclnr, tfclnm
order by tfclnr

Select
a.ZZCLNR, f.TFCLNM, a.ZZSENR, a.ZZNAME, a.ZZDESC, a.ZZCUCD, a.ZZCNCD, b.ASNumber, c.Description, c.industry_code, e.industry_desc, c.country_code, d.country_desc, a.ZZTQUA, a.ZZCPSC, a.ZZMVSC, a.ZZUGSC, a.ZZCPAC, a.ZZMVAC, a.ZZUGAC
from dbo.PRSEBA a
left join #TEMP z on a.tfvrnr = z.ConsolExtractId
left join EXT_ASB.dbo.ASB_ASNumbers_CCNumbers b on a.ZZSENR = b.CCNumber AND b.Location = @location
left join EXT_ASB.dbo.ASB_ASNumbers c on b.ASNumber = c.ASNumber
left join EXT_ASB.dbo.country_code d on c.country_code = d.country_code
left join EXT_ASB.dbo.industry_code e on c.industry_code = e.industry_code
left join dbo.PFFTPR f on a.ZZCLNR = f.TFCLNR AND a.TFVRNR = f.TFVRNR AND a.TFSOID = f.TFSOID
AND a.ZZSETY = @sec_type
AND a.ZZBADT = @portfolioDate
AND a.ZZTQUA <> 0
AND a.ZZLOSH = @long_short
ORDER BY a.ZZCLNR, a.ZZNAME

 

[SQLSister]

YOu could try creating the temp table first then using the insert statement. Select into often creates problems.

 

[SQLBill]

Here may be a problem with the advice you are getting:

I am using a tool called AQT to run queries on a MySQL server.

This is NOT the MySQL forum. This is the MS SQL Server forum and the SQL syntax is not the same between the two. Most is the same, but there's a lot from SQL Server' TSQL that won't work with MySQL. You are better off posting in the correct forum.

-SQLBill

 

[Dorine]

I am sorry, it is an SQL server...not a MySQL server. My mistake.