Correct Count

Oct 16, 2006

Hello Everyone,

I have the following code:


USE CHEC
SELECT
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
DAT01.[_@550] AS LoanType,
DAT01.[_@051] AS Branch,
DAT01.[_@TP] AS ProdTypeDescr,
SMT_Branches.[BranchTranType] AS TranType,
--SMT_Branches.[AUCode] AS AuCode,
Count(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
ON DAT01.[_@040] = [_@040]
INNER JOIN SMT_BRANCHES
ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
AND DAT01.[_@051] = '540'
--And SMT_Branches.[AUCode] = '1882'
And DAT01.[_@TP] = '115'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
SMT_Branches.[BranchTranType],
SMT_Branches.[AUCode],
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051],
DayofMonth ASC,
SMT_Branches.[AUCode] ASC
--COMPUTE sum(count(*))


Here is a partial display of the results:

APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39
APRIL13540115RETAIL39

The count is the same everytime. It should be different. What am I doing wrong?

TIA and have a great day!

Kurt

View 20 Replies


ADVERTISEMENT

Not Getting The Correct Count

May 12, 2008

I'm trying to get a year count and a year amount of payments made by a client. Below is my statement, it is not giving a correct count. If a client made more than one payment on the same day it counts it as 1.
What can I do to this statement to get correct totals?
SELECT DISTINCT                       Client_ID, DATEPART(year,PaymentDate) AS 'Year', SUM(AmountPaid) AS 'TotalYearlyPayments', COUNT(DISTINCT Payment_ID) AS 'YearlyPaymentCount'FROM        tblPaymentsWHERE Client_ID = @ClientIDGROUP BY Client_ID, DATEPART(year, PaymentDate)ORDER BY Client_ID, Year 

View 1 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct
sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

View 9 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

I use SQL 2000
I have a Column named Bool , the value in this Column is  0ã€?0ã€?1ã€?1ã€?1
I no I can use Count() to count this column ,the result would be "5"
but what I need is  "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is  2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.  
thank you very much
 

View 5 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.

View 5 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

Correct Me????

Nov 22, 2005

I've created C#.net program (behind code style). 
when I run it in Internet explorer, the following error occurs in IE window.
pls instruct me how to handle and correct this error.
And how to initialize the connectionstring...  Great thank!
 
 
Server Error in '/' Application.
--------------------------------------------------------------------------------
The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
Stack Trace:
 [InvalidOperationException: The ConnectionString property has not been initialized.]   System.Data.SqlClient.SqlConnection.Open() +809   CodeBox.BehindCode.getSubject() +80   CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31   System.Web.UI.Control.OnLoad(EventArgs e) +67   System.Web.UI.Control.LoadRecursive() +29   System.Web.UI.Page.ProcessRequestMain() +724 
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0

View 5 Replies View Related

Correct Use Of While

Oct 16, 2000

I have a simple while process to use with a trigger to insert values
into another table. IN VB this was simple but the while in TSQL seems
a little different. If anyone can point out my flaw greatly appreciated.

while @cnter < @nodays
--insert values
insert into table values (value1, value2)
--then increment counters and repeat
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
How or what is the best way to loop back?

View 2 Replies View Related

What Is Correct ?

Mar 4, 2001

If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?

Windows NT App log is full or SQL Server Agent stopped

I think SQL Server Agent stopped
How do you think..and why ?

thanks

View 2 Replies View Related

What Is Correct ?!!!!^_^

Mar 5, 2001

If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?

Windows NT App log is full or SQL Server Agent stopped

I think SQL Server Agent stopped
How do you think..and why ?

thanks

View 1 Replies View Related

Is This Correct

Apr 24, 2008

Im new to SQL so please bear with me & help me as to why Im not getting the desired results.

I want to find the difference between two sets of tables that reside in different databases but contain the same data.
I ONLY WANT
a. records that are only in A but not in B
b. records that are only in B but not in A
______________________________________________________________________



Here is what I wrote using something that I found in this forum -

CREATE PROCEDURE RPT_DETAILS
AS
BEGIN
DECLARE @Rowcount AS INT
DECLARE @First_Name AS VARCHAR(50)
DECLARE @Last_Name AS VARCHAR(50)
DECLARE @Id AS INT

CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))

INSERT INTO #Prowess
SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb
join beeentity be on bb.beebusinessguid = bb.beebusinessguid


INSERT INTO #SDK
SELECT cast(sa_ss as INT), first_name, last_name from ml


SELECT @ROWCOUNT = MAX(ID) FROM #SDK
PRINT '------------------------------------------------------------------------------------------'
PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------'
PRINT '------------------------------------------------------------------------------------------'

PRINT 'TOTAL Difference ('+ +
CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Prowess WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END


SELECT @ROWCOUNT = MAX(ID) FROM #Sdk

PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Sdk WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END

DROP TABLE #Prowess
DROP TABLE #Sdk

END

View 5 Replies View Related

Plz Correct This

May 5, 2008

