how to get this output ?

Hi Everybody,
I have a Table Call Referrals like this
EmpId Cl_Date RefrlNo OptNo
1 NULL 2565 2222
1 01/01/2007 2565 2223
2 01/02/2007 2567 2230
3 01/02/2007 2568 2231
3 01/03/2007 2568 2232
4 NULL 2569 2245
4 NULL 2570 2246

From this table i need to get the Emp Id, Cl_date RefrlNO,OptNo
where EmpId must have repeated more than one time as well as it must have at least one Cl_Date + at least one NULL Cl_Date

after all these requirement satisfy , i need to pick again a row data where Cl_Date Is null ?

So the out put should be like this
EmpId Cl_Date RefrlNo OptNo
1 NULL 2565 2222

how do i do this task ?

any idea ?
regards
suis

Code Snippet

createtable Referrals

(

EmpId int,

Cl_Date datetime,

RefrlNo int,

OptNo int

)

GO

insertinto Referrals values(1,NULL, 2565, 2222)

insertinto Referrals values(1,'01/01/2007', 2565, 2223)

insertinto Referrals values(2,'01/02/2007', 2567, 2230)

insertinto Referrals values(3,'01/02/2007', 2568, 2231)

insertinto Referrals values(3,'01/03/2007', 2568, 2232)

insertinto Referrals values(4,NULL,2569,2245)

insertinto Referrals values(4,NULL,2570,2246)

SELECT EmpId, Cl_date, RefrlNo, OptNo FROM Referrals

WHERE

EmpID IN(select EmpId from Referrals where Cl_date isNULL)

AND

EmpID IN(select EmpId from Referrals where Cl_date isNOTNULL)

AND

Cl_date ISNULL

If for EmpId we have line where Cl_date is NULL (see first IN clause) and line where Cl_Date is not NULL (see second IN clause), its that we have at least two lines with same EmpID

How to get this output

hi their
i create a temp table called tempEmpTran and it content a fileds like
Refrlno Description opt-refno

1474 New 5
1474 followup 3
1474 followup 3
1470 new 7

No my problem is i need to eleminate the refrlno which Description <> 'followup',but again the problem is if i see any referrals which repeating more than once then i need to check if any of that Rrefrlno got any Description which equal to the followup, if yes i need to eliminate the whole Referealno from the list,

Refrlno Description opt-refno
1474 New 5 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1470 new 7 -should include,cos its got description 'new'

so once all these condition satisfied then the output should looks like this

Refrlno Description opt-refno
1470 new 7

Any Idea ?
regards
Nirangatry this:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Formatting seems to be screwed on that post, heres the query again:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Hi sam,
Many thanks to u r fast respone,but in that query there is not line which eleminate the Description='Followup' ?
any idea ?
regards
Niranga|||Have you ran it?

It only returns records who have a single refrlno... so there shouldn't be any need to filter based on your sample data.

Have a play and try to understand what it does. Then you can expand on it |||

Hey, you could do this 2 ways:

Select *

from tempEmpTran

Where Refrlno not in (Select Refrlno from tempEmpTran where Description = 'Followup')

Select *

from tempEmpTran a

Where not exists (Select Refrlno from tempEmpTran b where Description = 'Followup' and a.Refrlno = b.Refrlno)

BobP

|||Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga|||

Niranga wrote:

Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga

Sorry about that, didn't bother creating the tables and testing. You just need to add an alias to the column name
count(*) becomes
count(*) as [Cnt]|||hi,

here's another alternative.
SELECT *
INTO #tempEmpTran
FROM (
SELECT 1474 AS Refrlno
, 'New' AS Description
, 5 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1470 AS Refrlno
, 'New' AS Description
, 7 AS [opt-refno]
) a

SELECT a.*
FROM #tempEmpTran a INNER JOIN
(
SELECT Refrlno
, IsNew = SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END))
FROM #tempEmpTran
GROUP BY
Refrlno
HAVING SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END)) = 0
) b ON a.RefrlNo = b.RefrlNo

DROP TABLE #tempEmpTran|||

here you go..

Code Snippet

Create Table #data (

[Refrlno] int ,

[Description] Varchar(100) ,

[opt-refno] int

);

Insert Into #data Values('1474','New','5');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1470','new','7');

Select

*

from

#Data Main

Where

Not Exists

(

Select

Sub.[Refrlno]

From

#Data Sub

Where

Main.[Refrlno]=Sub.[Refrlno]

And Sub.[Description] = 'followup'

)

How to get this output

Hi evrybody
i have a question like this
i need to display the row data like this
If i use this sql
select datediff(yyyy,'01/01/2001','01/01/2004')
i get the output = 4

problem is sometime first parameter will be null
like this
select datediff(yyyy,NULL,'01/01/2004'),'N/A')
in that case my result looks like NULL

