Hierarchical Selection Within A Select Statment

Jun 7, 2007

CREATE TABLE RS_A(ColA char(1), ColB varchar(10))

INSERT INTO RS_A
VALUES ('S', 'shakespeare')
INSERT INTO RS_A
VALUES ('B', 'shakespeare')
INSERT INTO RS_A
VALUES ('P', 'shakespeare')

INSERT INTO RS_A
VALUES ('S', 'milton')
INSERT INTO RS_A
VALUES ('P', 'milton')
INSERT INTO RS_A
VALUES ('B', 'shelley')

INSERT INTO RS_A
VALUES ('B', 'kafka')
INSERT INTO RS_A
VALUES ('S', 'kafka')

INSERT INTO RS_A
VALUES ('P', 'tennyson')


SELECT * FROM RS_A

Now i need a select which selects based on hierarchy

if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row

So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson

Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

If any of you can help me with this right away, its is greatly
appreciated
Thanks in advance

View 2 Replies


ADVERTISEMENT

Transact SQL :: Select Lowest Level From Hierarchical Data

Jun 22, 2015

I have hierarchical data such as:

Id    Level    ParentId
1     0         1
2     1         1
3     2         2
4     0         4
5     1         4
6     0         6
7     1         6
8     2         7
9     3         8        
10   4         9
11   0         11

As you can see even the parent element has parentId(in this case id = parentid)

How can I select the lowest level data in the hierarchy and get this result:

Id    Level    ParentId
3     2         2
5     1         4
10   4         9
11   0         11

View 4 Replies View Related

SELECT Statment

Mar 20, 2007

Hello, Is there an SELECT statement to just return the last 100 row in my tables?  I have about 500 rows in my tables and I only need the info on the last 100 rows.
 Thanks
Steve

View 4 Replies View Related

A SELECT Statment, Please Help Me

Oct 2, 2007

hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS

View 5 Replies View Related

Help On Sql Select Statment

Apr 20, 2007

Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null....
IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.

Code:


Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc,
t2.site_id,t3.CompanyCode,t3.CostCenter
From( PMTK_tbl as t1
Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num)
inner Join CostCenters AS t3
on t2.market = t3.market and t2.market_region = t3.RegionCode

View 4 Replies View Related

Help With Select Statment

May 23, 2008

whats wrong with this statment?

declare @alpha as nvarchar(50)
set @alpha = select top 1 monthyear from dbo.Performance_Indicators_Rolling order by recordkey

I get an error

Incorrect syntax near the keyword 'select'.

Can anyone please help?

View 3 Replies View Related

Help With A SELECT Statment

Jul 17, 2007

Hi all,
i'm new to SQL but i've been asked to write an SQL statement to select the latest numeric version value(in this case version 3) from this table, any help?


Name Version Episode

John 4c 60

John 4b 50

John 4a 40

John 3 30

John 2 20

John 1 10



Regards

View 7 Replies View Related

Help With Select Statment

Mar 8, 2008

Im trying to get a count of all user logins to display in a report - I have a column, count, which has a value of 1 for each record entered.


Select firstname, lastname, count(count) as TotalLogins Order by TotalLogins. But the count is always appearing as 1.

Report should look like:

John Smith 132
Jane Doe 101
Doris Day 99

View 19 Replies View Related

Hierarchical Table Functions Vs Hierarchical CTE

Jun 9, 2006

Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...

Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);

Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)


What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.

Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?

View 8 Replies View Related

Question:select Where Another Select Statment

Jan 18, 2008

Hello all
I create sp
--------------------code----------------------
 ALTER procedure [dbo].[uspInviteGroup] --uspInviteGroup 'fdi'
@strUserId nvarchar(50)=null
as
select GroupName as 'strGroupName',GroupFounder as 'strGroupFounder'
from SITE_MemberGroupswhere GroupId=
(select GroupId from SITE_GroupMember
where userId=@strUserId)
--------------------code----------------------
but when I tested the above sp --uspInviteGroup 'fdi'return this error
------------------error---------------------
Msg 512, Level 16, State 1, Procedure uspInviteGroup, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
------------------error---------------------
 
in my case the second select statment return 2 value,I need the first select statment return two row
how can I do that?
thank you

View 3 Replies View Related

Custom Select Statment

Feb 18, 2007

Hello,
Why when I make a custom select statment with multiple tables, that when I test my query I'm shown the same rolls multiple times.  When I make a custom query with just one table everything works just fine.  I don't have any primary keys or restraints or whatever.
SELECT * FROM table1, table2, table3           Does not work(shows each row multiple times)
SELECT * FROM table1         works just fine (shows each row just one time)
Thanks
Steve
 

View 1 Replies View Related

Add Cases To Select Statment

Jun 8, 2006

I need to add some cases to the select statment for cpeorderstatus:
Here is my Select statement:
"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD')                   AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER,                  OI.ITEM AS CPEORDERTYPE, OI.QTY,       O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST,       C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE,       'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE     FROM  SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R     WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND       (C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"
I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP

