Solved

Using Select Top (Select From )

Posted on 2014-03-24
5
493 Views
Last Modified: 2014-03-25
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
Comment
Question by:itdeptvnam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 39952516
"Select top"... top how many? I would usually expect to see something like "select top 3 ...".
0
 

Author Comment

by:itdeptvnam
ID: 39953162
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39953371
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 39953389
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
 

Author Comment

by:itdeptvnam
ID: 39953460
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

The goal of this blog is: - To define the incident management process - To go over the key elements of an incident management system - To look into incident alert management tools that integrate with ConnectWise.
A big percent of today’s marketing activity is performed through the online environment. The marketing strategies that have existed a decade ago no longer relate to what’s happening today. We’re currently facing a revolutionary era, called the digit…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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