Help with SQL Query

Hello Everyone,

I need help writing this query. I have a table named "PMP" that stores up to 10 objective ID's:

Code Snippet

SELECT ObjectiveID1,ObjectiveID2,ObjectiveID3,ObjectiveID4,... ObjectiveID10

FROM PMP

WHERE PMPID = @.PMPID

I have a child table named "METRICS" that stores the details for each objective:

Code Snippet

SELECT ObjectiveID, TitleText, Description, KPIvalue

FROM METRICS

WHERE ObjectiveID IN (query the PMP table)

I need to write a query that will return all the Metrics that are used for a given PMPID. Any ideas how this can be done?

Thank You

My first suggestion is to correct a bad design mistake. You have a severly de-normalized table.

You most likely 'should' NOT have columns Objective1-Objective10.

You most likely would benefit from having a separate table for Objectives.

THEN issues such as this one would become MUCH easier to solve.

If you are using SQL 2005, you may find the UNPIVOT statement to be useful.

|||

Which version of SQL Server are you using?

AMB

|||

I am using SQL Server 2005 Express Edition

I understand why you would think this table is de-normalized, but it's the right structure for the solution. My organization allows a maximum of 10 objectives to be identified per request. I need to send my crystal report 1 record.

Can someone help me with the UNPIVOT query?

Thank You

|||

I figured it out:

SELECT * FROM COACHING.METRICS

WHERE OBJECTIVEID IN (

SELECT OBJECTIVEID

FROM

(SELECT RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID

FROM COACHING.PMPFORMS

WHERE PMPID = 271 ) OBJ

UNPIVOT

(OBJECTIVEID FOR PMPID IN (RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID)) VALS)

|||

Thanks Arnie Rowland and hunchback for your reponses

|||I'm glad we could point you in a direction that worked.sql

Help with Sql Cache Dependency

Hello all.. i'm having a major issue with my Sql Express 2005 database :(

I'm using wicked code sitemap to allows for a sitemap to be stored in a database.. it uses sql cache dependency to invalidated the cache bla bla

Problem:

After i update a record / add new record the database generates a SqlQueryNotificationStoredProcedure

But it never gets executed. I've done tests on the code it's calling everything accordingly so i'm assuming the problem lies in a configuration setting of the database

done alot of searching around the net and i've found this

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username

i'm using Windows Authentication how do i run this and will this solve the problem

Thank you in advance

Chris

Any ideas anyone?|||

Well I've found something out..

The sitemap provided from wicked code only invalidates the cache when a new item has been inserted.. from what i can tell anyway

So updates are not displayed.. to get around this problem i've disallowed updates to the sitemap.. i could of writen a stored procedure to delete and recreate the entry however.

If you try deleting and recreating be careful of the ordering if you have set the ID to auto increment.

Happy Coding :D

All the above isWRONG.. i thought it was updating because of visual studios virutal webserver type of thing.. read below post for the correct answer..

Chris

|||

For all those ppl out there strugling with the Wicked Code Sitemap I have some new findings....

Please post thanks if i've helped anyone so i know weather it's worth updating these answers lol..

first of all

1. SQL EXPRESS 2005 DOES NOT SUPPORT QUERY NOTIFICATIONS, so site map will not update on database changes!!!

2. took me hours to find that out stupid me should read up on sql express before introducing it.. :(

3. to fix the updating problem add the following procedure to the SqlSiteMapProvider

publicvoid Refresh()

{

Clear();

_nodes.Clear();

_root =null;

}

personally i just refresh the sitemap on every page request in my master page... this isn't the best way.. only did it because the site doesn't get much traffic and it was easy bad bad bad programmer chris.. lol.. you should only refresh the sitemap on database changes..

please help my mate get more hitswww.drakesterling.com that will display the sitemap too.. i had to do alot of modifications to the CSS adapters to get it working properly any questions on what's shown here i'm happy to answer..

Happy programming all

From Chris

sql

Help with sp code - cursor

Friends,
When I step through the following code in QA it works fine - when I alter
the proc and run it, I get an error (you should be able to copy the code as
it is into your own QA for testing) - Anybody know where I am making the
mistke? Thanks in advance for your help ... Bill Morgan
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values
(@.dater)
set @.dater = @.dater + 1
end
update #main
set ca = 'a'
select * from #main
set nocount off
returnYour table #main doesn't have a column "ca" in it, just a column "Dates":

> update #main
> set ca = 'a'
"bill_morgan" <bill_morgan@.discussions.microsoft.com> wrote in message
news:7F945501-89B0-4B6C-81BC-4767C92B0464@.microsoft.com...
> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code
> as
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Look at where you create the #Main table, there is no "ca" column defined in
it, only a "dates" column... Therefore, later on where you try to update the
'ca' column, it fails... Can;t begin to suggest a fix until I know what
Stored Proc is SUpposed t odo...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Sorry, Now I see what you're doing...
Wat's wrong is that You have an Open uncommitted transaction
just delete the Begin Tran line and try it again... If you need the tran,
then you have to put in a corresponding Commit tran...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Here is the explanation of what is happening.
http://groups.google.ca/groups?selm...FTNGP11.phx.gbl
Also, you have a begin transaction without a matching commit / rollback.
Example:
-- I commented the begin transaction
use northwind
go
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
--begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values (@.dater)
set @.dater = @.dater + 1
end
exec ('update #main set ca = ''a''')
select * from #main
set nocount off
return
go
exec tester
go
drop procedure tester
go
AMB
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Bill, What is going on is that SQL7/2000 has what is called delayed
verification o(or something like that) which basically does NOT check the
column names of tables whoch ddo not currently exist when you create the
Stored Proc. It waits until run time... Then it checks again, BEFORE The
stored Proc runs, to make sure that every column and table exists...
So what's going on here is that the compiler sees that you're going to
create the #Main table, and that it will have a column named 'dates', but it
doesn't (no way it can) see that you're going to alter the table and add all
those state name columns, so the Update #Main Set CA = 'a' line fails the
compiler test...
If you comment that line out, (and fx the Open Transaction issue), the code
will work.
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||my apologies for the begin tran statement - i was monkeying with the
procedure and forgot to take that out - once it's removed you can step
through the procedure, but trying to run it all at once fails ...
"CBretana" wrote:
> Sorry, Now I see what you're doing...
> Wat's wrong is that You have an Open uncommitted transaction
> just delete the Begin Tran line and try it again... If you need the tran,
> then you have to put in a corresponding Commit tran...
> "bill_morgan" wrote:
>|||Thanks for the guidance ... my apologies for the begin tran - i forgot to
take that out before I posted this question (I thought the begin tran /
commit tran) might fix things ... I am visiting the sight you suggest ...
thanks ...
"Alejandro Mesa" wrote:
> Here is the explanation of what is happening.
> [url]http://groups.google.ca/groups?selm=uxV68C33DHA.3468%40TK2MSFTNGP11.phx.gbl[/url
]
> Also, you have a begin transaction without a matching commit / rollback.
> Example:
> -- I commented the begin transaction
> use northwind
> go
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> --begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values (@.dater)
> set @.dater = @.dater + 1
> end
> exec ('update #main set ca = ''a''')
> select * from #main
> set nocount off
> return
> go
> exec tester
> go
> drop procedure tester
> go
>
> AMB
>
> "bill_morgan" wrote:
>|||Yes ..!! I created a new proc to handle that final update ... Proc 1 calls
Proc 2 and it works great ... thank you for the new knowledge ...
"CBretana" wrote:
> Bill, What is going on is that SQL7/2000 has what is called delayed
> verification o(or something like that) which basically does NOT check the
> column names of tables whoch ddo not currently exist when you create the
> Stored Proc. It waits until run time... Then it checks again, BEFORE The
> stored Proc runs, to make sure that every column and table exists...
> So what's going on here is that the compiler sees that you're going to
> create the #Main table, and that it will have a column named 'dates', but
it
> doesn't (no way it can) see that you're going to alter the table and add a
ll
> those state name columns, so the Update #Main Set CA = 'a' line fails the
> compiler test...
> If you comment that line out, (and fx the Open Transaction issue), the cod
e
> will work.
> "bill_morgan" wrote:
>sql

