• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Using Select Top (Select From )

I can run this in SQL Server works fine but it errors out in Sql Anywhere

CREATE TABLE #Cal
(
      Site                  char(50),
      Dsc                  char(10),
      Tot                  int,
      TotSel                  int
)

INSERT INTO #Cal (Site, Dsc, Tot, TotSel)
VALUES( 'Dept1','Sales',3,1)

INSERT INTO #Cal (Site, Dsc, Tot, TotSel)
VALUES( 'Dept2','CallCenter',4,3)

INSERT INTO #Cal (Site, Dsc, Tot, TotSel)
VALUES( 'Dept3','Office',2,1)

CREATE TABLE #Data
(
      Site                  char(100),
      EmpFNam                  char(50),
      EmpLNam                  char(50),
      RandNum                  numeric(12,0),
      Sav                        Char(1),
      TotSel                  int

)

INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept1','Ann','Test',87693,'N',1)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept1','Annie','Test',89893,'N',1)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept1','Ann','Test',84593,'N',1)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept2','Ann','Test',87343,'N',2)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept2','Tom','Test',8793,'N',2)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav,TotSel)
VALUES( 'Dept2','Bill','Test',6893,'N',2)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept2','John','Test',69793,'N',2)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept3','Lisa','Test',68783,'N',2)
INSERT INTO #Data (Site, EmpFNam, EmpLNam, RandNum, Sav, TotSel)
VALUES( 'Dept3','Jim','Test',66783,'N',2)
Select * from #Cal
Select * from #Data

Update #Data Set Sav = 'Y'
      From #Data
      Join (Select top (Select TotSel from #Cal Where Site = 'Dept2') RandNum from #Data Where Site = 'Dept2' order by RandNum desc) as p on p.RandNum =#Data.RandNum



--Syntax error near 'Select' on line 3

Select * from #Data
0
itdeptvnam
Asked:
itdeptvnam
  • 2
  • 2
1 Solution
 
Joe WoodhousePrincipal ConsultantCommented:
"Select top"... top how many? I would usually expect to see something like "select top 3 ...".
0
 
itdeptvnamAuthor Commented:
I'm trying to get the How many from another select statement ie. a number from Table #Cal to fill in the value of how many I want in my Select Top statement.
Its works fine in SQL Server but I need it to work in SQL Anywhere.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
afaik, this is not possible in SQL Anywhere.
you can query the value into a variable (using the select @variable = xxx from ... where ), and use that variable in the select TOP @variable  ... statement
0
 
Joe WoodhousePrincipal ConsultantCommented:
Ah I see, using the nested SELECT as an expression to supply the value for the TOP.

I'm not even sure you can use a variable there... ah yes, an integer variable is allowed there.

angelIII has given you the correct answer. :)
0
 
itdeptvnamAuthor Commented:
Thanks angelII this works

Declare @Dept2 int
Set @Dept2 = (Select TotSel from #Cal Where Site = 'Dept2')

Update #Data Set Sav = 'Y'
      From #Data
      Join (Select top @Dept2 RandNum from #Data Where Site = 'Dept2' order by RandNum desc) as p on p.RandNum =#Data.RandNum
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now