But what i need to see is if the result is null then i need to display like this N/A

How to do this task ?
regards
suis

Hey,

You can use the isnull method, so that if a value is null, it will return another value, like 'N/A' in this case. Though I don't know how that could work in a datediff method. Rather, you may want to wrap the isnull around the entire datediff call, so that if it returns null, you can return 'N/A'.

|||Hi i already used isnull
like this way
select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')
but it giving me error !

"Conversion failed when converting the varchar value 'N/A' to data type int."
regards
suis|||

Try changing this:

select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')

to

select isnull(convert(varchar(4), datediff(yyyy,NULL,'01/01/2004')),'N/A')

|||Hi Kent
Thanks for ur comments,
its worked out
regards
suis

sql

How to get the output parameter from an internally called stored procedure??

Hi all,
i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:
I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:
my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.
any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??
-shane

Try these links the last link is a tool that will generate the code for you but read the articles first so you know what you are doing. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/default.aspx

http://www.codeguru.com/vb/gen/vb_database/sqlserver/article.php/c8671__3/

http://www.adonetexpress.com/default.aspx

|||thanks for the links, there are great information there but my problemis not with calling stored procedures within a page or creating astored procedure. my problem is getting the output parameter of astored procedure that is called within a trigger. From my understandingthis is not possible but i wanted to ask and make sure anyway.
thank you,
-shane

|||Can you post a sample code to demonstrate the issue? No need to post it all, if it is huge but something that would ease up reproing it.|||Hi,
i dont have the full code here but its something similat to below (ignore syntax)
my trigger:
instead of insert
as
declare @.ID int
select @.ID = select ID from inserted
begin
update mytable(mycolumn) --if the inserted parameter(@.ID) exists mycolumn will be updated
select mycolumn=mycolumn + 1
where mytable.myid in (select ID from inserted)
insert into mytable(mycolumn) -- if the inserted parameter doesnt exists it will be inserted with a mycolumn=0
values(0)
where mytable.myid not in (select ID from inserted)
end
myprocedure(@.ID)
go
the above trigger inserts a new row with mycolumn=0 to mytable if theinput parameter doesnt exists in mytable, if it exists instead of aninsert an update fires and the value of mycolumn increments one. Afterinsert/update finished i m calling myprocedure which will set the newvalue of mycolumn as an output parameter.
myprocedure
(@.ID int,
@.mycolumn int output)
select @.mycolumn=mycolumn from mytable
where ID = @.ID
please ignore the syntax mistakes, the actual code is working with no problem.
What i m trying to do is run an insert procedure or sql query with aparameter(@.ID) and get the output parameter which is @.mycolumn. Butthis mycolumn will be set to its final value after the trigger runs.
Is it possible?
thanks,
-shane
|||I am assuming this is what you need, try this link for the code to an undocumented stored proc calledsp_executesql. Hope this helps.
http://support.microsoft.com/default.aspx?scid=kb;en-us;262499|||It looks like something i might need, i will test it asap.
Any idea how to get the output parameter created/kept by sp_execuresql into my asp.net page?
my understanding is sp_executesql will call my stored procedure andkeep its output parameter as an output parameter but i still dont knowhow to use this parameter in my page.
thank you,
-shane
|||Run a search for "Using sp_executesql" in the BOL (books online) it shows some examples of what you can do but there is no ADO.NET code. Hope this helps.|||thank you,
thats what i ll do. thanks again for the link
-shane
sql

How to Get the Output Column in OLE DB Command Transformation

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = @.OrderID

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = ?

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

|||

It sounds as tho you are using the wrong component. To source stuff use the OLE DB Source Adapter, not the OLE DB Command.

-Jamie

|||

Dear Jamie,

Thanks for such a quick reply.

U Mean OLEDB Source From DataFlow Sources.

Actually the My dataflow task contains one OLEDB source component which is having connection to one table, from that table i am getting the orderID column, Then i am passing this OrderID column values to the query Which will get the serialnumber in the SerialNumbers table based on this OrderID. And my problem is i cant able to get this selected serialnumber column in the output column tree view,so i that column is not accessable for futher transformations.

Please give me some solution.

Thanks in advance.

- Dhivya

|||

You need the LOOKUP transform. That s exactly what it does.

-Jamie

|||

Dear Jamie,

That also i tried,the table contains multiple values(for same OrderID multiple serial numbers) and the lookup transform will take only the first value and map the same to the others.

-Dhivya

|||

So its a many-to-many?

Then you should use the MERGE JOIN component!

-Jamie

|||

Good advice, Jamie.

Dhivya, remember that the Merge Join needs a sorted input, so you'll also need to use sort components. Alternatively, use ORDER BY in the source queries, and set the IsSorted property of the source adapter output to True.