Help with SOAP Lite client error problems - CGI code

Hi,

I have been working on a soap client project for over a week now and can not figure out what I am doing wrong. I am not an expert using Perl or SOAP so any help would be "greatly" appreciated.

I get basic errors throughout, starting with:
Error - SOAP::Transport::HTTP::Client::send_receive: POST
It seems as if the user access information is not carried through as it should, but I truly have no idea what the problem is.

Here is what I have put together so far.

Thanks,
Mark

sub procgetmemberinfo {

my $pin = "$form{'lPin'}";
my $password = "$form{'sPassword'}";

my $soap = SOAP::Lite
-> uri('https://xmlsql.XXXXX.xxx:441')
-> on_action( sub { join '/', 'https://xmlsql.XXXXX.xxx:441', $_[1] } )
-> proxy('https://xmlsql.XXXXX.xxx:441/service.asmx?WSDL');

my @.params = (
SOAP::Data->name(lPin => $pin),
SOAP::Data->name(sPassword => $password)
);

my $method = SOAP::Data->name('GetMemberInfo')->attr({xmlns => 'https://xmlsql.XXXXX.xxx:441/'});

my $result = $soap->call($method => @.params);
unless ($result->fault) {

my $title = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/TITLE');
my $firstname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/FIRST');
my $middlename = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/MIDDLE');
my $lastname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/LAST');
my $suffix = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/SUFFIX');
my $address1 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STREET_ADDRESS');
my $address2 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ADDRESS2');
my $city = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/CITY');
my $stateprovince = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STATE_PROV');
my $postalcode = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ZIP');
my $country = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/COUNTRY');
my $homephone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/HOME_PHONE');
my $workphone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/WORK_PHONE');
my $emailaddress = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/EMAIL');

print "<font face=arial size=2><b>PIN Number:</b> $pin</font><br>";
print "<font face=arial size=2><b>Password:</b> $password</font><br>";
print "<font face=arial size=2><b>Title:</b> $title</font><br>";
print "<font face=arial size=2><b>First Name:</b> $firstname</font><br>";
print "<font face=arial size=2><b>Middle Name:</b> $middlename</font><br>";
print "<font face=arial size=2><b>Last Name:</b> $lastname</font><br>";
print "<font face=arial size=2><b>Suffix:</b> $suffix</font><br>";
print "<font face=arial size=2><b>Address1:</b> $address1</font><br>";
print "<font face=arial size=2><b>Address2:</b> $address2</font><br>";
print "<font face=arial size=2><b>City:</b> $city</font><br>";
print "<font face=arial size=2><b>State:</b> $stateprovince</font><br>";
print "<font face=arial size=2><b>ZIP/Postal Code:</b> $postalcode</font><br>";
print "<font face=arial size=2><b>Country:</b> $country</font><br>";
print "<font face=arial size=2><b>Home Phone:</b> $homephone</font><br>";
print "<font face=arial size=2><b>Work Phone:</b> $workphone</font><br>";
print "<font face=arial size=2><b>Email Address:</b> $emailaddress</font>";

}

else {
print join ', ',
$result->faultcode,
$result->faultstring,
$result->faultdetail;
}

}

Here is a nice easy way to debug SSL issue with Windows 2003 that I discovered while working on SOAP projects.

1. Create batch file c:\ssltrace.cmd with following contents:

logman start http_ssl_trace -pf c:\guids.txt -o out.etl -ets
pause
logman stop http_ssl_trace -ets
tracerpt /y out.etl
notepad dumpfile.csv

2. Create file c:\guids.txt with following contents:

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

3. On the web service machine run c:\ssltrace.cmd to start tracing, then hit your web service with your POST. Once you are finished testing press the spacebar to in the ssltrace.cmd command window to stop tracing and display the trace file. You should see everything coming in and going out along with error codes, etc...

|||

Hello Matt,

your Message was helpfull to solve a problem with tracing.

Could you please tell me where you get the information

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

from?

Thanks

str01014

|||