View 1 Replies View Related

Using A Select Statment For The Name Of The Field?

Jul 20, 2005

Do any of you know if it's possible to get the name of your field fromanother table?E.g.Select FYear as [Select description from YearTable where category=1]I need the name of the fields to be user defined and the only way Ican see this happening is through a table. But now...how do I get thefield name from the table?Any help would be appreciated!!!!Thank you,Susan

View 2 Replies View Related

&&amp; Character In Select Statment

Sep 21, 2006

Hi Folks

If I wrote this in QA what does it mean?

Declare @X INT
Set @X = 2

Select (@X & 8)

What & do in this select statment

Thank you

View 5 Replies View Related

Simple Select Statment

May 1, 2008

can someone read this to me thanks.


SELECT DISTINCT PlanNumber FROM tbmembers WHERE ISNULL(PlanID, '') <> ''

View 4 Replies View Related

SQL Command Select Statment Problem VB

Feb 19, 2008

Hi,
I have a Command that I can not get to work. I am trying to take an AVERAGE of 1 Columb and display it in a label.
ALSO what statement do I add when the Lable that is supposed to display that output is empty. Right now It gives me an Error.comm = New SqlCommand("SELECT * AVG Rating FROM Reviews WHERE CID = " & Request.QueryString("CID"), conn)
Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT AVG Rating FROM Company WHERE CID = " & Request.QueryString("CID"), conn)Dim objReader As SqlDataReader
conn.Open()
objReader = comm.ExecuteReader()
objReader.Read()lblRanking.Text = objReader("Rating")
 
objReader.Close()

View 3 Replies View Related

Use 'select Case' Statment In Sql Query.

Mar 18, 2005

hi,friend

is it possible to use 'select case' statment in sql query.

give any idea or solution.

thanks in advance.

View 2 Replies View Related

Setting The Value Of A Variable In A Select Statment...

Oct 19, 2001

I want to be able to have a single select statment:

SELECT TOP 1 Call.JobNum, Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID

but with this select statment I also want to set a variable:

declare @variable int

SELECT TOP 1 Call.JobNum, @variable = Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID

Now SQL Server does not like this, can not set a variable in a multiple select statment. I NEED to do this all in one step if possible. Any suggestions?

pat

View 1 Replies View Related

Seting A Variable Value From A Select Statment

Aug 1, 2006

In my sproc this is wrong :

SELECT @DetailItems= Count(idDetail)
FROM Details
WHERE CheckNum=@CheckNumber

How should i do this?


Create PROC voidCks
@CheckNumbervarchar(30)
AS
DECLARE @DetailItemsint,
@DetailTotalMONEY,
@CheckAmountMONEY,

SELECT @DetailItems= Count(idDetail)
FROM Details
WHERE CheckNum=@CheckNumber

View 3 Replies View Related

How This Select Statment Works Please It Is Urgent?

Apr 21, 2007

hey friends!
pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question .
create table mytable( english varchar(120), tigrigna Nvarchar(120))
insert into mytable values('peace',N'sälam')
insert into mytable values('kiss',N'samä ')
insert into mytable values('to kiss each other',N'täsasamä ')
then
select * from mytable where english='peace'; this works fine
but
select * from mytable where tigrigna=N'sälam'; this doesnot work;
select * from mytable where tigrigna='sälam'; this also doesnot work,
so what should i do to select this unicode select statement?

tigrigna is one of the spoken language in east africa(ethiopia).
Hopefully, i have joined this forum today, and just looking for your reply

I am Looking for your reply !

View 1 Replies View Related

Two Join Types In One Select Statment

Mar 7, 2007

Will writes "I have an employees table, which gives all emp. ids. I have a second table, time_log with tasks each employee has logged:

empID date log_time duration etc
===================================

and a 3rd table - a pivot table with a single column named "i" containing consecutive integers from 0 - 1000

I need to know for each date in a series, e.g seven consecutive days, how much time each has logged. easy if everyone has logged a task for every day, but I need to include every day where they have not logged a task.

so, a cartesian join on all the dates in a series(produced using dateadd on p.i and the pivot table)