declare @var varchar(50)
set @var= 'COLUMNNAME'
select ID, a.@var , b.@var
from rooper a
join jim_rooper b on b.id = a.id
join b_rooper bb on bb.id = a.id
where a.@var != b.@var


All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...

View 4 Replies View Related

T-SQL Correct Or Not???

May 28, 2008

Hi

I am writing T-SQL pls tell me wheather it is correct syntax or not

DECLARE @Chg1 VARCHAR(500)
SET @Chg1 = 'declare @AntID numeric exec casp_Switch_BackupData @AntID = #ANTID#'
SET @Chg1 = REPLACE(@Chg1,'#ANTID#','@AntID')
EXEC (@Chg1)

As I am getting following output
Command(s) completed successfully.

T.I.A

View 6 Replies View Related

Is It Correct?

Jun 2, 2008

hi!

i want to use IN query like

select ... from ...
where field1 in (...)
and field2 in (....)

when i write query like this, the result is display.
but its wrong.
is it correct?

View 3 Replies View Related

Is This Correct?

Aug 29, 2005

Dim strsql As String

strsql = "insert into MYENTRY(entryid) "
strsql &= "VALUES ("
strsql &= "'" & strtheEntryid & ")' "

I am not sure if this is correct snytax?

View 3 Replies View Related

Is This Correct?

Jun 28, 2006

hi

if row.col1 = nothing then ...

instead of (sql2k) if dtssource("col1") = null then...

TIA

View 3 Replies View Related

Is CLR Is Correct Way To Do It...

Jan 1, 2008

Hello to all,

Is it correct way to register my CLR library instead of having T-SQL codes (eg, Strored Procedure, Functions and Triggers) in the database in the following case:

Code security: If my Application (in .NET 2.0) and SQL Server Express in same PC and I have to give Windows-Administrator password to my application-user (to install/unistall some other softwares)

Thanks

PSDCHD

View 1 Replies View Related

Correct Syntax

Sep 25, 2006

HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI  have tried this:SELECT FName, LNameFROM Names_DBORDER BY  LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY  LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY  LName 'ASC' What is wrong with this syntax?ThanksLynn

View 2 Replies View Related

SQL Injection - Is This Correct

Jun 5, 2006

Hi, I'm building a web application in which I want to prevent SQL injection. I'm using stored procedures, and using queries on my app like this:in my database...create proc createStudy@title varchar(200),@text textasinsert into studies values(@title,@text)goand in my web app...query="createStudy '"+titleBox.Text+"','"+textBox.Text+"'";   //title and text boxes are textboxes, createStudy is a stored procedure in my databaseodmccommand cmd = new odbccommand(query,con);con.Open();cmd.ExecuteNonQuery();But before this I do this code:if (titleBox.Text.Contains("Drop") || titleBox.Text.Contains("Delete"))    messageLabel.Text="No permissions to do that";else(...my code)Is this ok to prevent SQL injection?!?

View 5 Replies View Related

Not Getting All The Values Correct

Aug 13, 2004

Hi,

This is strange....

I am getting my source data from another system am storing the SaleAmount of each product in a field the data type of which is [decimal](12, 2).

For some products I am getting an exact match (upto 2 decimal places) as compared with my source data BUT for some other products the value before the decimal places is correct but the 2 digits after the decimal place does not match with the source data :confused:

Even if this sounds stupid, can you please guide me. Am i missing some very basic and common sense thing?

Many TIA.

View 2 Replies View Related

What Is The Correct Syntax For Anything

Mar 15, 2007

I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...

and (r.id = 'anything')

View 7 Replies View Related

I Know I'm Stupid .. Please Help Me Correct Me.

Apr 13, 2008

I have two tables:
1) Table that holds all available ports.
2) Table that holds users for each port.

There may be times where one user is getting more than one port at a time.

I've built up an ASP .NET page that will display each user its port/s in one table.
On another table I want to display all the other available ports which the user doesn't posses and can buy to own.

My problem is where I try to build up the query. I just can't get all the other ports in normal display.



For example, this is what I need:

Ports table:
1/1
1/2
1/3
1/4

Users table:
User A , 1/1
User A , 1/2

ASP page display:

------------
User A Holds:
1/1
1/2
------------

Available:
1/3
1/4
------------


Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it !
I've tried all kinds of Joins and nesting SELECT queries with no luck.

I hate SQL. I want to die.

View 4 Replies View Related

Match The Correct Value

Apr 25, 2008

I have this sql statement:

SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
FROM PersonSkills INNER JOIN....

How can I choose a certain value from PersonSkills.Skills?

for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.

The tables that I have are:
PersonSkills:
id, ProductID, Skills

Product:
id, ProductName

Ive been reading and playing around with this without success.

View 6 Replies View Related

Please Correct This Procedure

May 23, 2008

create proc AuthorTable @AuthotID int,
@AuthorName varchar(20),
@AuthorBook varchar(60),

as
insert into dbo.Author
( AuthorID,
AuthorName,
AuthorBook

)
values
( @AuthotID ,
@AuthorName,
@AuthorBook
)


spatle

View 2 Replies View Related

