|
Hi,
This is quick question regarding calling function from join.
In my project i am facing one situation where i need to use join
between one temporary table and database table.From that join statement
i need to call one function which taking 4 input parameters of temporary table and 4 input parameters of database table and returns one varchar2 value.
Now here is question
Let's say i creates one join statement like :
"Select temp_table.firstname,temp_table.lastname,temp_table.ssn,
db_table.firstname,db_table.lastname,db_table.ssn,
getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)
from temporary_table ,database_table
where
(
/*
some code in where clause which joins temporary table and database table.
*/
)
Now my question is that do my
getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)
function returns same value for value based on input parameter or it returns
different differnt value every time.
Or can some one say me that how my function works in join query???
Actually , my requirement is that depends on where clause of join,
function takes input parameters and return back to select query and
everytime function should return new value for one by one record.
Is this join works of not for that??
Can some body help me out for this??
Thankx.
regards,
montu3377
|
|
|
|
|
My first idea would be to try it and see, but I think i see what you mean and your function should produce a different value based on the row, as long as the input parameters are different, of course...
|
|
|
|
|
I have the Stored Procedure below which I run using the code below that but each time I run the procedure the row gets added to the table but the @Identity parameter is always the value 1. There is a column on the Invoice table called InvoiceID which has Identity set as Yes.
Am I doing something wrong?
Thanks
Paul
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertInvoice]
-- Add the parameters for the stored procedure here
@IClientID int
,@InvoiceNumber varchar(15)
,@InvoiceDate datetime
,@PaymentStatus tinyint
,@Description varchar(255)
,@InvoiceNotes text
,@ICustomCheck1 bit
,@ICustomCheck2 bit
,@ICustomCheck3 bit
,@ICustomCheck4 bit
,@ICustomCheck5 bit
,@ICustomCheck6 bit
,@IsAgent bit
,@Identity int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Invoice]
([IClientID]
,[InvoiceNumber]
,[InvoiceDate]
,[PaymentStatus]
,[Description]
,[InvoiceNotes]
,[ICustomCheck1]
,[ICustomCheck2]
,[ICustomCheck3]
,[ICustomCheck4]
,[ICustomCheck5]
,[ICustomCheck6]
,[IsAgent])
VALUES
(@IClientID
,@InvoiceNumber
,@InvoiceDate
,@PaymentStatus
,@Description
,@InvoiceNotes
,@ICustomCheck1
,@ICustomCheck2
,@ICustomCheck3
,@ICustomCheck4
,@ICustomCheck5
,@ICustomCheck6
,@IsAgent)
SET @Identity = SCOPE_IDENTITY()
END
SqlCommand cmd = dCon.CreateCommand();
cmd.CommandText = "InsertClient";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@HoldingNumber", SqlDbType.VarChar).Value = client.HoldingNumber;
cmd.Parameters.Add("@TradingTitle", SqlDbType.VarChar).Value = client.TradingTitle;
cmd.Parameters.Add("@Surname", SqlDbType.VarChar).Value = client.Surname;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = client.ContactName;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = client.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = client.Address2;
cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = client.Address3;
cmd.Parameters.Add("@Address4", SqlDbType.VarChar).Value = client.Address4;
cmd.Parameters.Add("@Postcode", SqlDbType.VarChar).Value = client.Postcode;
cmd.Parameters.Add("@Tel", SqlDbType.VarChar).Value = client.Tel;
cmd.Parameters.Add("@Tel2", SqlDbType.VarChar).Value = client.Tel2;
cmd.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = client.Mobile;
cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = client.Fax;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = client.Email;
cmd.Parameters.Add("@Agent", SqlDbType.TinyInt).Value = client.Agent;
cmd.Parameters.Add("@CTSUsername", SqlDbType.VarChar).Value = client.CTSUsername;
cmd.Parameters.Add("@CTSPassword", SqlDbType.VarChar).Value = client.CTSPassword;
cmd.Parameters.Add("@LMeNTRY", SqlDbType.Bit).Value = client.LMeNTRY;
cmd.Parameters.Add("@FarmMatters", SqlDbType.Bit).Value = client.FarmMatters;
cmd.Parameters.Add("@ClientNotes", SqlDbType.VarChar).Value = client.ClientNotes;
cmd.Parameters.Add("@Support", SqlDbType.TinyInt).Value = client.Support;
cmd.Parameters.Add("@Licenes", SqlDbType.Int).Value = client.Licenes;
cmd.Parameters.Add("@IsBureau", SqlDbType.Bit).Value = client.IsBureau;
cmd.Parameters.Add("@OperatingSystem", SqlDbType.TinyInt).Value = client.OperatingSystem;
cmd.Parameters.Add("@SBI", SqlDbType.VarChar).Value = client.SBI;
cmd.Parameters.Add("@CustomCheck1", SqlDbType.Bit).Value = client.CustomCheck1;
cmd.Parameters.Add("@CustomCheck2", SqlDbType.Bit).Value = client.CustomCheck2;
cmd.Parameters.Add("@CustomCheck3", SqlDbType.Bit).Value = client.CustomCheck3;
cmd.Parameters.Add("@CustomCheck4", SqlDbType.Bit).Value = client.CustomCheck4;
cmd.Parameters.Add("@CustomCheck5", SqlDbType.Bit).Value = client.CustomCheck5;
cmd.Parameters.Add("@CustomCheck6", SqlDbType.Bit).Value = client.CustomCheck6;
cmd.Parameters.Add("@Identity", SqlDbType.Int);
cmd.Parameters["@Identity"].Direction = System.Data.ParameterDirection.Output;
if(isClosed) dCon.Open();
cmd.ExecuteNonQuery();
if(isClosed) dCon.Close();
return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
|
|
|
|
|
Stored procedure name as defined:
Howellsy wrote: ALTER PROCEDURE [dbo].[InsertInvoice]
Stored procedure called by your code:
Howellsy wrote: cmd.CommandText = "InsertClient";
Maybe that could be the problem? Or is it just a copy-past error (looks like it)?
It would be helpful to see the definition of the Invoice table.
Jon Sagara
I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So i had to leave the place as soon as possible.
--Mr.Prakash
Blog | Site | Articles
|
|
|
|
|
It was just a copy and paste error, that is the actually Stored Procedure, which is much the same but with different data fields. Below the Stored Procedure is the sql for creating the table. Not sure what the table definition is and how to get it, could you please tell me how to get it?
Kind Regards
Paul
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertClient]
-- Add the parameters for the stored procedure here
@HoldingNumber varchar(12)
,@TradingTitle varchar(30)
,@Surname varchar(30)
,@ContactName varchar(30)
,@Address1 varchar(30)
,@Address2 varchar(30)
,@Address3 varchar(30)
,@Address4 varchar(30)
,@Postcode varchar(30)
,@Tel varchar(20)
,@Tel2 varchar(20)
,@Mobile varchar(20)
,@Fax varchar(20)
,@Email varchar(255)
,@Agent tinyint
,@CTSUsername varchar(15)
,@CTSPassword varchar(15)
,@LMeNTRY bit
,@FarmMatters bit
,@ClientNotes varchar(255)
,@Support tinyint
,@Licenes int
,@IsBureau bit
,@OperatingSystem tinyint
,@SBI varchar(20)
,@CustomCheck1 bit
,@CustomCheck2 bit
,@CustomCheck3 bit
,@CustomCheck4 bit
,@CustomCheck5 bit
,@CustomCheck6 bit
,@Identity bit OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Client]
([HoldingNumber]
,[TradingTitle]
,[Surname]
,[ContactName]
,[Address1]
,[Address2]
,[Address3]
,[Address4]
,[Postcode]
,[Tel]
,[Tel2]
,[Mobile]
,[Fax]
,[Email]
,[Agent]
,[CTSUsername]
,[CTSPassword]
,[LMeNTRY]
,[FarmMatters]
,[ClientNotes]
,[Support]
,[Licenes]
,[IsBureau]
,[OperatingSystem]
,[SBI]
,[CustomCheck1]
,[CustomCheck2]
,[CustomCheck3]
,[CustomCheck4]
,[CustomCheck5]
,[CustomCheck6])
VALUES
(@HoldingNumber
,@TradingTitle
,@Surname
,@ContactName
,@Address1
,@Address2
,@Address3
,@Address4
,@Postcode
,@Tel
,@Tel2
,@Mobile
,@Fax
,@Email
,@Agent
,@CTSUsername
,@CTSPassword
,@LMeNTRY
,@FarmMatters
,@ClientNotes
,@Support
,@Licenes
,@IsBureau
,@OperatingSystem
,@SBI
,@CustomCheck1
,@CustomCheck2
,@CustomCheck3
,@CustomCheck4
,@CustomCheck5
,@CustomCheck6)
SET @Identity = SCOPE_IDENTITY()
END
USE [BreedSociety]
GO
/****** Object: Table [dbo].[Client] Script Date: 01/26/2007 08:39:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Client](
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[HoldingNumber] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TradingTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Surname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address3] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address4] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Postcode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Mobile] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Agent] [tinyint] NOT NULL,
[CTSUsername] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CTSPassword] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LMeNTRY] [bit] NOT NULL,
[FarmMatters] [bit] NOT NULL,
[ClientNotes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Support] [tinyint] NOT NULL,
[Licenes] [int] NOT NULL,
[IsBureau] [bit] NOT NULL,
[OperatingSystem] [tinyint] NOT NULL,
[SBI] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomCheck1] [bit] NOT NULL,
[CustomCheck2] [bit] NOT NULL,
[CustomCheck3] [bit] NOT NULL,
[CustomCheck4] [bit] NOT NULL,
[CustomCheck5] [bit] NOT NULL,
[CustomCheck6] [bit] NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
|
|
|
|
|
Howellsy wrote: ,@Identity bit OUTPUT
A BIT can only have a value of 1 or 0 (or NULL). Change the type to INT.
|
|
|
|
|
Thank you very much, what a silly mistake to make.
|
|
|
|
|
Hi
i want to select the values which are not in the other table mentioned below:
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type AND tran_type AND new_account AND rsp_code_rsp AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
the following error occuring:
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
plz help this.
SHAHZAD ASLAM
Software Engineer
shehzadaslam@hotmail.com
|
|
|
|
|
Try these steps........
First u make inner join with both table
and check all fields seperately with not equel operator ( add condition with And).
Or,
U can also use outer join. plz refer outer join...........
Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
The error you are getting here is because you have not defined boolean conditions for your AND statements. For instance, AND new_account, "AND new_account what"? Does this mean AND new_account = 1?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
if i remove the boolean field new_account from query still same error is occuring.
|
|
|
|
|
AND new_account AND rsp_code_rsp
But what about rsp_code_rsp - That is exactly the same thing.
Also your IN(SELECT...) statement isn't right either. You should only be specifying a single column in the SELECT statement in the IN() expression.
|
|
|
|
|
rsp_code_rsp is not boolean field the only boolean field is new_account but if i remove this new_account from query the same error is occuring.
|
|
|
|
|
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type AND tran_type AND new_account AND rsp_code_rsp AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
Right - let's analyze this statement:
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type What does this equate to?
AND tran_type Same here...
AND new_account And here...
AND rsp_code_rsp And here...
AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
Change to reference only one attribute here.
It is obvious from the above that you need to supply the other side of the test in each case highlighted in bold. For instance, you may want to specify AND tran_type = 2.
However, I think what you are trying to do in your statement is to test to retrieve records from transactions where there is not a corresponding entry in tran_types_dimension. Is this correct?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
In which case I would do this:
SELECT DISTINCT
t1.message_type, t1.tran_type, t1.new_account,
t1.rsp_code_rsp, t1.fraudrspcode
FROM TRANSACTIONS t1
LEFT OUTER JOIN Tran_Types_Dimension t2
ON t1.message_type = t2.message_type AND
t1.tran_type = t2.tran_type AND
t1.new_account = t2.new_account AND
t1.rsp_code_rsp = t2.rsp_code_rsp AND
t1.fraudrspcode = t2.fraudrspcode
WHERE
t2.message_type IS NULLThe LEFT JOIN causes the database to return all rows where there is a match (the boolean condition in the ON clause), plus any rows from the left-hand table in the join where there is no match in the right-hand table. In the latter case, the fields from the right-hand table are filled with NULLs. The WHERE clause causes all the rows that actually did match to be discarded, leaving only those where there wasn't a match (i.e. NOT IN). This only works properly if the field you're testing cannot (or, at least, does not) contain NULLs. Strictly you should probably test that all columns from the right-hand table are NULL, but it saves typing to only test one.
|
|
|
|
|
Thanks for help me i have made changes in the query now i m thinking that it is not effecient what u think?
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
new_account=1 AND
(message_type NOT IN
(SELECT message_type FROM Tran_Types_Dimension) OR
tran_type NOT IN
(SELECT tran_type FROM Tran_Types_Dimension) OR
rsp_code_rsp NOT IN
(SELECT rsp_code_rsp FROM Tran_Types_Dimension) OR
fraudrspcode NOT IN
(SELECT fraudrspcode FROM Tran_Types_Dimension)
) OR
new_account<>1 AND
(message_type NOT IN
(SELECT message_type FROM Tran_Types_Dimension) OR
tran_type NOT IN
(SELECT tran_type FROM Tran_Types_Dimension) OR
rsp_code_rsp NOT IN
(SELECT rsp_code_rsp FROM Tran_Types_Dimension) OR
fraudrspcode NOT IN
(SELECT fraudrspcode FROM Tran_Types_Dimension))
what type of indexes should i made on transaction table?
|
|
|
|
|
SQL requires that you actually compare a value to something in a comparison expression. It does not implicitly convert a value to a boolean as C++ does, or perform conditional logic based on integers as C does. This is true even for bit columns/variables.
|
|
|
|
|
hallo freinds,
i want to display data from 2 columns as 1 column in my select query.
for e.g. -> i have surname & name as 2 different columns in my employee_mast table.
i want to display both as in one column so i use following query
SELECT e.Surname + e.Name as nm FROM emp_mast e;
but unsuccessfull, it is displaying 0
please help me.
priya
|
|
|
|
|
try it
select (surname + Name) as FullName from TableName
-- modified at 2:50 Thursday 25th January, 2007
Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
thnx for help but this query is showing 0 in result 
i m using mysql
if you have another idea please tell me
-Regards priya
priya
|
|
|
|
|
try it...........
Select (CONCAT(FirstName,LastName)) Like 'Fullname' From Tablename
or
SELECT CONCAT(FirstName,LastName) AS FullName From TableNme
-- modified at 3:25 Thursday 25th January, 2007
Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
yes i got it.
Thank you so much parwej.
-Regards Priya
priya
|
|
|
|
|
I have the following SP
IF EXISTS (SELECT LID FROM [HomeSolutions].[dbo].[GlobalLendersList] WHERE [Name] = @Name) <br />
RAISERROR('This value already exists.', 11, 2) <br />
ELSE <br />
INSERT INTO [HomeSolutions].[dbo].[GlobalLendersList]<br />
([Name]<br />
,[Status])<br />
VALUES<br />
(@Name<br />
, '0')<br />
<br />
INSERT INTO [HomeSolutions].[dbo].[PersonalLendersList]<br />
([UserID]<br />
,[LenderID])<br />
VALUES<br />
(@UserID , @@IDENTITY)
My problem is that the second INSERT occurs without any regard to the IF ELSE Statment that the other INSERT obeys. How can I encapsulate both INSERTS so they either both occur or both dont occur. Thanks
|
|
|
|
|
To create a block of statements (or compound statements), BEGIN and END is used, that is:
BEGIN
statement1
statement2
END
You can modify your code something like this:
IF EXISTS (SELECT LID FROM [HomeSolutions].[dbo].[GlobalLendersList] WHERE [Name] = @Name)
RAISERROR('This value already exists.', 11, 2)
ELSE
<code>BEGIN</code>
INSERT INTO [HomeSolutions].[dbo].[GlobalLendersList]
([Name]
,[Status])
VALUES
(@Name
, '0')
INSERT INTO [HomeSolutions].[dbo].[PersonalLendersList]
([UserID]
,[LenderID])
VALUES
(@UserID , @@IDENTITY)
<code>END</code>
Regards
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Thanks mate that worked a treat. I originally tried the TRANSACTION - COMMIT keywords to no avail.
It works with or without ';' at the end of each line. Is this a required part of the syntax or not?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
