|
hey , i have a query that collects data from 3 tables using one inner join and left join but the problem that there are many duplicate rows
<
SELECT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)
thanks for your time.
>
|
|
|
|
|
Underscore make that unreadable bleh...
What I do is a 2 query pass, the inside query uses ROW_NUMBER() and PARTITION OVER the key fields (those that make up the unique record)
I then delete records with a row number > 1
Here is a snippet I keep around for deduping
DECLARE @Tbl TABLE (IDField INT, RowNo INT)
INSERT @Tbl
SELECT lnkStrategyNodeID IDfield,
ROW_NUMBER() OVER(PARTITION BY StrategyID,NodeID ORDER BY lnkStrategyNodeID) Rw
FROM lnkStrategyNode
DELETE
FROM lnkStrategyNode
WHERE lnkStrategyNodeID IN (SELECT IDField FROM @Tbl WHERE RowNo > 1)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use the DISTINCT keyword
SELECT DISTINCT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
|
The following error occurred while trying to connect database
ora-00257 archiver error solution in sql developer
need solution for sql developer 
|
|
|
|
|
As I remember this error is caused by out-of-space in your storage, so log data can not be stored (archived) while trying to run some transaction...
Please check your disks...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
I have already checked the disk its not full
suggest any other solution.............
|
|
|
|
|
Kornfeld is absolutely correct, you're having space problems.
The simplest and best solution is usually to run a backup, that will cleanup old archived files (depending on your settings).
If you're running Oracle express edition, it's not the harddrive space that's the limit, it's the maximum storage space allowed. 4GB Before 11.2G, 11 GB after.
Also check your settings so that all file locations are correct.
|
|
|
|
|
Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.
Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter archive_log_dest is set up properly for archiving.
|
|
|
|
|
I have a table called Inventory with the following schema
ID, ItemID, Name, Value, CollectedDate
All of the columns allow duplicates except ID, so there may be multiple instances of a given Name, but with a different CollectedDate
For a given ItemID, I need to return the Value with the latest CollectedDate for each distinct Name. To add to the complexity, I need to put all of the returned items into a single row, using the Name as the column name.
So, what I'm starting with is this:
ID ITEMID NAME VALUE COLLECTEDDATE
1 12 Test1 abc 01/01/2014 00:00:00
2 12 Test2 def 01/01/2014 00:00:00
3 12 Test3 ghi 01/01/2014 00:00:00
4 12 Test4 jkl 01/01/2014 00:00:00
5 12 Test1 mno 01/03/2014 00:00:00
6 12 Test3 stu 01/05/2014 00:00:00
7 12 Test4 vwx 01/06/2014 00:00:00
8 12 Test4 yz 01/09/2014 00:00:00
9 14 Test1 123 01/09/2014 00:00:00
And my goal is to get to this:
ITEMID Test1 Test2 Test3 Test4
12 mno def stu yz
14 123 NULL NULL NULL
I created the following temp tables:
- @tempNames - contains a list of all of the distinct Names
- @tempIDs that contains all of the unique ItemIDs
- @tempInventory that contains all of the desired schema shown above.
How do I get one row of info for each ItemID to the @tempInventory table?
(I don't even know what to call what I'm trying to do. Otherwise, I'd google it).
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
you are looking for a pivot query[^]
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Awesome hint - fingered it out.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Hello,
The following code returns the results we want.
However, they are being repeated.
For instance, we have ElectionName, Position, CandidateName and TotalCount.
Rather then display the results in the following format (current format)
ElectionName Position Candidate Name TotCount
Election Name 1 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 2 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 1 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 1 27
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 8
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 27
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 8
Election Name 3 Mar 19 2014 1ST Vice President Candidate VP 27
etc
etc
We would like the results displayed in the following format:
ElectionName Position Candidate Name TotCount
Election Name 1 Mar 19 2014 President Candidate Name 1 13
President Candidate Name 2 22
President Candidate Name 1 13
President Candidate Name 2 22
President Candidate Name 1 13
President Candidate Name 2 22
Election Name 1 Mar 19 2014 1ST Vice President Candidate VP 1 27
1ST Vice President Candidate VP 1 8
1ST Vice President Candidate VP 1 27
1ST Vice President Candidate VP 1 8
1ST Vice President Candidate VP 1 27
etc
etc
with related positions group together with ElectioName.
Below is my code. Any help is greatly appreciated.
Select (e.ElectionName + CAST(e.ClosingDate AS VARCHAR(12))) as [ElectionName],
p.position,
c.candidateName,
COUNT(*) AS TotalCount
from ElectionResults er
inner join candidates c on er.candidateId = c.candidateid
inner join Positions p on er.positionId = p.positionId
inner join Elections e on c.PositionId = c.PositionId
group by grouping Sets(e. ElectionName),p.position,p.positionid,c.candidateName, e.closingDate
order by p.PositionId
|
|
|
|
|
This can be achieved by looping through the resultset and clear the ElectionName if the Position is the same as in the previous row. But this doesn't belong to the database.
|
|
|
|
|
As Jorgen has said, this is the wrong tool for the job, the database serves up the data, your UI/Report/Consumer of the data formats that data according you your requirements.
So do your loop/delete in the client, not the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It is doing the grouping by electionName but I can't figure out how to count the total count for each candidate.
Can you guys add that bit?
I just to get the total scores for each candidateName
WITH
ctePreAgg AS
(
SELECT (ElectionName + ' - ' + CAST(ClosingDate AS VARCHAR(12))) electionName, CASE WHEN Position='Member' THEN '' ELSE Position END As Position, CASE WHEN c.CurrentOfficeHolder='Incumbent' THEN CandidateName + '('+ c.CurrentOfficeHolder + ')' ELSE CandidateName END As CandidateName , c.PositionId,COUNT(*) TotalVotes
FROM Candidates c
JOIN Positions p ON c.PositionId = p.PositionId
JOIN Elections e on c.ElectionId = e.ElectionId
WHERE c.ElectionId IN (1,2,3)
GROUP BY Position, CandidateId, CandidateName,c.PositionId,CurrentOfficeHolder,AnswerType,ElectionName, ClosingDate
)
SELECT [OrgName] = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.Position) ELSE mh.ElectionName END
,Names = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.CandidateName) ELSE '' END
,PositionId = CASE WHEN GROUPING(mh.PositionId) = 0 THEN mh.PositionId ELSE '' END
,TotalCount = CASE WHEN GROUPING(mh.PositionId) = 0 THEN COUNT(*) ELSE '' END
FROM ctePreAgg mh
GROUP BY ElectionName,PositionId WITH ROLLUP
HAVING GROUPING(mh.ElectionName) = 0
ORDER BY mh.ElectionName, GROUPING(mh.PositionId) DESC, mh.PositionID;
|
|
|
|
|
Well you are doing exactly what we advised you not to do and you want suggestion on how to improve doing that. Getting the total/count per group IS a database thing, formatting the text is NOT.
Personally I would use ROW_NUMBER and PARTITION OVER but not with a CTE.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok, sorry for my stupidity but I thought this is a database forum.
If it is, I am only trying to figure out how to get Total Count for each candidate, not formatting request.
|
|
|
|
|
795]] Warning: Truncation may occur due to inserting data from data flow column "AS" with a length of 16 to database column "AS" with a length of 15.
I resolved this issue yesterday but today it failed again.
The package is successful if I run from Microsoft Visual studio. However, if I run initiate the job through SQL job agent it will fail.
Does anyone have a better idea to resolve this?
|
|
|
|
|
Please do not re-post - it will not get you a quicker or better answer, but may piss off some of the CPians around us 
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thank you for your observation.
|
|
|
|
|
You're more than welcome..
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
And when you run it using the different methods do you
- Use EXACTLY the same data. How do you know?
- Use EXACTLY the same database? How did you verify it?
|
|
|
|
|
One of the issues I have run across over time is in the data itself. If your source is a legacy system then there may be a crlf issue, it can be either 1 or 2 characters. They sometimes use weird row terminators as well.
I would check if AS is the last field in a row and then pull the data into a string and check the length of the field. VS may well treat line feeds differently than SSIS.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
[795]] Warning: Truncation may occur due to inserting data from data flow column "AS" with a length of 16 to database column "AS" with a length of 15.
I resolved this issue yesterday but today it failed again.
The package is successful if I run from Microsoft Visual studio. However, if I run initiate the job through SQL job agent it will fail.
Does anyone have a better idea to resolve this?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
