Creating A Heirarchical Output From SQL Statement

Jul 20, 2005

This may be a basic question, but defining anything other than a cursor
is preffered.

I have, as an example, 2 tables. One with customer data (addresses,
phones, etc), the other is a listing of all 50 states (a cross reference
for short state alias to long state name, i.e. FL - Florida, etc...).

I want to sort the out put by state long name, and show each customer in
the state ... BUT ...
the output needs to be like so:

Florida
ABC,Inc Address1 City, State Zip, other Info
Dummy Corp Address1 City, State Zip, other Info
...
Georgia
XYZ, Inc Address1 City, State Zip, other Info
...
etc ...

This is a basic heirarchical listing. Can this be done with a single
T-SQL statement or are cursors needed?

Thanks in advance.

"Excellence is achieved through 1% inspiration and 99% perspiration." A.
Einstein

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

View 1 Replies

A Heirarchical Query

Jul 20, 2005

pls anybody help me with this.i need to make a query where i have to display all names of a categoryheirarchically.C1-->C2-->C3-->C4where C1 is the top level categoryit shud b displayed as C1/C2/C3/C4Also there can b any no of category levels.pls anybody help memanu

View 8 Replies View Related

Heirarchical Query In Sql Server 7

Feb 13, 2001

I have a table in which I have a parent_id and child_id field. I may then have a record
in the same table where the child_id of the first record is now the parent_id of the new
record with a new child_id. And that child_id is now the parent_id in a new
record with a new child_id, and so on, and so on. How do I query this table to get
all related parent and child_ids?

View 1 Replies View Related

GROUP BY For Heirarchical Data

Mar 7, 2008

I have a table:

ID int, AreaID int, SaleAmount float, SaleDate smalldatetime

AreaID is an foreign key to a second table of heirarchical area data:

ID int, ParentId int, AreaName nvarchar.

The heirarchy varies in depth for different parts of the country - sometimes 3 levels deep, sometimes up to six.

My problem is this: how do a construct a sproc that will allow me to pass in any area id and then return one or more result sets with all the child data of that area id grouped (to allow a SUM() of the sales data and a MIN() of the dates) by EACH LEVEL of the heirarchy?

Sales data only exists in the bottom one or two levels.

I've tried looking at CTEs but can't seem to crack the problem. I got close with a WHILE loop, but that kept grouping the data at the same level.....

Thanks

John

Expected output from this data if, say, I passed in 1 (the National level)

'National', 47.81, 20-Feb-08
'Super Region A', 37.81, 21-Feb-08
'Region 1', 16.81, 21-Feb-08
'Region 2', 21.00, 22-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08
'Sub Region 3', 7.70, 23-Feb-08
'Sub Region 4', 12.30, 22-Feb-08

and so on. Note that it doesn't have to be one table - each level could come back as a separate table (in fact, that might be helpful).

If I passed in 3 I'd get

'Region 1', 16.81, 21-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08

(NB these results are just typed in, so forgive typos please, and the sums and min dates are not necissarily the same as from the sample
data below)

CREATE TABLE [dbo].[SalesData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[AreaID] [int] NULL,
[SalesDate] [smalldatetime] NULL ,
[SalesAmount] [float] NULL
)

CREATE TABLE [dbo].[SalesAreas](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL ,
[Name] [nvarchar](64) NOT NULL
)

DECLARE @ID int
DECLARE @ID2 int
DECLARE @ID3 int

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( 0, 'National') --1
SELECT @ID = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region A')--2
SELECT @ID2 = @@IDENTITY

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 1') --3
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 1') --4
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 2') --5

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 2') --6
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 3') --7
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 4') --8

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region B')--9
SELECT @ID2 = @@IDENTITY

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 3') --10
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 5') --11
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 6') --12
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 7') --13

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 4') --14
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 8') --15
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 9') --16
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 10') --17
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 11') --18

INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-5, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-6, GETDATE()), 2.81)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-8, GETDATE()), 4.14)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-9, GETDATE()), 1.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-2, GETDATE()), 1.02)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-3, GETDATE()), 3.13)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 6.17)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-1, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-4, GETDATE()), 4.29)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-5, GETDATE()), 4.46)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-6, GETDATE()), 3.33)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-1, GETDATE()), 3.92)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (12, DATEADD(d,-7, GETDATE()), 5.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-5, GETDATE()), 6.16)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-3, GETDATE()), 3.34)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-2, GETDATE()), 2.61)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-3, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (16, DATEADD(d,-4, GETDATE()), 8.28)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (17, DATEADD(d,-5, GETDATE()), 2.44)