Donald

|||

I tried merge join, the problem is order ID is not unique in my source table and in transaction table. so if i put some inner or left joins i am not getting the values what i want.

-Dhivya

|||

Merge Join worked for me. I did right outer join.

Thank u Jamie and donald.

But still my question is, we cant get the output columns in OLE DB Command Component if we use select command?

|||

No. That's not what its for!

-Jamie

|||

OK. Thanks a lot

-Dhivya

How to get the name of the error column from the errorcolumn output

Hi,

Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?

Regard,

pritesh

Possible, but not trivial. MS seem to consider this a design-time lookup, so you could use something that queries the package structure to get the info. Simon has written a transform that does this as well, note it needs both outputs to be able to get all the metadata. http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||Thanks for the info , i will try to implement it. It sounds useful !|||

Anonymous wrote:

Thanks for the info , i will try to implement it. It sounds useful !

And if you want to continue posting here, please change your display name from Anonymous to something else. Microsoft staff will be cleaning up those accounts with display names of Anonymous.

How to get the count of a value( for ex "PASS") in each row and showits count as a col

I have my sP output as given below:

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2

1 abc 1/1/2007 Pass PassYes No

2 abc 1/1/2007 Pass Fail Yes No

3 abc 1/1/2007 Pass PassYes No

4 abc 1/1/2007 Pass Fail Yes No

5 abc 1/1/2007 Pass Fail Yes No

What i need is this way

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2 Passcount

1 abc 1/1/2007 Pass Pass Yes No 2

2 abc 1/1/2007 Pass Fail Yes No 1

3 abc 1/1/2007 Pass Pass Yes No 2

4 abc 1/1/2007 Pass Fail Yes No 1

5 abc 1/1/2007 Pass Fail Pass No 2

Similarly i need FailCount, Yes Count, Nocount as few more columns.

The query for the first table output is this way..

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[VerificationSummaryReport_TEST] '1/1/2007','3/28/2007','ONBOARD'

-- Add the parameters for the stored procedure here

@.FromDate datetime,@.ToDate datetime,@.VerificationType varchar(15)

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

select AD.audit_id, AD.Audit_Name, SM.Shortcode 'shortcode_name', CM.Campaign_Name , CM.Shortcode_Owner 'brand_name', SM.Operator_Name,

E.first_name+' '+E.last_name 'employee_name',AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate,

--case when DE.Status_criteria = 'PASS' then count(*) else '0' end as 'PassCount',

max(case when DE.criteria_id = 1 then DE.Status_criteria else '-' end) as '6.2',

max(case when DE.criteria_id = 2 then DE.Status_criteria else '-' end) as '6.2.1',

max(case when DE.criteria_id = 3 then DE.Status_criteria else '-' end) as '6.2.2',

max(case when DE.criteria_id = 4 then DE.Status_criteria else '-' end) as '6.3',

max(case when DE.criteria_id = 5 then DE.Status_criteria else '-' end) as '6.3.1',

max(case when DE.criteria_id = 6 then DE.Status_criteria else '-' end) as '6.4',

max(case when DE.criteria_id = 7 then DE.Status_criteria else '-' end) as '6.4.1',

max(case when DE.criteria_id = 8 then DE.Status_criteria else '-' end) as '6.4.3',

max(case when DE.criteria_id = 9 then DE.Status_criteria else '-' end) as '6.4.2',

max(case when DE.criteria_id = 10 then DE.Status_criteria else '-' end) as '3.1',

max(case when DE.criteria_id = 11 then DE.Status_criteria else '-' end) as '3.1.1',

max(case when DE.criteria_id = 12 then DE.Status_criteria else '-' end) as '3.1.2',

max(case when DE.criteria_id = 14 then DE.Status_criteria else '-' end) as '3.2',

max(case when DE.criteria_id = 15 then DE.Status_criteria else '-' end) as '3.2.1',

max(case when DE.criteria_id = 16 then DE.Status_criteria else '-' end) as '3.2.2',

max(case when DE.criteria_id = 19 then DE.Status_criteria else '-' end) as '4.6',

max(case when DE.criteria_id = 20 then DE.Status_criteria else '-' end) as '4.6.2',

max(case when DE.criteria_id = 21 then DE.Status_criteria else '-' end) as '4.6.3',

max(case when DE.criteria_id = 22 then DE.Status_criteria else '-' end) as '4.6.4',

max(case when DE.criteria_id = 23 then DE.Status_criteria else '-' end) as '4.7',

max(case when DE.criteria_id = 24 then DE.Status_criteria else '-' end) as '4.7.2',

max(case when DE.criteria_id = 25 then DE.Status_criteria else '-' end) as '4.8',