Actually I updated my batch file to inline everything (i've included it below).

To get a list of trace providers, run this ->

C:\>logman query providers

Provider GUID
-
...

IIS: SSL Filter {1fbecc45-c060-4e7c-8a0e-0dbd6116181b}
...
HTTP Service Trace {dd5ef90a-6398-47a4-ad34-4dcecdef795f}.
...

This will list each trace provider and it's GUID. The 2nd and 3rd values are flags passed to the trace provider to turn on and off various traces. How these values are used are provider specific. However if you just want to turn on all the flags pass in 0xFFFFFFFF and 255 like in my example below. Search MSDN for HTTP Service Trace and SSL Filter for information on what these flags do.

Here is my updated batch file (note it requires logparser tool which is downloadable from Microsoft) ->

rem START SSL TRACE DEMO
if exist ssltrace.guids del ssltrace.guids
echo {1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0xFFFFFFFF 255 IIS: SSL Filter >> ssltrace.guids
echo {dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0xFFFFFFFF 255 HTTP Service Trace >> ssltrace.guids
logman start ssltrace -pf ssltrace.guids -o out.etl -ets
rem PRESS <SPACEBAR> TO STOP SSL+HTTP TRACING AND VIEW RESULTS
pause
logman stop ssltrace -ets
if exist ssltrace.guids del ssltrace.guids
if exist ssltrace.log del ssltrace.log
logparser -i:ETW "select * from out.etl" -e:ON >> ssltrace.log
start notepad ssltrace.log
rem END SSL TRACE DEMO

Help with SOAP Lite client error problems - CGI code

Hi,

I have been working on a soap client project for over a week now and can not figure out what I am doing wrong. I am not an expert using Perl or SOAP so any help would be "greatly" appreciated.

I get basic errors throughout, starting with:
Error - SOAP::Transport::HTTP::Client::send_receive: POST
It seems as if the user access information is not carried through as it should, but I truly have no idea what the problem is.

Here is what I have put together so far.

Thanks,
Mark

sub procgetmemberinfo {

my $pin = "$form{'lPin'}";
my $password = "$form{'sPassword'}";

my $soap = SOAP::Lite
-> uri('https://xmlsql.XXXXX.xxx:441')
-> on_action( sub { join '/', 'https://xmlsql.XXXXX.xxx:441', $_[1] } )
-> proxy('https://xmlsql.XXXXX.xxx:441/service.asmx?WSDL');

my @.params = (
SOAP::Data->name(lPin => $pin),
SOAP::Data->name(sPassword => $password)
);

my $method = SOAP::Data->name('GetMemberInfo')->attr({xmlns => 'https://xmlsql.XXXXX.xxx:441/'});

my $result = $soap->call($method => @.params);
unless ($result->fault) {

my $title = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/TITLE');
my $firstname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/FIRST');
my $middlename = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/MIDDLE');
my $lastname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/LAST');
my $suffix = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/SUFFIX');
my $address1 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STREET_ADDRESS');
my $address2 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ADDRESS2');
my $city = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/CITY');
my $stateprovince = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STATE_PROV');
my $postalcode = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ZIP');
my $country = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/COUNTRY');
my $homephone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/HOME_PHONE');
my $workphone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/WORK_PHONE');
my $emailaddress = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/EMAIL');

print "<font face=arial size=2><b>PIN Number:</b> $pin</font><br>";
print "<font face=arial size=2><b>Password:</b> $password</font><br>";
print "<font face=arial size=2><b>Title:</b> $title</font><br>";
print "<font face=arial size=2><b>First Name:</b> $firstname</font><br>";
print "<font face=arial size=2><b>Middle Name:</b> $middlename</font><br>";
print "<font face=arial size=2><b>Last Name:</b> $lastname</font><br>";
print "<font face=arial size=2><b>Suffix:</b> $suffix</font><br>";
print "<font face=arial size=2><b>Address1:</b> $address1</font><br>";
print "<font face=arial size=2><b>Address2:</b> $address2</font><br>";
print "<font face=arial size=2><b>City:</b> $city</font><br>";
print "<font face=arial size=2><b>State:</b> $stateprovince</font><br>";
print "<font face=arial size=2><b>ZIP/Postal Code:</b> $postalcode</font><br>";
print "<font face=arial size=2><b>Country:</b> $country</font><br>";
print "<font face=arial size=2><b>Home Phone:</b> $homephone</font><br>";
print "<font face=arial size=2><b>Work Phone:</b> $workphone</font><br>";
print "<font face=arial size=2><b>Email Address:</b> $emailaddress</font>";

}

else {
print join ', ',
$result->faultcode,
$result->faultstring,
$result->faultdetail;
}

}

Here is a nice easy way to debug SSL issue with Windows 2003 that I discovered while working on SOAP projects.

1. Create batch file c:\ssltrace.cmd with following contents:

logman start http_ssl_trace -pf c:\guids.txt -o out.etl -ets
pause
logman stop http_ssl_trace -ets
tracerpt /y out.etl
notepad dumpfile.csv

2. Create file c:\guids.txt with following contents:

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

3. On the web service machine run c:\ssltrace.cmd to start tracing, then hit your web service with your POST. Once you are finished testing press the spacebar to in the ssltrace.cmd command window to stop tracing and display the trace file. You should see everything coming in and going out along with error codes, etc...

|||

Hello Matt,

your Message was helpfull to solve a problem with tracing.

Could you please tell me where you get the information

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

from?

Thanks

str01014

|||

Actually I updated my batch file to inline everything (i've included it below).

To get a list of trace providers, run this ->

C:\>logman query providers

Provider GUID
-
...

IIS: SSL Filter {1fbecc45-c060-4e7c-8a0e-0dbd6116181b}
...
HTTP Service Trace {dd5ef90a-6398-47a4-ad34-4dcecdef795f}.
...

This will list each trace provider and it's GUID. The 2nd and 3rd values are flags passed to the trace provider to turn on and off various traces. How these values are used are provider specific. However if you just want to turn on all the flags pass in 0xFFFFFFFF and 255 like in my example below. Search MSDN for HTTP Service Trace and SSL Filter for information on what these flags do.

Here is my updated batch file (note it requires logparser tool which is downloadable from Microsoft) ->

rem START SSL TRACE DEMO
if exist ssltrace.guids del ssltrace.guids
echo {1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0xFFFFFFFF 255 IIS: SSL Filter >> ssltrace.guids
echo {dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0xFFFFFFFF 255 HTTP Service Trace >> ssltrace.guids
logman start ssltrace -pf ssltrace.guids -o out.etl -ets
rem PRESS <SPACEBAR> TO STOP SSL+HTTP TRACING AND VIEW RESULTS
pause
logman stop ssltrace -ets
if exist ssltrace.guids del ssltrace.guids
if exist ssltrace.log del ssltrace.log
logparser -i:ETW "select * from out.etl" -e:ON >> ssltrace.log
start notepad ssltrace.log
rem END SSL TRACE DEMO

sql

Help with Select Statement

Edited by moderator XIII, please don't use [ code ] tags around your code. Instead change your Profile | Site settings to use the Rich editor (with code support) to insert colorized code and line numbers:

I have a select statement that follows:

SELECT Events.legendID AS Events_legendID,
Units.unitID AS Units_unitID,
Units.msbbID, Units.belongsTo,
Units.name AS unitName,
Legend.legendID AS Legend_legendID,
Legend.Color,
Legend.Name legendName,
Legend.Active,
Events.unitID AS Events_UnitID,
Events.userID AS eventUserID,
Events.startDate,
Events.endDate,
Events.eventID,
Events.Title
FROM Events INNER JOIN Units ON Events.unitID = Units.unitID
AND Events.unitID = Units.unitID INNER JOIN Legend ON Events.legendID = Legend.legendID
WHERE startDate BETWEEN convert(datetime, '4/01/2006') AND convert(datetime, '6/30/2006')
ORDER BY unitName ASC;

This code works great with the exception of the events that start and end outside of the quarter dates I am entering. For example if there is an event that start in January and ends in August then I need to show that event in the 1st quarter, 2nd quarter and 3rd quarter.

any help on how to get those events that start and end outside the 'active' quarter BUT pertain to the active quarter?

thanks in advance!

Hello, if i get you right, maybe this would work:

WHERE
startDate <= convert(datetime, '6/30/2006') AND
endDate >= convert(datetime, '4/01/2006')

Anyway, i'd suggest you craft your data structure a bit more, to work on "quarters" rather than generic (unconstrained) dates...

HTH -LV

|||what do you mean by "... work on quarters rather then generic..."?|||

Mmm, what's unclear? So to say, i'd rather not write a treaty...

Also, i'm not such an expert with SqlServer either, so i guess we'll need someone else for a "ground-breaking" implementation sample...

-LV

Help with SELECT

Can anyone point me in the right direction, the code below returns results but the date range imposed by the BETWEEN command doesn't seem to work, asall the results from the table are shown.

<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
AND date BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>

Regards

Tino

What format is your date column in the DB table? You can try using:

WHERE...AND date <='25/11/2007' AND date >= '26/11/2007'

Hope this helps,

Vivek

|||

Vivek

Do you mean the date type, if so its a datetime.

Thanks

Tino

|||

Hitinomclaren ,

try like this

ANDCONVERT(DATETIME,'date',101) BETWEEN CONVERT(DATETIME,'2007-11-25',101) AND CONVERT(DATETIME,'2007-11-26',101)

or

AND (date > '2007-11-25' anddate < '2007-11-26' )

Regards,
Shri

|||

Its a good practice to apply the conversion to all the participants in any comparison. So, your query should be like

"SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
ANDCONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)"

Hope this helps.

|||

Thanks for suggestions, they didn't work for me

I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.

After 2 days at this one problem im trying not to get down but its really getting hard now

My database is SQLExpress and i've used the datetime datatype in the date colum on the DB. Im using VS2008 beta 2.

I have 20 test records in my DB and all have the date column filled in, im not using thetimepart but they are there in the DB and all default to midnight 00:00:00.

I dont know how to use stored procedures yet but im looking at the video tutorial form thelearn part of this site to hopfully get to grips with it, im assuming that the examples you have given me will work in either on the page (in the SqlDataSource as above) or in a Stored Procedure. Is there any Difference in the Syntax when switching between the two?

When I tried the suggestions you gave me using the Convert way I get ALL the records in the DB and when I use the < > way I get No records.

What am i doing wrong?

thx

tino

|||

Can you post the table structure along with some sample records, so that we can understand your problem better ?

|||

Here's the Table structure......

Coumn Name Data Type

gigId int ---> Primary Key, Is Identity-->Yes

venue varchar(50)

town varchar(50)

artist varchar(50

date datetime

I've looked at the datatypes available and there isn't just a date (without the time) must be an SqlExpress thing.

I cant even hard code 1 date in the SELECT command i.e.

SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE (town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)AND (date = '25/11/2007') ">

thx tino

|||

tinomclaren:

Thanks for suggestions, they didn't work for me

I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.

Yes, you are missing something fundamental. :)

You cannot mix AND with OR as you have done and expect to get correct results.

In your example, all the ORs need to be within a set of () .

() must be used to delimit the logic boundaries of the ANDs and ORs.

Example:

If I tell you I will only date women who have red hair and blue eyes or green eyes, what do I mean?

Do I mean the women must have red hair - then they must also have blue eyes or green eyes?

Or would a blonde with green eyes qualify?

My original statement was ambiguous.

If, instead, I said, "I will only date women with red hair and (blue eyes or green eyes)", then the blonde with green eyes won't qualify.

If I said "I will only date women with (red hair and blue eyes) or green eyes" then the blonde with green eyes will qualify. So would a redhead with green eyes, for that matter.

HTH

|||

David,

Once again you have saved my life. I can only hope that one day I will be as experienced as you.

Two days for 2 brackets........I wont forget this.

Thankyou

Tino

|||

Thanks everyone for your help....All answers turned out to be correct......this forum is simply superb :)

For anyone with a similar problem here is the final code I used, which works perfectly....

<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE((town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)) AND CONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103) ORDER BY date asc">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>

Brackets inbold made the whole thing work correctly

thnx

Tino

Help with search code

I have been using the following code on a search page for some time, is has worked very well. We recently changed our database to support multiple addresses for each client. So I added the INNER JOIN on the tblClientAddresses. But now when I try to search on a ID I get an ambiguous cloumn name error on the ID. Can anyone see how I could correct this?

Thanks for any suggestions,

Sub BindDataForPaging(ByVal sortExpr As String)
 Dim MyConnection As SqlConnection
 Dim MySQLAdapter As SqlDataAdapter
 Dim DS As DataSet
 Dim ConnectStr As String
 Dim WhereClause As String
 Dim SelectStatement As String
 If SearchLastName.Text = "" And SearchFirstName.Text = "" And _
 SearchID.Text = "" And SearchCompanyName.Text = "" And _
 SearchSal1.Text = "" And SearchComment.Text = "" And SearchAddress.Text = "" Then
 Message.Text = "You didn't enter any search parameters. Try Again."
 Exit Sub
 End If
 WhereClause = "Where "
 If SearchLastName.Text <> "" Then
 WhereClause = WhereClause & "[LastName] Like '" & _
 SearchLastName.Text & "%" & "' AND "
 End If
 If SearchFirstName.Text <> "" Then
 WhereClause = WhereClause & "[FirstName] Like '" & _
 SearchFirstName.Text & "%" & "' AND "
 End If
 If SearchID.Text <> "" Then
 WhereClause = WhereClause & "[ID] = " & _
 SearchID.Text & " AND "
 End If
 If SearchCompanyName.Text <> "" Then
 WhereClause = WhereClause & "[CompanyName] Like '" & _
 SearchCompanyName.Text & "%" & "' AND "
 End If
 If SearchSal1.Text <> "" Then
 WhereClause = WhereClause & "[Sal1] Like '" & _
 SearchSal1.Text & "%" & "' AND "
 End If
 If SearchComment.Text <> "" Then
 WhereClause = WhereClause & "[Comments] Like '" & "%" & _
 SearchComment.Text & "%" & "' AND "
 End If
 If SearchAddress.Text <> "" Then
 WhereClause = WhereClause & "[Address] Like '" & "%" & _
 SearchAddress.Text & "%" & "' AND "
 End If
 If ClientTypeDrop.SelectedItem.Text <> "" Then
 WhereClause = WhereClause & "[CLientType] Like '" & "%" & _
 ClientTypeDrop.SelectedItem.Text & "%" & "' AND "
 End If
 If Right(WhereClause, 4) = "AND " Then
 WhereClause = Left(WhereClause, Len(WhereClause) - 4)
 End If
 SelectStatement = "Select *,A.Address FROM tblClients INNER JOIN dbo.tblClientAddresses A ON dbo.tblClients.ID = A.ID " & WhereClause & " ORDER BY " & sortExpr
 Try
 ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
 MyConnection = New SqlConnection(ConnectStr)
 MySQLAdapter = New SqlDataAdapter(SelectStatement, MyConnection)
 DS = New DataSet
 MySQLAdapter.Fill(DS)
 MyDataGrid.DataSource = DS
 MyDataGrid.DataBind()
 Catch objException As SqlException
 Dim objError As SqlError
 For Each objError In objException.Errors
 Response.Write(objError.Message)
 Next
 End Try
 End Sub

WhereClause = WhereClause & "[ID] = " & _

Which tables ID do you mean?|||I tried both as below and got a runtime error, but it is the ID from tblClients that I would like.

WhereClause = WhereClause & "tblClients.[ID] = " & _

Thank you,|||That's exactly what you need to do. You're still getting an error - and its still the same error? Can you just print out the resulting query rather than all the string concats?|||Try this instead:

WhereClause = WhereClause & "dbo.tblClients.[ID] = " & _

If this doesn't work, we need to see what SelectStatement contains exactly. Plus an exact error message would be helpful.

Terrisql

Help with script and variables

Hi If I try to run the code below I get the following errors

Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'view'.
Server: Msg 170, Level 15, State 1, Line 51
Line 51: Incorrect syntax near '@.month1'.

I am not sure why it does not like the keyword view ? also I am trying to use the variables in the column name of the create table but again it does not like this.

-- declare all variables!

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate
and date_and_time <= @.enddate1

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money,
@.month2 money,
@.month3 money
)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

For starters, the CREATE VIEW statement has to be the first statement in a batch so it can't be used in this way.

You could dynamically create your CREATE VIEW statement then use sp_executesql to execute the command.

Chris

|||

Hi thanks for the reply

I am quite new to this what do you mean by dynamically create the CREATE VIEW statement ?

|||

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate -- you cant have variabl inside view
and date_and_time <= @.enddate1 -- you cant have variabl inside view

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money, -- Remove @.
@.month2 money, -- Remove @.
@.month3 money -- Remove @.

)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

and also tell us what u are intended to do... there are couple of wrong sysntax in the script... you can not use variable inside a view definition...

Madhu

|||

Hi

First of I am trying to create a view of data between a specific date range. This is the last three months. So if it was to run today then the view would contain data from 1st nov 2006 to 28th feb 2007.

When it runs on the 1st Apr the view would contain data from 1st Dec to 31st march and so on. This is why I am trying to drop the view first then create it. I am then running a query on the view and inserting the results of this into the table i create in the script.

Secondly as I am working with a rolling 3 months of data I need to drop the table I insert the data into an create it again with the correct column headings ie NOV,DEC,JAN. This is why I have the variables in the create view and create table statements.

If this is not possible is their an other way of doing this ?

Hope that makes sense

|||

I wouldn't bother creating a View for temporary purposes, you can use the SELECT statement as is to directly insert data into tables.

Try the code below to drop and create your table - I see no need for a cursor in the scenario you have presented.

Chris

DECLARE @.startdate DATETIME

DECLARE @.enddate DATETIME

DECLARE @.enddate1 DATETIME

DECLARE @.month1 NVARCHAR(10)

DECLARE @.month2 NVARCHAR(10)

DECLARE @.month3 NVARCHAR(10)

DECLARE @.TableName NVARCHAR(100)

DECLARE @.SQLString NVARCHAR(4000)

SELECT @.startdate = dbo.removetime(DATEADD(month, -3, getdate())),

@.enddate = dbo.removetime(DATEADD(month, -2, getdate())-1),

@.enddate1 = dbo.removetime(DATEADD(month, 0, getdate())-1),

@.month1 = left(dbo.removetime(DATEADD(month, -3, getdate())),5),

@.month2 = left( dbo.removetime(DATEADD(month, -2, getdate())-1),4),

@.month3 = left(dbo.removetime(DATEADD(month, 0, getdate())-1),4)

--The name of the new table

SELECT @.TableName = N'Temp_Table'

--Build the string to drop the table

SET @.SQLString =

N'IF OBJECT_ID(' + QUOTENAME(@.TableName, '''') + ') IS NOT NULL DROP TABLE [' + @.TableName + '];'

--Build the string to create the table

SET @.SQLString = @.SQLString +

N'CREATE TABLE [' + @.TableName + ']

(

[account_no] int,

[account_holder_surname] varchar(80),

[account_holder_forename] varchar(80),

[' + @.month1 + '] money,

[' + @.month2 + '] money,

[' + @.month3 + '] money

)'

--Execute the statements

EXEC(@.SQLString)

--Prove that the new table exists

--EXEC sp_help 'Temp_Table'

/*

--Don't create a 'temporary' view - just use the query directly like this...

INSERT INTO.... / UPDATE etc...

select column1,column2

from some_table

where date_and_time >= @.startdate

and date_and_time <= @.enddate1

*/

|||

Hi Chris

Thanks so much. It was the syntax around the create table I could not get my head around !

Thanks

Help with sample code for ssis surrogate key transform

I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.

Thanks

Basically - here is a surrogate key Transform Script - to generate image numbers for products
Input is ProdNum column - output ImgNo colum.
The idea is to get the result like this:
ProdNum ImgNo
1 1
1 2
2 1
3 1
3 2

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim imgno As Short, incr As Short, prevProdNum As String

Public Sub New()
imgno = 0
incr = 1
prevProdNum = ""
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.ProdNum <> prevProdNum Then
imgno = incr
Else
imgno += incr
End If
Row.ImgNo = imgno
prevProdNum = Row.ProdNum
End Sub
End Class|||Also - you can check out this article "SSIS Generating Surrogate Keys"

help with rowcommand...please....

Hello. I have this code that updates my database. My problem is that it updates every row in the database. What I want to do is update only the selected rows.

What should be done so that only selected rows will be updated. Thanks in advance!

**************************************************************************************************************************************************************************************

If e.CommandName = "Select" Then

Dim index As Integer = Convert.ToInt32(e.CommandArgument)

Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)


Dim strSQL2 As New SqlCommand("UPDATE TE_zangyou SET ninka_nen_from=apply_nen_from, ninka_gatsu_from=apply_gatsu_from, ninka_hi_from=apply_hi_from, ninka_day_name_from=apply_day_name_from, ninka_ji_from=apply_ji_from,ninka_bun_from=apply_bun_from,ninka_nen_to=apply_nen_to, ninka_gatsu_to=apply_gatsu_to, ninka_hi_to=apply_hi_to, ninka_day_name_to=apply_day_name_to, ninka_ji_to=apply_ji_to,ninka_bun_to=apply_bun_to", cnn)

cnn.Open()




strSQL2.ExecuteNonQuery()

cnn.Close()
End If

**************************************************************************************************************************************************************************************

hi audrey,

you've not specified where clause in update query.

thanks,

satish.

|||

I tried this, it does not update.

Dim strSQL2 As New SqlCommand("UPDATE TE_zangyou SET ninka_nen_from=apply_nen_from, ninka_gatsu_from=apply_gatsu_from, ninka_hi_from=apply_hi_from, ninka_day_name_from=apply_day_name_from, ninka_ji_from=apply_ji_from,ninka_bun_from=apply_bun_from,ninka_nen_to=apply_nen_to, ninka_gatsu_to=apply_gatsu_to, ninka_hi_to=apply_hi_to, ninka_day_name_to=apply_day_name_to, ninka_ji_to=apply_ji_to,ninka_bun_to=apply_bun_toWHERE id=' '", cnn)

id is the primary key.

|||

I also tried this...

But there is an error,Must declare the scalar variable "@.syain_No".

Dim strSQL2 As New SqlCommand("UPDATE TE_zangyou SET ninka_nen_from=apply_nen_from, ninka_gatsu_from=apply_gatsu_from, ninka_hi_from=apply_hi_from, ninka_day_name_from=apply_day_name_from, ninka_ji_from=apply_ji_from,ninka_bun_from=apply_bun_from,ninka_nen_to=apply_nen_to, ninka_gatsu_to=apply_gatsu_to, ninka_hi_to=apply_hi_to, ninka_day_name_to=apply_day_name_to, ninka_ji_to=apply_ji_to,ninka_bun_to=apply_bun_toWHERE [syain_No] = @.syain_No", cnn)

|||

are you passing value parameter to update command like following

strSQL2.parameter.addwithvalue("@.syain_no", value); //value is value you need to pass like in grid_selectindexchanged event it should be value of current row's syain_no.

thanks,

satish.

|||

I'm not using -> strSQL2.parameter.addwithvalue("@.syain_no", value).

Im just updating a column from another column.

In my gridview, if button_1 is clicked, values should be updated. Im using rowcommand.

|||

you are updating record using following as per your earlier post

Dim strSQL2 As New SqlCommand("UPDATE TE_zangyou SET ninka_nen_from=apply_nen_from, ninka_gatsu_from=apply_gatsu_from, ninka_hi_from=apply_hi_from, ninka_day_name_from=apply_day_name_from, ninka_ji_from=apply_ji_from,ninka_bun_from=apply_bun_from,ninka_nen_to=apply_nen_to, ninka_gatsu_to=apply_gatsu_to, ninka_hi_to=apply_hi_to, ninka_day_name_to=apply_day_name_to, ninka_ji_to=apply_ji_to,ninka_bun_to=apply_bun_toWHERE [syain_No] =@.syain_No", cnn)

so you need to pass @.syain_no & its value as a parameter which is what i referring in my earlier post

strsql2.parameter.addwithvalue("@.syain_no", value) //value is value of gridview's cell in current row

hope you getting

thanks,

satish.

|||

Ok. I get it now. I'll let you know if I'll let it run...

Thanks so much.

|||

no problems. do let me know.

thanks,

satish.

|||

Here's the code. There is no error, but it does not update. What seems to be wrong?

If e.CommandName = "Select" Then



Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)

cnn.Open()
Dim strSQL2 As New SqlCommand("UPDATE TE_zangyou SET ninka_nen_from=apply_nen_from WHERE syain_No = @.syain_No", cnn)

strSQL2.Parameters.AddWithValue("@.syain_No", "syain_No")
strSQL2.Parameters.AddWithValue("@.apply_nen_from", "ninka_nen_from")

strSQL2.ExecuteNonQuery()
strSQL2.Parameters.Clear()

cnn.Close()
End If

|||

wait!!! it worked...

Thanks so much...

I added some codes...

Thanks...

|||

hi audrey

is syain_no character field, if yes use

Dim strSQL2 As New SqlCommand("UPDATE TE_zangyou SET ninka_nen_from=@.apply_nen_from WHERE syain_No = @.syain_No", cnn) //a little mistake
strSQL2.Parameters.AddWithValue("@.syain_No", "'" + syain_no + "'" ) //i assume syain_no is variable that contains value, which is mistake i think in your code & i've included in single quotes check properly please.

strSQL2.Parameters.AddWithValue("@.apply_nen_from", "'" + ninka_nen_from + "'") //same is case here.

hope it helps.

regards,

satish

|||ok. Thanks so much.|||

no worries.

cheers,

satish.

Help with rewriting code without cursor

Hello,
Just wondering if anyone can tell me the best way to rewrite the below code
without a cursor.
It's just passing each Id to a stored procedure.
Let me know if you need any more info.
Thanks & go easy on me, I know cursors tend to rile everyone up.
Declare cur_DeleteStuff Cursor Scroll For
Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID)
Open cur_DeleteStuff
Fetch First FROM cur_DeleteStiff into @.ChildTableID
While (@.@.Fetch_Status <> -1)
Begin
exec sp_SMART_ANADeleteFrom @.ChildTableID, 1, 0
If @.@.Error <> 0
BEGIN
ROLLBACK Transaction CDTTransfer
RAISERROR('Something Bad Happened, Updates ROLLED BACK!',1,1)
RETURN
END
Fetch Next FROM cur_Deletestuff into @.ChildTableID
END
Close cur_DeleteStuff
Deallocate cur_DeleteStuff"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> Hello,
> Just wondering if anyone can tell me the best way to rewrite the below
> code
> without a cursor.
> It's just passing each Id to a stored procedure.
> Let me know if you need any more info.
> Thanks & go easy on me, I know cursors tend to rile everyone up.
>
What is the code for the stored procedure: sp_SMART_ANADeleteFrom
If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
based on the ChildTableID
then you should be able to modify the delete to do something like the
following:
DELETE TableName
WHERE ChildTableID IN
(Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID))
One a side note: You should probably not be naming your user defined stored
procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
generally use for SQL Server system stored procedure which are found in the
master database and are available globally throughout the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for your help Rick,
Though the naming convention implies it's only deleting a child - it's
actually doing something completely different.
I still need to call the stored procedure for each table ID found.
Thanks for the sp_ info.
Lesley
"Rick Sawtell" wrote:

> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> What is the code for the stored procedure: sp_SMART_ANADeleteFrom
> If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
> based on the ChildTableID
> then you should be able to modify the delete to do something like the
> following:
> DELETE TableName
> WHERE ChildTableID IN
> (Select distinct TableID
> from tbl_DTM
> Where APID IN
> (Select TableID from tbl_DTM where
> supplierID = @.v_FromSupplierID))
>
> One a side note: You should probably not be naming your user defined stor
ed
> procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
> generally use for SQL Server system stored procedure which are found in th
e
> master database and are available globally throughout the system.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Whatever it does, we can't help you find a set-baset solution without you
posting the procedure.
ML|||Sorry, I was thinking I could just use the sp as is. I pasted it below. It
was written a while ago by someone else & is in production now.
Basically it's deleting rows from a table, then deleting the defining row
from another table based on the tableID
I'd welcome any input on how to change this to set based.
That may also address rollback issues I predict I will have.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_SMART_ANADeleteFrom
@.FromTableID INT = 0,
@.OKtoDeleteORIG BIT = 0,
@.DebugMode INT = 0
AS
DECLARE @.TableType CHAR(3)
DECLARE @.AnalyticParentID INT
DECLARE @.FromPhysicalTableName varchar(255)
DECLARE @.strSQL nvarchar(2000)
if @.FromTableID is null
begin
raiserror ('Invalid From Table ID.',1,1)
return
end
--
SELECT @.TableType = MyType,
@.AnalyticParentID = AnalyticParentID,
@.FromPhysicalTableName = PhysicalDataTableName
from
tbl_DataTableMaster where Tableid = @.FromTableID
BEGIN TRANSACTION DELETEfromAnalytics
-- Delete the rows from the Quarterly ANA table
SET @.StrSQL = N'DELETE FROM My_Users.' + @.FromPhysicalTableName +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error deleting rows. Table Deletion did NOT occur!!',1,1)
RETURN
end
--delete row from tbl_DataTableMaster
SET @.strSQL = N'DELETE FROM tbl_DataTableMaster ' +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE data table master Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error Deleting in Data Table Master. Table Deletion did NOT
occur!!',1,1)
RETURN
end
--
COMMIT TRANSACTION DELETEfromAnalytics
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Help with reading database.

I have this code that I hacked together from someone else's example. I kind of understand how it works. I just don't know if it will and i am not in a location right now to check. I was wondering if I did this correctly first, second how can it improve and should i do something different. Basically i just want to check the password in a database. I am passing the username and password to this function from another functio

private bool authUser(string UserName, string Password)
{
string connectionString = ConfigurationSettings.AppSettings["DBConnectionString"];

SqlConnection DBConnection = new SqlConnection(connectionString);

bool result = false;

DBConnection.open()
SqlCommand checkCommand = new SqlCommand("SELECT password FROM Users WHERE userName='" + Password + "', DBConnection)
SqlDataReader checkDataReader = checkCommand.ExecuteReader();

if(checkDataReader.GetString(0) == Password)
{
result = true;
}
else
{
result = false;
}
checkDataReader.Close();
DBConnection.Close();

return result;
}

Thank you
Buddy LindseyNo. It won't work.
You're using the Password in your SQL statement, not the username. So unless the users are really silly and use their usernames as their passwords, it's going to fail.
More seriously, using string concatenation to build query strings represents one of the biggest security disasters on the planet. Never, ever do it. Just search the web for "SQL Injection attack" to see what I mean.
You should also ponder whether == with string comparisons is case sensitive or not.
You can also leak connection resources because you're not using try {} finally {} blocks or the C# using(){} wrapper.
And I'm really hoping that the password is protected in some way (by hashing or encryption), so that the password that is passed into this method is not the plain text password as entered by a user.
|||Yep that definetly won't work,
I agree with DMW, people who write code like that should be shot! :-)
At it's most basic that function may work , apart from the obvious mistake of querying the Password as the Username,
I also convert my string comparisons to .toupper or .tolower , just get rid of casing issues.
on Passwords I always enter them into the DB encrypted, and basically compare the encrypted text.
I would never do this in Code, as I would rather do this kind of operation in the Stored Proc, and let the SP deliver the result.
Inline SQL is so Passe, in a field where we must be moving with the times , this is a no no.
|||That is a little harsh of a response don't you think. Did youtake the time to consider that i may be a new developer and not reallyknow what i am doing.
Anyway, that is why I posted I wanted to dknow if i was doing it rightor not. And when i pass the password to the function it willalready be hashed.
Thanks for the help.
|||

appologies if I came across a little, harsh I did not certainly mean in it in that way,

I was trying to be funny.

I'll stick to code, because I suck at language :-)

|||I agree.
I don't recall advocating that ANY developer ever be shot.
And the reason that I spend my time on this forum is that I'm passionate about passing on what little experience I have to other developers so that they can learn from my mistakes.
So keep posting, and you'll get lot's of support and advice.
Speaking of which, I'll offer one other piece of advice: never, ever use someone else's code (especially not sample code or book code) unless you're really happy with how it works. Most samples, including book samples, don't follow best practice. If they did, the code would be harder to follow and generally much too long to fit in a book. A lot of "book code" (by which I mean conference samples, MSDN samples, my samples) is written to try and explain a learning point, not for commercial use. I know that this sounds daft, but that's the way it is.
The most common thing that is omitted is error handling code (which is critical), and the normal security reviews that would accompany real application development.|||Apology accpeted no hard feelings at all.
yeah one reason I posted was to make sure that I was doing it right. Thank for taking the time to answer.
I have the book code complete so that i can learn better ways of codingI guess i need to pull that sucker out and try to read it again.
Thanks for all the help.
sql

Help with query needed!

Hi,

I have a table of this form:

| ID | Code | Calc

1 A 0
2 A 0
3 X 0
4 U 0
5 P 0
6 A 0
7 P 0
8 P 0
9 F 0
10 J 0
11 A 0
12 P 0
13 A 0

How can I write an update statement so that the Calc column is updated in such a way that
- if the Code column's value is equal to 'P', the Calc's value will be the value of the previous record's Calc value plus 1
- if the Code column's value is not equal to 'P', the Calc's value will be the same as the value of the previous record's Calc value

| ID | Code | Calc

1 A 0
2 A 0
3 X 0
4 U 0
5 P 1
6 A 1
7 P 2
8 P 3
9 F 3
10 J 3
11 A 3
12 P 4
13 A 4

Thanks

here you go..

Code Snippet

Create Table #calctable (

[ID] int ,

[Code] Varchar(100) ,

[Calc] int

);

Insert Into #calctable Values('1','A','0');

Insert Into #calctable Values('2','A','0');

Insert Into #calctable Values('3','X','0');

Insert Into #calctable Values('4','U','0');

Insert Into #calctable Values('5','P','0');

Insert Into #calctable Values('6','A','0');

Insert Into #calctable Values('7','P','0');

Insert Into #calctable Values('8','P','0');

Insert Into #calctable Values('9','F','0');

Insert Into #calctable Values('10','J','0');

Insert Into #calctable Values('11','A','0');

Insert Into #calctable Values('12','P','0');

Insert Into #calctable Values('13','A','0');

Update #calctable

Set

Calc = Data.NewCalc

From

#calctable Tbl

Join

(

SelectId

,(Select Count(*) From #calctable Sub Where Sub.Code='P' And Sub.Id<=Main.Id) as NewCalc

From

#calctable Main

)as Data On Tbl.Id = Data.Id

Select * From #calctable

|||Thanks!

Considering there will be millions of records within this table, how efficient is this method? Could there be a better way to do this?
|||Sorry, I was not intending to edit your response; I guess I clicked the wrong button. Does your table have a clustered index that is based on the ID column?|||

Code Snippet

select t1.*,min([t3].[PRank])

from <MyTable> as [t1]

inner join

(select [t2].[ID] ,row_number() over (order by [t2].[ID]) - 1 as [PRank] from <MyTable> as [t2] where t2.Code = 'P') as [t3]

on t1.ID < t2.Id

group by t1.ID,t1.code,t1.calc

order by t1.id

|||The table is a temporary one, so I can create any index needed.
|||

rusag2 wrote:

Code Snippet

select t1.*,min([t3].[PRank])

from <MyTable> as [t1]

inner join

(select [t2].[ID] ,row_number() over (order by [t2].[ID]) - 1 as [PRank] from <MyTable> as [t2] where t2.Code = 'P') as [t3]

on t1.ID < t2.Id

group by t1.ID,t1.code,t1.calc

order by t1.id

the join clause should be

Code Snippet

t1.ID < t3.Id

Help with Query #2

Hi. All

I am having trouble quering #2. No problem with #1(there is product & sub product code) & data was taken from 1 tble for #1.. Data is also avail on another table as well.

Cust has many product, but 1 cust_id.

Need your help guys..

JK

1) Select customers with Free Checking, Gold Checking,ect... with combined balance $11,000 - $25,000.

2) Select all customers who have an IXI(Total Relationship Balance) value at least twice the amount of their combined balance of $11K - $25. For example, if a customer has $11,000 in combined balance, their IXI must be $22,000+.

3) How many customers fall into this bucket?

How about offering us the table DDL, your attempted queries so far, and perhaps some sample data in the form of INSERT statements.

|||

Hi Arnie,

This is what I did for step one:

step #1
select cust_id, acct_num, sys_prod_cd, sys_sub_prod_cd, cur_book_bal into lp.dbo.RTP_JK_IXIDepBal_19939
from RTP_Cust_Data_Final
where sys_prod_cd+sys_sub_prod_cd in ('CDA43','CDA49','DDAU7',
'RSV20','DDA86') and cur_book_bal >= 11000and cur_book_bal <= 25000

Step1.1

select count(distinct cust_id), sum(cur_book_bal)from RTP_temp_JK_IXIDepBal_19939
group by cust_id
order by Cust_id

Step 2 is where I am having problem. What I am going to do is to run them individually to get a waterfall count of potential cust count.

Thanks

JK(john)

|||

With my interpretation of #1, I don't think your query will give the correct result, but then I don't know the definitions of your fields.

So, here's a rewrite of #1 and guess at #2 since i didn't know where the IXI-relationship-balance comes from.

Code Snippet

--#1

select cust_id,

sum(cur_book_bal)as combined_balance

from RTP_Cust_Data_Final

where sys_prod_cd+sys_sub_prod_cd in('CDA43','CDA49','DDAU7','RSV20','DDA86')

groupby cust_id

havingsum(cur_book_bal)between 11000 and 25000

--#2

select cust_id,

sum(cur_book_bal)as combined_balance,

sum(ixi_balance)as ixi_balance

from RTP_Cust_Data_Final

groupby cust_id

havingsum(cur_book_bal)between 11000 and 25000

andsum(ixi_balance)=>(sum(cur_book_bal)*2)

|||

Hi Dale.J

IXI-relationship Balance mean the total sum bal of All (each) account cur_bal the cust has with us.

(sum(cur_book_bal) as IXI_balance),

then if that balance fall B/ween 11000 & 25000

multiply those bal by 2 & fetch those cust_id that fall within that bal, as the result set.

Im trying out ur query as well & twek it as I go. But this is what I am trying to get.

Thank U.

JK

|||

You're welcome.

If you need more help with the tweaking, just post a little more DDL, etc. and some sample (insert) data.

BTW, does the RTP prefix stand for Research Triangle Park?

|||

RTP=Name of project team.

JK

|||

Ah, yes.

I thought maybe you were here in the Triangle.

Help with query

I want to take this code and insert data on a monthly basis. For example all the data for month of August.

INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1
GO

ThanksChange your where clause

WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1

To this

WHERE MONTH(LogTime)=8|||If you can ride an index, I'd use:INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE '2004-08-01' <= LogTime
AND LogTime < '2004-09-01'
GOThis lets you ride an index if one exists on LogTime, which can improve your performance by literally orders of magnitude (things can take much less than one tenth as long as not riding the index).

-PatP

help with query

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:524963
Hi,
I need to modify the data in a column in CODE.stock_code. Some of the
records are in the form of 250-10-12-9. Not all records are the same lengh,
but I need to remove the -9 in those records.
This newbie would appreciate your help in the sql that would do this.
regards,
g
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04Will the "-9" always appear as the last two characters in the value? If that
is
the case, then you could do the following:
Update Code
Set Stock_Code = Left(Stock_Code, Len(Stock_Code) - 2)
Where Left(Stock_Code, 2) = '-9'
Thomas
"Neither rhyme nor reason" <sportman_6663@.yahoo.ca> wrote in message
news:L38ee.11476$3U.717136@.news20.bellglobal.com...
> Hi,
> I need to modify the data in a column in CODE.stock_code. Some of the
> records are in the form of 250-10-12-9. Not all records are the same lengh
,
> but I need to remove the -9 in those records.
> This newbie would appreciate your help in the sql that would do this.
>
> regards,
> g
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>|||You said "need to remove the -9 in those records" -- which records (rows)
are "those" ? We need to know more details.. Do you need to remove the '-9'
from any row that Has a '-9' at the end? Id so, then just
Update Table Set
ColName = Left(ColName, Len(ColName) - 2)
Where ColName like '%-9'
-- or is there another rule?
"Neither rhyme nor reason" wrote:

> Hi,
> I need to modify the data in a column in CODE.stock_code. Some of the
> records are in the form of 250-10-12-9. Not all records are the same lengh
,
> but I need to remove the -9 in those records.
> This newbie would appreciate your help in the sql that would do this.
>
> regards,
> g
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>
>|||You should never have a table named "Code" -- what kind of code is it'
-9 in those records [sic]. <<
You need to learn to use the right words so you will have the right
mindset. Rows are not records and you update columns. Try this.
UPDATE Inventory
SET stock_code
= REVERSE (SUBSTRING (REVERSE(stock_code), 3))
WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
There are also proprietary LEFT() and RIGHT() substring functions, but
these are reserved words in Standard SQL. However, they will probably
be faster.|||Joe, shouldn't that be
Update Inventory
Set Stock_Code = Reverse(Substring(Reverse(Stock_Code), 3))
Where Substring(Reverse(Stock_Code), 1, 2) = '9-'
With the string reversed, the 9 will appear before the dash.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
> You should never have a table named "Code" -- what kind of code is it'
>
> -9 in those records [sic]. <<
> You need to learn to use the right words so you will have the right
> mindset. Rows are not records and you update columns. Try this.
> UPDATE Inventory
> SET stock_code
> = REVERSE (SUBSTRING (REVERSE(stock_code), 3))
> WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
> There are also proprietary LEFT() and RIGHT() substring functions, but
> these are reserved words in Standard SQL. However, they will probably
> be faster.
>|||Thank you all for your help... So my wording should have been
I need to remove the last 2 digits from rows that contain -9 in the
STOCK_CODE column ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
> You should never have a table named "Code" -- what kind of code is it'
>
> -9 in those records [sic]. <<
> You need to learn to use the right words so you will have the right
> mindset. Rows are not records and you update columns. Try this.
> UPDATE Inventory
> SET stock_code
> = REVERSE (SUBSTRING (REVERSE(stock_code), 3))
> WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
> There are also proprietary LEFT() and RIGHT() substring functions, but
> these are reserved words in Standard SQL. However, they will probably
> be faster.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04|||Then my modified version of Joe's code should do it:
Update Inventory
Set Stock_Code = Reverse(Substring(Reverse(Stock_Code), 3))
Where Substring(Reverse(Stock_Code), 1, 2) = '9-'
Thomas
"Neither rhyme nor reason" <sportman_6663@.yahoo.ca> wrote in message
news:ogpee.13540$3U.936989@.news20.bellglobal.com...
> Thank you all for your help... So my wording should have been
> I need to remove the last 2 digits from rows that contain -9 in the
> STOCK_CODE column ?
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>