View 14 Replies View Related

Creating An Asynchrous Output

May 17, 2006

Are there any good examples creating an asychronous output. I need to be able to output the values from the input as well as adding 7 new columns to each row.

View 3 Replies View Related

Creating A Store Procedure With Output Parameter

Oct 22, 2005

Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.

View 1 Replies View Related

Creating XML Output...unusual/impossible Format?

Sep 23, 2005

Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!

View 6 Replies View Related

Creating Stored Procedure With Output Parameter

Dec 5, 2005

Hi

Being a rookie to stored procedures the following cry for help will be posted.

The stored procedure below is supposed to create a dynamic string as an output parameter

create procedure spGetFieldNameList
@TableName varchar(256),@String varchar(8000) output
As
select @String = @String + sc.name + ','
from syscolumns as sc(nolock) join sysobjects as so(nolock)
on sc.id = so.id
and so.name = @TableName
order by colid asc

Creating the stored procedure does not trigger an error.

Calling the stored procedure as: spGetFieldNameList 'OfferCondition'.
Causes the following message:
Server: Msg 201, Level 16, State 4, Procedure spGetFieldNameList, Line 0
Procedure 'spGetFieldNameList' expects parameter '@String', which was not supplied.

Calling it this way:
declare @String as varchar(8000)
set @String = ''
set @String = spGetFieldNameList 'OfferCondition'.

Causes:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'OfferCondition'.

Any help is highly appreciated.

Thanks

View 6 Replies View Related

Creating Error Output For Custom Components

Feb 14, 2006

Hi,

I have a 2 custom components - source and destination.

I want to create an error output for each, to allow the users of my component to handle errors the way they choose.

I only found a property in IDTSOuptut90 named isErrorOut - a boolean property indicating whether this output is an error output or not.

Does anyone have additional documentation / articles / code samples regarding how to really populate the rows in the error output?

Thanks

View 1 Replies View Related

Tips On Creating Output Columns In A Custom Transformation

Aug 14, 2007

I would like my transformation to automatically create an output column for each input column. Any tips? I can't seem to determine which event to listen to or method to override.

View 3 Replies View Related

How To Use SP's Output In The SELECT Statement

Sep 8, 2006

hi guys!it's very very simple question for you mighty sql DBAs. but very hardfor a developer like me who is very very new to MS SQL.anyways the problem is i want to use one SPs out to in the SELectstatement. here is an example :select * from sp_tables tablename like 'syscolumns'please note that this is just an example. i'm using different SP but iwant to use in the same way.if anybody has anything to say. please write to me. i would be glade toread your repliesThanks,Lucky

View 3 Replies View Related

Output Parameter Vs Select Statement

Mar 7, 2008

If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?

View 1 Replies View Related

Can A Query Using OUTPUT Statement Be SELECTED From?

Oct 19, 2007

I tried wrapping a DELETE FROM ...OUTPUT DELETED.* WHERE... inside of a select, crossed my fingers and got a syntax error. I thought maybe since the OUTPUT statement produces a rowset that it and it's DELETE could be selected from. Is such a thing possible in the same query or should I give up?

View 5 Replies View Related

Select Statement To Output A Date

Oct 7, 2007

Hello Guys,

Please could you help me formulate a SELECT statement to output a date in the format (Month, Date Year), eg
October, 07 2007. Thanks.

View 5 Replies View Related

Strange GROUP BY Statement Output

Oct 16, 2006

I am currently straching my head as to why the following doesn't work as I expect.
I have Three tables UsersManagers, Users and Bookings:-

SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours
FROM UsersManagers
LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser
LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username
WHERE UsersManagers.UsernameManager = 'testPM1'
AND (Bookings.DateOfBooking BETWEEN '01 October 2006' AND '31 October 2006')
GROUP BY UsersManagers.UsernameUser