max(case when DE.criteria_id = 26 then DE.Status_criteria else '-' end) as '4.8.1',

max(case when DE.criteria_id = 27 then DE.Status_criteria else '-' end) as '4.9',

max(case when DE.criteria_id = 28 then DE.Status_criteria else '-' end) as '4.9.2',

max(case when DE.criteria_id = 29 then DE.Status_criteria else '-' end) as '7.1',

max(case when DE.criteria_id = 30 then DE.Status_criteria else '-' end) as '7.1.1',

max(case when DE.criteria_id = 31 then DE.Status_criteria else '-' end) as '7.2',

max(case when DE.criteria_id = 32 then DE.Status_criteria else '-' end) as '7.2.1',

max(case when DE.criteria_id = 36 then DE.Status_criteria else '-' end) as '5.10',

max(case when DE.criteria_id = 37 then DE.Status_criteria else '-' end) as '5.10.1',

max(case when DE.criteria_id = 38 then DE.Status_criteria else '-' end) as '5.5',

max(case when DE.criteria_id = 39 then DE.Status_criteria else '-' end) as '5.5.1',

max(case when DE.criteria_id = 41 then DE.Status_criteria else '-' end) as '5.5.2',

max(case when DE.criteria_id = 42 then DE.Status_criteria else '-' end) as '5.6',

max(case when DE.criteria_id = 43 then DE.Status_criteria else '-' end) as '5.6.1',

max(case when DE.criteria_id = 44 then DE.Status_criteria else '-' end) as '5.6.2',

max(case when DE.criteria_id = 45 then DE.Status_criteria else '-' end) as '5.7',

max(case when DE.criteria_id = 46 then DE.Status_criteria else '-' end) as '5.7.1',

max(case when DE.criteria_id = 47 then DE.Status_criteria else '-' end) as '5.9',

max(case when DE.criteria_id = 48 then DE.Status_criteria else '-' end) as '5.9.1',

max(case when DE.criteria_id = 49 then DE.Status_criteria else '-' end) as '5.9.2',

max(case when DE.criteria_id = 51 then DE.Status_criteria else '-' end) as '1.3',

max(case when DE.criteria_id = 60 then DE.Status_criteria else '-' end) as '8.2',

max(case when DE.criteria_id = 66 then DE.Status_criteria else '-' end) as '9.3',

max(case when DE.criteria_id = 67 then DE.Status_criteria else '-' end) as '9.3.1',

max(case when DE.criteria_id = 68 then DE.Status_criteria else '-' end) as '9.3.2',

max(case when DE.criteria_id = 69 then DE.Status_criteria else '-' end) as '10.1',

max(case when DE.criteria_id = 70 then DE.Status_criteria else '-' end) as '10.1.1',

max(case when DE.criteria_id = 71 then DE.Status_criteria else '-' end) as '10.1.2',

max(case when DE.criteria_id = 72 then DE.Status_criteria else '-' end) as '10.5',

max(case when DE.criteria_id = 73 then DE.Status_criteria else '-' end) as '10.5.1',

max(case when DE.criteria_id = 74 then DE.Status_criteria else '-' end) as '10.5.2',

max(case when DE.criteria_id = 75 then DE.Status_criteria else '-' end) as '10.6',

max(case when DE.criteria_id = 76 then DE.Status_criteria else '-' end) as '10.6.1',

max(case when DE.criteria_id = 77 then DE.Status_criteria else '-' end) as '10.7',

max(case when DE.criteria_id = 78 then DE.Status_criteria else '-' end) as '10.7.1',

max(case when DE.criteria_id = 79 then DE.Status_criteria else '-' end) as '6.4.4'

--, case when DE.Status_criteria = 'PASS' then Count(*) else 0 end as 'PASSCOUNT'

from dbo.Audit_Details AD INNER JOIN

dbo.Data_Evaluation DE ON DE.Audit_Id = AD.Audit_ID INNER JOIN

dbo.ShortCode_Master SM ON SM.ShortCode_Id = AD.Shortcode_Id INNER JOIN

dbo.Campaign_Master CM ON CM.Campaign_Id = AD.Campaign_Id INNER JOIN

dbo.Employee E ON E.Emp_Id = AD.DE_empid

where AD.present_auditstate = 'AS' and AD.verificationtype = @.VerificationType AND AD.STATUS = 'ACTIVE'

AND AD.audit_createdDate between @.FromDate and --'3/29/2007'

REPLACE(CONVERT(CHAR(10),DATEADD(day, 1,@.ToDate),110),'-','/')

group by AD.audit_id, AD.shortcode_id, AD.campaign_id, AD.Audit_Name, SM.Shortcode, CM.Campaign_Name , CM.Shortcode_Owner, SM.Operator_Name,

E.first_name,E.last_name ,AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate--, DE.Status_criteria

--order by AD.audit_id

END

Please Help me out in solving this problem. Thanks in advance

Moving to T-SQL forum.

Mike

|||sum(case when DE.Status_criteria = 'PASS' then 1 else 0 end) as 'PASSCOUNT'

How to get the count of a value( for ex "PASS") in each row and showits count as a col

I have my sP output as given below:

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2

1 abc 1/1/2007 Pass PassYes No

2 abc 1/1/2007 Pass Fail Yes No

3 abc 1/1/2007 Pass PassYes No

4 abc 1/1/2007 Pass Fail Yes No

5 abc 1/1/2007 Pass Fail Yes No

What i need is this way

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2 Passcount

1 abc 1/1/2007 Pass Pass Yes No 2

2 abc 1/1/2007 Pass Fail Yes No 1

3 abc 1/1/2007 Pass Pass Yes No 2

4 abc 1/1/2007 Pass Fail Yes No 1

5 abc 1/1/2007 Pass Fail Pass No 2

Similarly i need FailCount, Yes Count, Nocount as few more columns.

The query for the first table output is this way..

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[VerificationSummaryReport_TEST] '1/1/2007','3/28/2007','ONBOARD'

-- Add the parameters for the stored procedure here

@.FromDate datetime,@.ToDate datetime,@.VerificationType varchar(15)

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

select AD.audit_id, AD.Audit_Name, SM.Shortcode 'shortcode_name', CM.Campaign_Name , CM.Shortcode_Owner 'brand_name', SM.Operator_Name,

E.first_name+' '+E.last_name 'employee_name',AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate,

--case when DE.Status_criteria = 'PASS' then count(*) else '0' end as 'PassCount',

max(case when DE.criteria_id = 1 then DE.Status_criteria else '-' end) as '6.2',

max(case when DE.criteria_id = 2 then DE.Status_criteria else '-' end) as '6.2.1',

max(case when DE.criteria_id = 3 then DE.Status_criteria else '-' end) as '6.2.2',

max(case when DE.criteria_id = 4 then DE.Status_criteria else '-' end) as '6.3',

max(case when DE.criteria_id = 5 then DE.Status_criteria else '-' end) as '6.3.1',

max(case when DE.criteria_id = 6 then DE.Status_criteria else '-' end) as '6.4',

max(case when DE.criteria_id = 7 then DE.Status_criteria else '-' end) as '6.4.1',

max(case when DE.criteria_id = 8 then DE.Status_criteria else '-' end) as '6.4.3',

max(case when DE.criteria_id = 9 then DE.Status_criteria else '-' end) as '6.4.2',

max(case when DE.criteria_id = 10 then DE.Status_criteria else '-' end) as '3.1',

max(case when DE.criteria_id = 11 then DE.Status_criteria else '-' end) as '3.1.1',

max(case when DE.criteria_id = 12 then DE.Status_criteria else '-' end) as '3.1.2',

max(case when DE.criteria_id = 14 then DE.Status_criteria else '-' end) as '3.2',

max(case when DE.criteria_id = 15 then DE.Status_criteria else '-' end) as '3.2.1',

max(case when DE.criteria_id = 16 then DE.Status_criteria else '-' end) as '3.2.2',

max(case when DE.criteria_id = 19 then DE.Status_criteria else '-' end) as '4.6',

max(case when DE.criteria_id = 20 then DE.Status_criteria else '-' end) as '4.6.2',

max(case when DE.criteria_id = 21 then DE.Status_criteria else '-' end) as '4.6.3',

max(case when DE.criteria_id = 22 then DE.Status_criteria else '-' end) as '4.6.4',

max(case when DE.criteria_id = 23 then DE.Status_criteria else '-' end) as '4.7',

max(case when DE.criteria_id = 24 then DE.Status_criteria else '-' end) as '4.7.2',

max(case when DE.criteria_id = 25 then DE.Status_criteria else '-' end) as '4.8',

max(case when DE.criteria_id = 26 then DE.Status_criteria else '-' end) as '4.8.1',

max(case when DE.criteria_id = 27 then DE.Status_criteria else '-' end) as '4.9',

max(case when DE.criteria_id = 28 then DE.Status_criteria else '-' end) as '4.9.2',

max(case when DE.criteria_id = 29 then DE.Status_criteria else '-' end) as '7.1',

max(case when DE.criteria_id = 30 then DE.Status_criteria else '-' end) as '7.1.1',

max(case when DE.criteria_id = 31 then DE.Status_criteria else '-' end) as '7.2',

max(case when DE.criteria_id = 32 then DE.Status_criteria else '-' end) as '7.2.1',

max(case when DE.criteria_id = 36 then DE.Status_criteria else '-' end) as '5.10',

max(case when DE.criteria_id = 37 then DE.Status_criteria else '-' end) as '5.10.1',

max(case when DE.criteria_id = 38 then DE.Status_criteria else '-' end) as '5.5',

max(case when DE.criteria_id = 39 then DE.Status_criteria else '-' end) as '5.5.1',

max(case when DE.criteria_id = 41 then DE.Status_criteria else '-' end) as '5.5.2',

max(case when DE.criteria_id = 42 then DE.Status_criteria else '-' end) as '5.6',

max(case when DE.criteria_id = 43 then DE.Status_criteria else '-' end) as '5.6.1',

max(case when DE.criteria_id = 44 then DE.Status_criteria else '-' end) as '5.6.2',

max(case when DE.criteria_id = 45 then DE.Status_criteria else '-' end) as '5.7',

max(case when DE.criteria_id = 46 then DE.Status_criteria else '-' end) as '5.7.1',

max(case when DE.criteria_id = 47 then DE.Status_criteria else '-' end) as '5.9',

max(case when DE.criteria_id = 48 then DE.Status_criteria else '-' end) as '5.9.1',

max(case when DE.criteria_id = 49 then DE.Status_criteria else '-' end) as '5.9.2',

max(case when DE.criteria_id = 51 then DE.Status_criteria else '-' end) as '1.3',

max(case when DE.criteria_id = 60 then DE.Status_criteria else '-' end) as '8.2',

max(case when DE.criteria_id = 66 then DE.Status_criteria else '-' end) as '9.3',

max(case when DE.criteria_id = 67 then DE.Status_criteria else '-' end) as '9.3.1',

max(case when DE.criteria_id = 68 then DE.Status_criteria else '-' end) as '9.3.2',

max(case when DE.criteria_id = 69 then DE.Status_criteria else '-' end) as '10.1',

max(case when DE.criteria_id = 70 then DE.Status_criteria else '-' end) as '10.1.1',

max(case when DE.criteria_id = 71 then DE.Status_criteria else '-' end) as '10.1.2',

max(case when DE.criteria_id = 72 then DE.Status_criteria else '-' end) as '10.5',

max(case when DE.criteria_id = 73 then DE.Status_criteria else '-' end) as '10.5.1',

max(case when DE.criteria_id = 74 then DE.Status_criteria else '-' end) as '10.5.2',

max(case when DE.criteria_id = 75 then DE.Status_criteria else '-' end) as '10.6',

max(case when DE.criteria_id = 76 then DE.Status_criteria else '-' end) as '10.6.1',

max(case when DE.criteria_id = 77 then DE.Status_criteria else '-' end) as '10.7',

max(case when DE.criteria_id = 78 then DE.Status_criteria else '-' end) as '10.7.1',

max(case when DE.criteria_id = 79 then DE.Status_criteria else '-' end) as '6.4.4'

--, case when DE.Status_criteria = 'PASS' then Count(*) else 0 end as 'PASSCOUNT'

from dbo.Audit_Details AD INNER JOIN

dbo.Data_Evaluation DE ON DE.Audit_Id = AD.Audit_ID INNER JOIN

dbo.ShortCode_Master SM ON SM.ShortCode_Id = AD.Shortcode_Id INNER JOIN

dbo.Campaign_Master CM ON CM.Campaign_Id = AD.Campaign_Id INNER JOIN

dbo.Employee E ON E.Emp_Id = AD.DE_empid

where AD.present_auditstate = 'AS' and AD.verificationtype = @.VerificationType AND AD.STATUS = 'ACTIVE'

AND AD.audit_createdDate between @.FromDate and --'3/29/2007'

REPLACE(CONVERT(CHAR(10),DATEADD(day, 1,@.ToDate),110),'-','/')

group by AD.audit_id, AD.shortcode_id, AD.campaign_id, AD.Audit_Name, SM.Shortcode, CM.Campaign_Name , CM.Shortcode_Owner, SM.Operator_Name,

E.first_name,E.last_name ,AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate--, DE.Status_criteria

--order by AD.audit_id

END

Please Help me out in solving this problem. Thanks in advance

Moving to T-SQL forum.

Mike

|||sum(case when DE.Status_criteria = 'PASS' then 1 else 0 end) as 'PASSCOUNT'

How to get String representation of a Hex number?

Hi,
I want to get the string representation of a hex number from a
varBinary column of a table.
For example I want to get the output : 'The Hex value is 0xFF'
but
select 'The Hex value is ' + convert(varchar(10), 0xFF)
retruns the ascii charecter for 0xFF

Any idea how do I get the hex form as it is?
thanks.
Supratim[posted and mailed, please reply in news]

