|
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?
|
|
|
|
|
Hi,
I have the following tables:
Request Table: (RequestID, Request Description, QuoteID)
Quote Table: (QuoteID, QuoteNumber)
If I have 1 Quote Record. This quote record is assigned to 3 different Requests. I want a VIEW of those tables with a JOIN which will display:
vw_QuoteRequest:
================
QuoteID
1
I DO NOT want it to display:
vw_QuoteRequest:
================
QuoteID
1
1
1
I hope you see what I mean.
Thank you.
|
|
|
|
|
What else do you want to display? I assume it's not just the QuoteID because if it was, you would just do SELECT DISTINCT QuoteID.
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.
|
|
|
|
|
Actually I dont want to display anything. I have another table totalling 3:
Stage (StageID, StageNumber, ProjectID)
Request (RequestID, RequestNumber, RequestDescription, QuoteID)
Quote (QuoteID, QuoteNumber)
What I need to do is get a link between Quote and Stage. This will also provide a link to the Project Table.
I can only obtain this link if I go through the request table.
The reason I want this link is because I am querying the table according to projects and stages, which will return a set of Quotes based on that.
NOTE: This view will be used as part of another view, therefore this view will not be displaying anything.
However, Examining the senario I posted initially, you can see that my view will return 3 instances of the quote because the same quote ID is present in 3 different requests 
I hope this is clear. Please do not hesistate to ask about anything, or even suggest better methods of implementation.
Thank you for your time.
|
|
|
|
|
Thank you very much for SELECT DISTINCT!
It works now =D.
|
|
|
|
|
Hello,
I'm using sql server and I want to use a parameter in an IN clause in the command.
e.g.
select * from tableX where ID in @test
(The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') )
No matter how I declare or fill that variable I don't succeed.
Can anybody help please?
Thank you,
Wim
|
|
|
|
|
|
Thanks for your quick reply, but I don't want to use a stored procedure.
Just a simple statement with a sql variable.
declare @test varchar(600)
select @test = <list of="" id's="" in="" some="" form="">and do the select statement like this:
select * from tableX where ID in @test
(The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') )
|
|
|
|
|
@test is a variable replaced at query execution. Using your 'expanded' example, the resultant query would be looking for all rows where ID = "('ID1','ID2','ID3')". The IN would result to equal because there is only one element in the clause (@test).
SQL variables can only replace one element, not a list of more than one. You can get around this with dynamic SQL generation which uses your variable to build a new SQL string but, you indicate you don't want to use a Stored Proc.
Solution: Drop the variable and rewrite your code to build the full statement with a comma deliminated IN clause.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