SELECT dateadd('d',p.i, #02/19/2007#), e.empID
FROM pivot1 p, employees e
WHERE i<no_dates

However I need to do a left join with time log where the date and employee ids are the same, and I have summed the durations for each date. The following query does this, but does not include dates and times where nothing has been logged.

SELECT empID, log_date, sum(duration)
FROM time_log
GROUP BY empEIN, log_date

GIVING, EVENTUALLY, ALL DATES AND EMPIDS AND THE TOTAL AMOUNT OF TIME THEY HAVE LOGGED FOR EACH DAY."

View 1 Replies View Related

Need Help For Selecting Posts In SELECT Statment

Dec 4, 2007

I'll try to explane what I am looking for.

I'd like to select from a table, let say with 2 variables. One with a currency and one with integer. The integer value is a number between 0 and 255. The integer value represents a value of what kind of BIT is turned on in one BYTE.

Down to business.

I am looking for all BYTES with the fifth BIT = 1 as in example 00001000.This represents the value of 16. This value is an integer in my table. But as you see, the BYTE has a number of different values with the fifth=1 as the rest of the BITS can be 1 as in example 01011000 = 26.

Down to code.
Tablename = myMoney
Column Name = mCurrency; Data Type = decimal(18,2)
Column Name = bit5; Data Type = int





Code Block

SELECT sum(mCurrency)
FROM myMoney
WHERE somefunction(bit5) = 1





I have made the code in VB some time ago but this time I need to do the aclculation in the SQL server.

Hopefully Robin M

View 1 Replies View Related

Excluded A Record From The Select Statment

Feb 20, 2008

Hi I have a table where I am searching for the values between certain numbers. But then I want to exclude a record to be part of the search

so how I could do that.

This is what I am trying to do.

Select * from mytable Where Value Between 10000 and 200000 --this gives me the result

But then I want to exclude a record from the select statment.
For example this is what I really want to do

Select * from mytable Where Value Between 10000 and 20000 and where value != '100o'

It is not a typo '100o' and this not let me test the select statment so I want to exclude that particular record.
The type was in the table and I want to exclude that record so I can get my statment running.

View 5 Replies View Related

Stored Procedure With Conditional Select Statment

Feb 6, 2007

Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE  PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255)           )
AS          declare @vSQL nvarchar(1000)         set @vSQL = 'SELECT ' +  @loc + ', '  + @pnum +  ' FROM  Category '         exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
 Please help,
Thanks,
bullpit

View 7 Replies View Related

EXECUTING STORE PROCEDURE IN SELECT STATMENT

Aug 27, 2001

hI
DOES ANY BODY KNOW HOW TO EXECUTE STORE PROCEDURE IN SELECT STATMENT

THANKS
jk

View 1 Replies View Related

How To Use Variable To Receive The Result Of A Select Statment. ?

Jun 21, 2002

Good morning;

My Problem is :im my transaction i use insert code like the following :

" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"

In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.

" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "

I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.

Thinks.

View 1 Replies View Related

Select Statment On Column That Can Contain Alpha And Numeric Values

Sep 13, 2007

Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
 This report will be used to find students who have failing grades. Thanks for any help! 

View 5 Replies View Related

Stored Proc Using Variable As Fieldname In Select Statment

Apr 20, 2001

Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.

I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?

Thanks,

Oliver

CREATE PROCEDURE test
@ContractCode varchar(10),
@dtFrom datetime,
@dtTo datetime,
@Umbrella int

AS

declare @option varchar(900)


if @umbrella = 0
set @option = 'c.contractcode'
else
set @option = 'cb.employercode'

select
c.claimsno,
c.attenddoctor,
c.patientcode,
p.sex,
cb.employercode
into #SavingsData1
from claimsa c inner join Patient p
on c.patientcode = p.patientcode
inner join claimsb cb on c.claimsno = cb.claimno
where
@option = @ContractCode and c.dateentered between @dtFrom and @dtTo
and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'

select * from #SavingsData1

View 1 Replies View Related

Newbie - How To Update Multiple Rows With Select Statment

Oct 25, 2004

Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

Pam

View 8 Replies View Related

Character To Numeric Conversion Error- Select Statment On Char Column

Sep 18, 2007

Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT     commentFROM         informix.ipr_stucomWHERE     (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = '  I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = '  P' OR comment = '  F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.

View 2 Replies View Related

Select Statement With Run Time Field Selection

Jan 3, 2008



I have this SELECT statement.


SELECT [issueID], [name] FROM [MyIssue]


What I wanted to do is in addition to the above statement, I want to add two run time fields like this:


99 [issueID],'All Issues' [name]

So let's say the above select statements generates this list:

Summer 2007 Issue
Winter 2007 Issue

The two addition fields will make the result list like this:

01 Summer 2007 Issue
02 Winter 2007 Issue
99 All Issues


How do I accomplish this? Any help is much appreciated.

View 5 Replies View Related

Reporting Services :: Report Parameter Shows (Select A Value) Only For First Selection

Jun 11, 2015

I have a rdl where its working fine while report preview, for parameter  it shows <Select a value> only first select . for next select its just showing dropdown list of values .

How can i get <select a value> for all selections. I referred few links which says only for first select you will get <select a value> ,from second select it just shows dropdown.

If we want <select a value> again then we should go back design part and then preview again. But I don't want. What is the possible way to see <select a value> for every select without going back to design.

View 3 Replies View Related

Insert And Delete Statment In One Statment

Jan 18, 2008



Hi all

I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?

Thank you in advance .....

Regards,
sms

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved