Subselect With Numbers
Feb 18, 2002
I have the following table:
CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1))
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)
-- Those works and gives me 11,12,21,22
SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3)
SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2','3')
-- This doesn't works
declare @Rule varchar(10)
set @Rule='2,3'
SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule)
Any idea?
I don't mind to change the data type if it works.
View 1 Replies
ADVERTISEMENT
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Mar 16, 2006
Is this syntax not supported by SQL Server 2000...?
select count(*) from (select useridentifier from registered_users
where useridentifier < -120)
View 5 Replies
View Related
Jan 24, 2007
I have two tables with the following relevant fields:
Apps
appID appName
PBC
pbcID appID appCT
These are joined on appID. appCT can be 1 of 2 values, either "PC" or "LA". So an example of a few records in PBC would be:
1 1 PC
2 1 LA
3 2 PC
4 2 LA
5 3 PC
6 4 LA
...
...
You can see that for each App, in PBC there can be two related records - PC and LA. But for example, record number 5 is App 3 PC, but there is no App 3 LA. I am trying to build a select to tell me which Apps are not in PBC at all, AND which Apps only have either LA or PC, not both.
Help?
Thanks!
View 5 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Nov 16, 2006
I'm working on a purchasing website for a store. A request has many line items, and a line item can have many products. One of the characteristics of the line item data table is a total price, calculated from multiplying lineitems.quantity and product.price.
INSERT INTO lineitems (request_id, quantity, product_id, total_price)VALUES (@rid,@quant,@pid,@totalprice)WHERE @totalprice = (SELECT products.price * @quant FROM lineitems, products WHERE lineitems.product_id = products.id)
Visual Studio isnt accepting this. Is there a way to do this better?
View 10 Replies
View Related
Aug 1, 2004
How do you combine the following 2 updates into one Update statement (1 SUBSELECT statement)
Update SPLL_Policy
SET SPLL_Policy.Prog_Year =
(Select TOP 1 Prog_Year From SPLL_WinsPolicy_Input
Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number
ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
Update SPLL_Policy
SET SPLL_Policy.Prog_NAME =
(Select TOP 1 Prog_Name From SPLL_WinsPolicy_Input
Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number
ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
Thanks
Harold Hoffman
View 7 Replies
View Related
Aug 28, 2007
I have two tables in which I need to select data from and I don't know what construct to use. The two tables are SY and MV. SY contains stocks and MV contains a log of all price changes of these stocks. I need to produce a report of price changes between the latest price and the previous price and take the difference between the two. I'm using MSSQL. Here are the important fields in my tables:
SY - SY.SYID
MV - MV.SYID
MV - MV.PRICE
MV - MV.DATE
Here is what I have so far:
Code:
SELECT
sy.syid,
sy.sycode,
mv.price,
(SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) AS lastprice,
(mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) AS diff,
(mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) /
(SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) * 100 AS percentdiff,
mv.date
FROM
mv,
sy
WHERE
mv.syid = sy.syid
AND mv.date = '8/27/2007'
AND sy.ACTIVE = '1'
ORDER BY
sy.syid,
mv.date
DESC
View 1 Replies
View Related
May 20, 2008
Hi,
I have a query which contains 2 subselects joined with a union all. The select for each is just a count, so I'm only returning 2 rows. I then want to be able to perform a calculation between these 2 results... ie divide one by the other to get the percentage.
The only way I could think of doing that was make the whole query a subselect of another query where I could then perform the calculation in the new select statement, however it doesn't like this. I just get incorrect syntax near the closing bracket of the from section.
Any ideas? Thanks!
SELECT *
FROM
(SELECT count(t0.product)
FROM
(SELECT t0.packslip ,
t1.date_upld ,
t0.product AS product ,
t0.qty_topick as topick ,
t0.qty_picked as picked ,
t0.qty_topick - t0.qty_picked as shorted,
(t0.qty_picked / t0.qty_topick) * 100 as linefill
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)) t0
UNION ALL
SELECT count(t1.product)
FROM
(SELECT t0.packslip ,
t1.date_upld ,
t0.product AS product ,
t0.qty_topick as topick ,
t0.qty_picked as picked ,
t0.qty_topick - t0.qty_picked as shorted,
(t0.qty_picked / t0.qty_topick) * 100 as linefill
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)
AND t0.qty_picked <> t0.qty_topick) t1) t2
View 11 Replies
View Related
Aug 31, 2007
Hello,I have a problem with a subselect I use in a stored procedure:UPDATE #TEMP_TABLESET P_ID_1=(SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID),P_ID_2=PARENT_ID,P_ID_3=IDWHERE PARENT_ID IN (SELECT P_ID_2FROM #TEMP_TABLE b)So the subselect is (SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID), and it returns NULL. The cause of that is mostprobably the fact that I try to link ID from inner table b withPARENT_ID from the outer table. I thought it had to be done this way,but obviously not. Can somebody help me with this syntax problem?Thx,Bart
View 5 Replies
View Related
Jul 20, 2005
Not sure if this is the right group to post this to but.This is the current query that I have.select tableA.id,tableB.artist,tableB.image,from tableA,tableB wheretableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20order by tableB.price DESC'What I need is, for each row returned I need information from a thirdand fourth table. tableC, and tableD.tableC has information ( the tableA.id = tableC.eventId) that I need toobtain tableC.accountId = tableD.accountId in order do select thethe binding information in tableD between a Vendor(name,address..etc..)and tableB.imageAny help would be greatly appreciated.
View 2 Replies
View Related
Sep 26, 2013
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]...
View 2 Replies
View Related
Mar 2, 2007
Hi,
Hope someone could help me in revising a long running query. Here is the query
select *
from table1
where classid is null
and productid not in (
select productid
from table1
where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
-Ruel
View 9 Replies
View Related
Mar 2, 2007
Hi,
Hope someone could help me in revising a long running query. Here is the query
select *
from table1
where classid is null
and productid not in (
select productid
from table1
where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
-Ruel
View 4 Replies
View Related
Jul 20, 2005
I have a stored procedure what produces N number of rows.The rows are ordered by a cataegoryType as followscatAcatBcatCWhat is needed to do on the C++ code side is break these out intotheir respective categories by iterating through the rows and checkingthe category type. Is there a way to let the DB do this via some sort ofsubselect on the rows returned via the stored procedure.Thanks in advance.
View 3 Replies
View Related
May 31, 2007
I have a sqldatasource, and on the selectcommand I'm trying to use a case statement with a subselect. The case statement works fine without the subselect, but I'm trouble getting it to work with the case statement. Could you help me with the syntax? ThanksSelectCommand="
SELECT DISTINCT RecipeID, Title
FROM [Recipes]
WHERE
(CASE WHEN @Type='Appetizer' THEN Appetizer
WHEN @Type='Pies' THEN
(Select Distinct RecipeID, Title From Recipes
WHERE Title like '%Pie%')
WHEN @Type='Beverages' THEN Beverage
WHEN @Type='Dessert' THEN Dessert
WHEN @Type='Kids' THEN Kids
WHEN @Type='Side' THEN Side
WHEN @Type='Soup' THEN Salad
WHEN @Type='Main' THEN Main
WHEN @Type='Breakfast' THEN Breakfast
END) = 1"
View 1 Replies
View Related
Feb 27, 2006
Im not sure the best way to go about doing this
i want to pass 2 values to the procedure
one passed value will be a direct insert value
the other passed value will be used within a subselect
and the result from the subselct will be the second inserted value.
Code:
Create procedure InsertSubSelect
(
@Value1 nvarchar(50),
@Value2 nvarchar(50)
)
AS
INSERT INTO Mytable (field1, field2)
VALUES (@Value1,
(select Value2
from Mytable2
where Value2 = @Value2 ) )
Go
View 5 Replies
View Related
Jul 20, 2005
Help!I'm trying to understand the new ANSI join syntax (after many years ofcoding using the old style). I am now working with an application that onlyunderstands ANSI syntax so I am struggling.My first (old style syntax) SQL statement below produces 60 rows:SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1,ADDRESS A6WHERE A1.ADDRESSID=A6.ADDRESSIDAND A1.CONTACTID IN(SELECT A4.CONTACTIDFROM CONTACT_LEADSOURCE A4,LEADSOURCE A5WHERE A4.LEADSOURCEID = A5.LEADSOURCEIDAND A5.DESCRIPTION = 'some_description' )AND A1.CONTACTID IN(SELECT A2.CONTACTIDFROM TICKET A2,ENROLLHX A3,EVENT A7WHERE A3.STATUS IN ('R', 'Confirmed')AND A2.TICKETID = A3.EVXEVTICKETIDAND A3.EVENTID = A7.EVENTIDAND A7.CODE IN('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))ORDER BY A1.LASTNAME ASCI am trying to convert this to the newer ANSI sytax. My second SQL statementbelow produces 67 rows (duplicates):SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)JOIN( SELECT C.CONTACTIDFROM CONTACT CJOIN CONTACT_LEADSOURCE A4 ON (C.CONTACTID= A4.CONTACTID)JOIN LEADSOURCE A5 ON (A4.LEADSOURCEID =A5.LEADSOURCEIDAND A5.DESCRIPTION ='some_description' )) AS C1 ON C1.CONTACTID = A1.CONTACTIDJOIN(SELECT C2.CONTACTIDFROM CONTACT C2JOIN TICKET A2 ON (C2.CONTACTID =A2.CONTACTID)JOIN ENROLLHX A3 ON (A2.TICKETID =A3.TICKETID AND A3.STATUS in ('R', 'Confirmed'))JOIN EVENT A7 ON (A3.EVENTID = A7.EVENTIDAND A7.CODE IN ('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')))AS C3 ON C3.CONTACTID = A1.CONTACTIDCan anyone shed some light on what I am missing?cheers,Norm
View 3 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Jun 15, 2015
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)
WHEN 'Monday' then 'Monday'
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')
END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
View 7 Replies
View Related
Apr 21, 2008
Hello,
I've got the following query:
SELECT zA."ID" AS fA_A
, zA."TEXT" AS fA_B
, (
SELECT COUNT(zC."ID")
FROM Test."Booking" AS zC
) AS fA_E
FROM Test."Stack" AS zA
WHERE zA."ID" = ?
With this query I call:
- SQLPrepare -> SQL_SUCCESS=0
- SQLNumParams -> SQL_SUCCESS=0, pcpar = 1
- SQLDescribeParam( 1 ) -> SQL_ERROR=-1, [Microsoft][ODBC SQL Server Driver]Invalid parameter number", "[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index"
Is there a problem with this calling sequence or this query? Or is this a problem of SQL Server?
Regards
Markus
View 7 Replies
View Related
Mar 30, 2004
I need to figure out how to pull records if the id is even or odd.
ie
ID Name
1 bill
2 john
3 joe
4 jane
for example i need a idea of a select statement that will pull the even records 2 and 4.
is this possible?
View 2 Replies
View Related
Feb 25, 2008
Hello,
I have the following:
insert into dbo.Categories (CategoryID, [Name])
select
newid(),
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers
Numbers is a table with 1000 numbers.
How can I create only 10 records in Categories?
I need to restrict the number of @Numbers used.
Thanks,
Miguel
View 7 Replies
View Related
Mar 1, 2007
In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.
SELECT dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total,
dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid,
dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding,
ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage,
dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission,
ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order
FROM dbo.t_Agents LEFT OUTER JOIN
dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN
dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID
Any ideas how i do this?
Cheers
Anthony Swift
View 3 Replies
View Related
Sep 24, 2007
I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.
Does this sound like something that can be done within SSRS?
I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.
View 5 Replies
View Related
Aug 26, 2007
Hi,
Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?
Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.
Cheers,
koekie
View 3 Replies
View Related
Mar 28, 2008
I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this? Thanks for any help you provide.
View 2 Replies
View Related
Jan 19, 2004
hi,
I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.
The number will have 8 digits.
first 2 digits correspond to year. ex:04 or 03
the next 6 digits start with 000001 and it should get added for each new entry of data.
i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.
Any help would be highly appreciated.
thanks,
-sriram
View 7 Replies
View Related
Apr 2, 2001
How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.
In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...
I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...
please help me in this...
urs
vj
View 1 Replies
View Related
Jun 6, 2000
Looking for a way to round numbers to a specified number of significant digits. The ROUND function rounds to a specific decimal place but does not take into account the level of significance of the remaining numbers. (i.e.
ROUND(7.12435,2)=7.12000) The type of function I need would round the number in the following manner: SigFigRound(7.12435,3)=7.12 or
SigFigRound(7.12345,1)=7.
Any solutions?
View 1 Replies
View Related
Mar 9, 2000
Hello,
I am trying to run a query
select logid, count(logid) from temp2
group by logid
order by logid
compute sum(count(logid))
when I get the result the numbers are being truncated
eg instead of 10471066 it shows 104710 so last two digits
get truncated. Any ideas or hints appreciated.
Thanks
HP
View 1 Replies
View Related