Correct The Error

May 29, 2008

Hi friends,
I've created one procedure.I'm trying to execute that i got the error message like 'Must declare the scalar variable @series'.
but i declared it already.Table name starts with SI,dont have the fields like series and hono.I dont know how to correct this error.Please help me out.Here is my procedure.

alter proc procinsertAllFields
as
begin
declare @series varchar(10)
declare @hono varchar(5)
declare @tabname varchar(8)
declare @sql nvarchar(500)
if exists (select * from sysobjects where name=ltrim(rtrim('ccno_dir1')))
drop table ccno_dir1
set @sql='create table ccno_dir1(cc_no varchar(20),series varchar(1),
hono varchar(10),denom_code varchar(10),i_date datetime,d_date datetime,
locked varchar(10),csd_no varchar(10),invoice_no int,invoice_date datetime)'--print @sql
exec sp_executesql @sql

declare c cursor
for
select series=substring(name,3,1),hono =substring(name,4,5),name from sysobjects where name like 'si[1-3]_____'
open c
fetch next from c into @series,@hono,@tabname
while @@fetch_status=0
begin
print 'begin'
fetch next from c into @series,@hono,@tabname
set @sql='insert into ccno_dir1(cc_no,series,hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,invoice_date)
select cc_no,series=@series,hono=@hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,
invoice_date from '+@tabname
print @sql
exec sp_executesql @sql

end
close c
deallocate c
end

Thanks in advance!

kiruthika
http://www.ictned.eu

View 3 Replies View Related

Please Correct My Query

Sep 29, 2007

DECLARE @fullname nvarchar(50)
SET @fullname =
(SELECT (OriginalName + ContentType) AS Name
FROM Files_Dyn)
INSERT
INTO Files_Dyn(FullName)
VALUES (@fullname) where username = 'user_admin'
...what is the wrong with this query..it is giving 'Incorrect syntax near the keyword 'where'. Please correct me!
thanks in advance!

View 10 Replies View Related

Correct Syntax

Feb 25, 2008

Hi.

This is a Stored proc I am working on
I dont know much about stored procs.
This is what I am trying to achieve
The proc takes two input parameter and returns one outparameter.

This is what I have

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]

(
@Code char(10),
@Track varchar(30),
@NextAction ntext output
)
AS
BEGIN

SET NOCOUNT ON;
Declare @ID int;
@ID = Select Sequence from [dbo].[Track] where Code=@Code;
Declare @nextCode varchar;
@nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1);
@NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode;
return @NextAction;


END
GO

I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this.
Thanks

View 4 Replies View Related

But The Documentation Is NOT Correct

Jul 23, 2005

The documentation saysISNUMERIC returns 1 when the input expression evaluates to a validinteger, floating point number, money or decimal type; otherwise itreturns 0. A return value of 1 guarantees that expression can beconverted to one of these numeric types.(Cut and pasted from books online)Yet the following, one of many, example shows this is not true.select isnumeric(char(9))select convert(int, char(9))-----------1(1 row(s) affected)Server: Msg 245, Level 16, State 1, Line 2Syntax error converting the varchar value '' to a column of data typeint.So, besides filtering every possible invalid character, how do youconvert dirty values without error. I am not concerned that I may loosepossibly valid values or convert suspect strings to 0 (zero). I justwant to run without raising an error

View 3 Replies View Related

Is This Correct Use Of INSTEAD OF Triggers?

Jul 23, 2005

I am loading data from table A into table B. Certain columns in B havecheck constraints. I'd like for any rows from A, which violateconstraints, to be inserted into a third table, C. When the process isfinished, I'll have only good rows in B, and exeption rows in C.I am investigating INSTEAD OF triggers, however my question to thegroup is, is there a better or best practice for this scenario? Thismust be common. Any high-level tips or direction will be highlyappreciated.DAP

View 4 Replies View Related

Plz, What Is The Most Correct Method

Jul 20, 2005

Hi,Given 2 tables:Table1:id Auto,int,PrimKeytable2id inttxt nvarchar50Table2:id Auto,int,PrimKeyorder intThe scenario: Table2.order defines how the table1.txt is should be ordered.Table1.table2id contains the id of the order. This cannot be changed :(How do I select all Table1.txt values but ordered by their correspondingvalues of the table2.order field?--Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003

View 7 Replies View Related

What Is The Correct Option For Me?

Feb 21, 2007

Hello there,

I am not a company programmer but I do like to write programs for myself. I am currently looking at writing a Database program to track my DVD's, CD's Tapes and the likes (as well as quite a bit of other data). While discussing this with others it was suggested that I use MSDE (since it's free) and that way I could learn more about SQL databases at the same time.

When I connected to the MS website to download MSDE I found that it will not be supported with Vista. Since I am planning on getting a new PC which will most likley come with Vista I am now wondering what would be the best way to develop my database.

1. Should I use Access (Since it will only be accessed across my home network)?

2. Is there a version of SQL (that does not require a License fee) for Home use?

Please tell me what options I have now!.

View 1 Replies View Related







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