This statement returns only one username with the hours they have booked in my Bookings table. However the left outer joins (I would have thought) should return an entry even if there are no hours booked for a user. If I omit the Date Section:

SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours
FROM UsersManagers
LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser
LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username
WHERE UsersManagers.UsernameManager = 'testPM1'
GROUP BY UsersManagers.UsernameUser

I get two records returned
testUsr1 37.5
testUsr2 NULL

Why don't I get this when I use a date?

Thanks

View 5 Replies View Related

Interesting Output In A Select Statement

Nov 26, 2007

Hi,

If I query like this, am getting the following Output.. Why is the part before '_ ' gets truncated and gets displayed as result ?

SELECT 10_to_100

_to_100
-----------
10

SELECT 25_from

_from
-----------
25

If anybody is familiar with the reason,pls share...

cheers !
ash

View 5 Replies View Related

Creating SQL Statement

Mar 4, 2007

Alright, so let me explain the details first.I have two tables. One is the default aspnet_users table that themembership class builds. that has GUID, username, lowereduser, and such.then I have another table called "UserSkills". That stores the GUID of the member, then the skills they have. so in that table i have. userID as GUID, then about 12 languages in 'bit' format.. (thats becuase in the webpage when they fill out there profile, all these are checkboxes. Basically all of the info is here http://www.listofcoders.com/profile.aspx?name=fenixsn. so there are a couple of bit fields, 1 text, and couple of varchars.anways, so i wanna build a powerful search thingy. where the users have the option to search a user that only does for ex say php, asp, asp.net. and is from location "Canada". ok so when they fill out the info, I want my SQL statement to do the following search the userskills table for the required fields. there might be more then 1 person that has the same profile, but different GUID. and then maybe using "Join" or another sql statement, grab there username, and last activity date from the users table that memberhship createes. so in short, how do i make a dynamic sql statement.

View 4 Replies View Related

Creating A SQL Statement

Aug 25, 2004

Hello all - newbie post forthcoming....

I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows:

sql = "INSERT INTO article (maincat, subcatid, subject, article)"
sql = sql & " Values ('"
sql = sql & quotes(Request.form("maincat")) & "', '"
sql = sql & quotes(Request.form("subcatid")) & "', '"
sql = sql & quotes(Request.form("subject")) & "', '"
sql = sql & quotes(Request.form("article")) & "')"

The response I get is.... "Data Type Mismatch in Criteria Expression"

The post data does not have quotes around the data - I am thinking this is the problem.

Any help would be appreciated!

View 4 Replies View Related

How To Assign The SELECT Statement Output To A Local Variable?

May 7, 2008

In my program i have function that will get one value from Database.
Here i want to assign the output of the sql query to a local variable.
Its like select emp_id into Num from emp where emp_roll=222;
here NUM is local variable which was declared in my program.
Is it correct.?
can anyone please guide me..?

View 7 Replies View Related

Can You Have Multiple Output Parameters? Difference Between A Select Statement?

Mar 9, 2004

I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?

Do I need just a select statement to do this? What is the difference between the output and select statements?

Thanks in advance.

View 1 Replies View Related

Hiding Or Removing Column Output From Select Statement

Feb 22, 2005

I'm executing the following...

select COL1, min(COL2) from TABLE group by COL1

the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.

I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"

would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?

this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.

tia

a

View 2 Replies View Related

Formating Of Columns In Output Of A SQL Statement In Query Analyzer

Jan 16, 2004

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
------------------------- -----------------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---------- ----------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

View 7 Replies View Related

SQL 2012 :: Store Procedure Only Output One Select Statement

May 28, 2014

There are about 10 select statements in a store procedure.

All select statements are need.

Is it possible to output only the result of last select statement?

View 2 Replies View Related

Transact SQL :: Unable To Get Required Output Using Case Statement

May 17, 2015

Table Structure
EID   COLA   COLB
1   name   A
1   age   23
1   city   hyd
1   email   abc@live.in
1   mobile   45126
2   name   B
2   age   43

[code]....

how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null

View 9 Replies View Related

Having Problems Creating An SQL Statement

Oct 28, 2007

I am having trouble getting the SQL statement to return stats from a survey the way I want them. The table is set up as:
ID Q1 Q2 Q3 Q4
Responses for each question (Columns Q1 – Q4) will be a numerical value between 1-5. I want to count how many 1s, 2s, 3s, etc. I have tried different joins, self joins, unions and sub selections but cannot get the correct output.
I would like to get the output for each question as a single record, and if possible have a final column with an average for the question. But I can do that in the data binding if needed. Qs        Ones    Twos    Threes Fours   Fives
Q1       #of 1s   #of 2s   #of 3s   #of 4s   #of 5s
Q2       #of 1s   #of 2s   #of 3s   #of 4s   #of 5s
Q3       #of 1s   #of 2s   #of 3s   #of 4s   #of 5s
Any tips or SQL sample statements would be greatly appreciated.

View 5 Replies View Related

Select Statement That Will Output Related Rows With Different Column Data Per Row?

Apr 27, 2008

Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:

rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...

where for each ID, I have 3 rows that are associated with it and with different corresponding values.

I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.

i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..

After my loop, I will just do a select * from #someTempTable

Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005

View 2 Replies View Related

Creating Insert Statement Script

Dec 14, 2000

how can insert statement script of data present in sql server can
be generated in a .sql file

View 3 Replies View Related

Creating Table With A Select Statement

Nov 27, 2006

Dear folks,

create table temptable(eno, ename) as select eno, ename from emp.

here the problem is it is asking for the datatype for the temporary table.

is it not possible to create the temp table without providing the datatypes?

thank you very much.

Vinod

View 8 Replies View Related

Help Creating A SELECT Statement For Today

Jan 25, 2006

Hello,I am attempting to build a MS SQL query that will return data from"today"; today being current day 8:00AM-10:00PM today. My goal is toreturn the data from a table that is written to throughout the day, thequery will provide the current grade of service in our call center.I am having difficulty defining my where clause:- I can accomplish my goal my statically defining my 'date between' asthe actual date and time (not ideal)- I can accomplish the second part of my date using CURRENT_TIMESTAMP;but I am unable to define the starting pointHere is where I am thus far:/* We are going to count the total calls into each queue from start ofbusiness today (8:00AM) to now */select COUNT(Result) as "Total Sales Calls Offered" fromdbo.QueueEncounterwhere Direction='0'andQueueID='1631'and/* This is where I get lost */Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())Clearly the last line returns zero as there are no calls between thesame date range. How can I add to that line, or write this to work?Any thoughts?Thanks for the help.-Chris