Supratim (supratim@.sagemetrics.com) writes:
> I want to get the string representation of a hex number from a
> varBinary column of a table.
> For example I want to get the output : 'The Hex value is 0xFF'
> but
> select 'The Hex value is ' + convert(varchar(10), 0xFF)
> retruns the ascii charecter for 0xFF
> Any idea how do I get the hex form as it is?

You can use

select master.dbo.fn_varbintohexstr(@.binvalue)

Note, however, that fn_varbintohexstr is un documented function, and thus
unsupported.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That works exactly the way I want ..
Thanks a lot
-Supratim

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9558844747DDEYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Supratim (supratim@.sagemetrics.com) writes:
> > I want to get the string representation of a hex number from a
> > varBinary column of a table.
> > For example I want to get the output : 'The Hex value is 0xFF'
> > but
> > select 'The Hex value is ' + convert(varchar(10), 0xFF)
> > retruns the ascii charecter for 0xFF
> > Any idea how do I get the hex form as it is?
> You can use
> select master.dbo.fn_varbintohexstr(@.binvalue)
> Note, however, that fn_varbintohexstr is un documented function, and thus
> unsupported.

How to get Stored Procedure output ?

I have a variable @.NetPay as type money, and a stored proc spGetNetPay.
The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.

Now I need assgin output from spGetNetPay to user variable @.NetPay. How can I do That?

Set @.NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?

I have little knowledge about User defided function. Is is the way I should go?

Thanks.

David J.

Create Procedure dbo.spGetNetPay (
 @.NetPay Money OUTPUT
) AS
SET @.NetPay = (Select Top 1 NetPay From SomeTable)
GO

|||Post your code for spGetNetPay to enable determine the best solution for you.|||I use Kay Lee's solution. My spGetNetPay is huge, having more than 200 factors to determine the net pay. However, adding an output parameter is an easy job for me:)

I am still interested in if it is possible to code "user defined function". It has its beauty such that you can add it in select statment column list. But it is totally new for me. I even never seen sample code...

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
Ashish
You can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish
|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:

> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

How to get output of sql command in columns

Hi,
I am working with Informix db in Digital Unix.
When I try to give any select commands and try to retrieve more than 5 columns in the same sql command, the output comes in rows instead of columns.
Is there a way to force it to come in columns?

i just use a simple format,
select column1 ,column2 ,column3 ,column4 ,column5 from tableyou should be getting 5 columns per record in the DB

column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5

how do you want the layout and why?

How to get name which has id ?

hi,
This is my table structure ,

Table 1 :
first_Id Count
Emp1000 10
Emp1001 5

Table 2:
Second_id Name
Emp1000 John
Emp1001 Raj

Now my output should be .

Name Count

John 10
Raj 5

How to write the query ?hi,

use inner join.. like
select y.name, x.count from [table 1] x inner join [table 2] y on x.first_id = y.second_id

see "joins" on BOL..

-clintz|||I think that this was more fully explored and answered in your other post. Check here.

How To Get HTML Rendering

I am using a ReportViewer with SQL Server Express. The ReportViewer provides options for output as Excel or PDF but not HTML. According to what I have read, SQL Server Express supports HTML rendering. How do I get it to do that?Let me clarify my own post. I have HTML-formatted text in two columns in the database. I need to have those two columns format as HTML in their respective textboxes. I've done a lot of reading on this issue and no one seems to have a very good solution.

How to get free space