View 4 Replies View Related

Help Needed Creating Select Statement

Mar 20, 2007

Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!

View 2 Replies View Related

Need Help Creating Statement Using OPENQUERY AND INSERT

Nov 7, 2006

I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.

Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')

View 6 Replies View Related

Help Creating A Transact SQL Insert Statement

Jun 3, 2006

Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.

To explain, I have two web pages. search.asp and results.asp.

search.asp has the following.

Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName

results.asp has the following SQL which pulls all the results.

SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC

The database & form I want to insert into.

tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the 'Location' result here"
category (Text) "Want to insert the 'CategoryTable' result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the 'Keyword' result here"

Please ask if u need more info.

Mally

View 1 Replies View Related

Creating A SQL Statement Store Procedure

Sep 28, 2007

I'm trying to do this:

CREATE PROCEDURE [dbo].[spStoryDisplay]

-- Add the parameters for the stored procedure here

@clsYear int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT *, "Class Year" =

CASE

WHEN year1 >= 1940 AND year1 < 1950 THEN '1940s'

WHEN year1 >= 1950 AND year1 < 1960 THEN '1950s'

WHEN year1 >= 1960 AND year1 < 1970 THEN '1960s'

WHEN year1 >= 1970 AND year1 < 1980 THEN '1970s'

WHEN year1 >= 1980 AND year1 < 1990 THEN '1980s'

WHEN year1 >= 1990 AND year1 < 2000 THEN '1990s'

WHEN year1 >= 2000 AND year1 < 2010 THEN '2000s'

END

FROM dvlStory

WHERE "Class Year" = @clsYear

ORDER BY lstName ASC

END

I kept getting this error:

Msg 207, Level 16, State 1, Procedure spStoryDisplay, Line 27

Invalid column name 'Class Year'.
So, my question is, how do I compare to the additional column "Class Year" to the parameter?

View 13 Replies View Related