Hi,
SQL2K, Win 2k.
I am trying to acquire space by deleting older records from a table.
When I ran sp_spaceused on a Table A, I got following output
Table A
rows: 145030470
reserved: 139653704 KB
data: 81832616 KB = 78GB
index_size: 23655616 KB = 22.5
unused: 34165472 KB
Afte that I had deleted around 60 million records based on certain criteria.
I thought I will get atleast 30GB free space. But when I checked in EM > task
pad, I only see around 14GB free space.
Is there anyway to get free space from the pages?
Thanks in advance
Chinna.See BOL... DBCC Shrinkfile
"Chinna Kondaveeti" wrote:
> Hi,
> SQL2K, Win 2k.
> I am trying to acquire space by deleting older records from a table.
> When I ran sp_spaceused on a Table A, I got following output
> Table A
> rows: 145030470
> reserved: 139653704 KB
> data: 81832616 KB = 78GB
> index_size: 23655616 KB = 22.5
> unused: 34165472 KB
> Afte that I had deleted around 60 million records based on certain criteria.
> I thought I will get atleast 30GB free space. But when I checked in EM > task
> pad, I only see around 14GB free space.
> Is there anyway to get free space from the pages?
> Thanks in advance
> Chinna.
>|||Thanks for the reply. I ran dbcc shrinkfile to get that 14GB free space. What
I am trying to say is, based on the size estimation, there should have been
more than 14GB free space. But I don't see that in the task pad view. Is
there any way to reorganize the pages/db files to get the remaining space?
Thanks in advance
Chinna.
"Wex" wrote:
> See BOL... DBCC Shrinkfile
> "Chinna Kondaveeti" wrote:
> > Hi,
> > SQL2K, Win 2k.
> > I am trying to acquire space by deleting older records from a table.
> > When I ran sp_spaceused on a Table A, I got following output
> >
> > Table A
> > rows: 145030470
> > reserved: 139653704 KB
> > data: 81832616 KB = 78GB
> > index_size: 23655616 KB = 22.5
> > unused: 34165472 KB
> >
> > Afte that I had deleted around 60 million records based on certain criteria.
> > I thought I will get atleast 30GB free space. But when I checked in EM > task
> > pad, I only see around 14GB free space.
> >
> > Is there anyway to get free space from the pages?
> >
> > Thanks in advance
> > Chinna.
> >
> >|||Does the table have a Clustered Index? If so use DBCC SHOWCONTIG to see the
level of fragmentation. You can use DBCC INDEXDEFRAG to defragment the
index. Watch out for performance drop of while defraging
"Chinna Kondaveeti" wrote:
> Thanks for the reply. I ran dbcc shrinkfile to get that 14GB free space. What
> I am trying to say is, based on the size estimation, there should have been
> more than 14GB free space. But I don't see that in the task pad view. Is
> there any way to reorganize the pages/db files to get the remaining space?
> Thanks in advance
> Chinna.
> "Wex" wrote:
> > See BOL... DBCC Shrinkfile
> >
> > "Chinna Kondaveeti" wrote:
> >
> > > Hi,
> > > SQL2K, Win 2k.
> > > I am trying to acquire space by deleting older records from a table.
> > > When I ran sp_spaceused on a Table A, I got following output
> > >
> > > Table A
> > > rows: 145030470
> > > reserved: 139653704 KB
> > > data: 81832616 KB = 78GB
> > > index_size: 23655616 KB = 22.5
> > > unused: 34165472 KB
> > >
> > > Afte that I had deleted around 60 million records based on certain criteria.
> > > I thought I will get atleast 30GB free space. But when I checked in EM > task
> > > pad, I only see around 14GB free space.
> > >
> > > Is there anyway to get free space from the pages?
> > >
> > > Thanks in advance
> > > Chinna.
> > >
> > >|||Chinna,
Do you have text or image column? If you do then you will have issues
reclaming all the space.
Did you use truncate option or noturncate option. There is a difference in
the way these work.
"Chinna Kondaveeti" <ChinnaKondaveeti@.discussions.microsoft.com> wrote in
message news:4DA0D6E0-E353-4751-A485-817CBD4A53E7@.microsoft.com...
> Thanks for the reply. I ran dbcc shrinkfile to get that 14GB free space.
What
> I am trying to say is, based on the size estimation, there should have
been
> more than 14GB free space. But I don't see that in the task pad view. Is
> there any way to reorganize the pages/db files to get the remaining space?
> Thanks in advance
> Chinna.
> "Wex" wrote:
> > See BOL... DBCC Shrinkfile
> >
> > "Chinna Kondaveeti" wrote:
> >
> > > Hi,
> > > SQL2K, Win 2k.
> > > I am trying to acquire space by deleting older records from a table.
> > > When I ran sp_spaceused on a Table A, I got following output
> > >
> > > Table A
> > > rows: 145030470
> > > reserved: 139653704 KB
> > > data: 81832616 KB = 78GB
> > > index_size: 23655616 KB = 22.5
> > > unused: 34165472 KB
> > >
> > > Afte that I had deleted around 60 million records based on certain
criteria.
> > > I thought I will get atleast 30GB free space. But when I checked in EM
> task
> > > pad, I only see around 14GB free space.
> > >
> > > Is there anyway to get free space from the pages?
> > >
> > > Thanks in advance
> > > Chinna.
> > >
> > >

How to Get Error Output from and OLE DB Command Destination

I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.

However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.

I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).

Any help would be greatly appreciated.

To get rows down the error output you change the ErrorRowDisposition property for the input to be redirect row. Have you done this? If not go the last page of the Advanced Editor, select the Input, and change the ErrorRowDisposition property.|||

I reviewed your suggestion of changing the ErrorRowDisposition value to RD_RedirectRow and that is where the issue is. I view the Advanced Editor for the OLE DB Command destination object and expand the Input Columns under OLE DB Command Input and see several input columns. However, the problem is every one of those columns has an ErrorRowDisposition=RD_NotUsed and the field is greyed out so I am unable to change the setting. Would I need to change any settings in the source or data conversion objects to allow these values to be editable?

|||Select the input and stop there, don't expand the columns. The setting is on the input which is in effect the parent for the columns.