Query returning the same row multiple times

sql query returns same record multiple times
sql select same record multiple times
sql union remove duplicates based on one column
sql union all example
sql union different columns
sql union distinct
insert and update in one query in sql
sql union merge rows

My query is returning one result multiple times. I know the answer has to be simple, but I'm stumped. Any help is appreciated.

My code is below.

USE ODS

DECLARE @recalctime AS TIME
DECLARE @portfolio AS VARCHAR(MAX)
DECLARE @loannumber AS BIGINT
DECLARE @zipcode AS VARCHAR(10)
DECLARE @timezone AS VARCHAR(10)
DECLARE @calltime AS TIME
DECLARE @cst AS DATETIME
DECLARE @calldate AS DATETIME

SELECT
@portfolio = CASE
    WHEN r.PortfolioID = '10' THEN 'A'
    WHEN r.portfolioid = '11' THEN 'B'
    WHEN r.PortfolioID = '12' THEN 'C'
    ELSE CAST(r.portfolioid AS VARCHAR)
    END 
, @calldate = CAST(r.RowCreatedDate AS DATE) 
, @loannumber = r.loannumber 
, @zipcode = r.zipcode 
, @timezone = r.zone 
, @calltime = CONVERT(char(10), r.contacttime, 108) 
, @recalctime = CASE
    WHEN r.Zone = 'PST' THEN DATEADD(HOUR, -3, r.contacttime)
    WHEN r.Zone = 'CST' THEN DATEADD(HOUR, -1, r.contacttime)
    WHEN r.Zone = 'EST' THEN r.contacttime
    WHEN r.Zone = 'MST' THEN DATEADD(HOUR, -2, r.contacttime)
    ELSE CONVERT(char(10), r.contacttime, 108)
    END 

FROM dbo.table r (nolock)


SELECT 
@portfolio AS 'Product'
, @loannumber AS 'Loan Number'
, CAST(@calldate AS DATE) AS 'Call Date'
, @zipcode AS 'Zip Code'
, CONVERT(char(10), @calltime, 108) AS 'Timezone'
, CONVERT(char(10), @recalctime, 108) AS 'Call Time'
, DATEPART(hh, @recalctime)
, @timezone AS 'Recalc Time'

FROM dbo.table r (nolock)

I'm getting 89,928 rows of the same result.

Your first query will perform local var assignment for each row in the table, rewriting this value for every row, and ending up holding values derived from the last row. Your second query returns those values as many times as there are rows.

If you just want to output your derived values, combine these two queries into one, and don't use local variables at all.

Make SQL Select same row multiple times, Sql query returns same record multiple times. SQL returning the same result multiple times, You have a cartesian product in your query. You should JOIN assets  I want a query which will checks if a particular user is assigned 'admin' or 'superadmin' role (checking for more roles may be required in the future). I tried to do it using PLSQL by selecting every role for a user and passing it to a variable, then checking if the any of those selected roles is either 'admin' or 'superadmin'.

The first query is assigning the variables per row which might not be what you are trying to do.

In general, most uses of the first query would include a where clause so the values would be from a specific row:

SELECT
@portfolio = CASE
    WHEN r.PortfolioID = '10' THEN 'A'
    WHEN r.portfolioid = '11' THEN 'B'
    WHEN r.PortfolioID = '12' THEN 'C'
    ELSE CAST(r.portfolioid AS VARCHAR)
    END 
, @calldate = CAST(r.RowCreatedDate AS DATE) 
, @loannumber = r.loannumber 
, @zipcode = r.zipcode 
, @timezone = r.zone 
, @calltime = CONVERT(char(10), r.contacttime, 108) 
, @recalctime = CASE
    WHEN r.Zone = 'PST' THEN DATEADD(HOUR, -3, r.contacttime)
    WHEN r.Zone = 'CST' THEN DATEADD(HOUR, -1, r.contacttime)
    WHEN r.Zone = 'EST' THEN r.contacttime
    WHEN r.Zone = 'MST' THEN DATEADD(HOUR, -2, r.contacttime)
    ELSE CONVERT(char(10), r.contacttime, 108)
    END 

FROM dbo.table r (nolock)
WHERE [MyColumn] = something

There is no need in the second query for the "from dbo.table" as this will select out the variables per record in the table.

 SELECT 
    @portfolio AS 'Product'
    , @loannumber AS 'Loan Number'
    , CAST(@calldate AS DATE) AS 'Call Date'
    , @zipcode AS 'Zip Code'
    , CONVERT(char(10), @calltime, 108) AS 'Timezone'
    , CONVERT(char(10), @recalctime, 108) AS 'Call Time'
    , DATEPART(hh, @recalctime)
    , @timezone AS 'Recalc Time'

;

If you don't use the variables for anything else you can combine it all together in a single query:

SELECT
    [Product] = CASE
        WHEN r.PortfolioID = '10' THEN 'A'
        WHEN r.portfolioid = '11' THEN 'B'
        WHEN r.PortfolioID = '12' THEN 'C'
        ELSE CAST(r.portfolioid AS VARCHAR)
        END 
   -- Note you can also use the 'AS' syntax depending on version of sql server
    , CAST(r.RowCreatedDate AS DATE) AS 'Call Date'
    ...etc        
    FROM dbo.table r (nolock)
    WHERE [MyColumn] = something

SQL: UNION ALL Operator, Each SELECT statement within the UNION ALL must have the same number of This SQL UNION ALL example would return the supplier_id multiple times in UNION ALL operator with different column names and order the query results. Creating a Query to return search results from multiple one-to-many tables but getting too many combinations I am trying to create a query that contains 15 tables. 14 of the tables are connected to one main table with the same foreign key.

Because in table "dbo.table" have total 89,928 rows, That is the reason it shows 89,928 times.

you need to select only Variables

SELECT 
@portfolio AS 'Product'
, @loannumber AS 'Loan Number'
, CAST(@calldate AS DATE) AS 'Call Date'
, @zipcode AS 'Zip Code'
, CONVERT(char(10), @calltime, 108) AS 'Timezone'
, CONVERT(char(10), @recalctime, 108) AS 'Call Time'
, DATEPART(hh, @recalctime)
, @timezone AS 'Recalc Time'

SQL results displays same result multiple times - PHP, I have a query that searches all videos that a selected instructor is in. it shows each result multiple times until the last row that is only d… the columns returned by the SELECT statement all contain the same duplicate data. Using the pipeline, you'd need to iterate through a loop N times and the query would get access to each row as the loop runs. Instead, I created a function that returns a nested table. All the function has to do is initialize the table, then extend it N times.

Pro Java 8 Programming, As mentioned, performing the same query multiple times in a single transaction If transaction Athen executes the same query and only nine rows are returned,  When a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL), if one of the values returned by the inner query is a null value, the entire query returns: Mark for Review (1) Points A list of Nulls All rows that were selected by the inner query including the null values

4. Inserting, Updating, Deleting, You can update one record, and you can delete one record. Use the INSERT statement with the VALUES clause to insert one row at a time: You want to take rows returned by a query and insert those rows into multiple target tables. Performing a LINQ query on the view returns incorrect and confusing results. Close Ad the correct number of records for each group is returned but the second column value is the same for each row.

Solved: Repeat same rows multiple times in a result, Solved: I have an interesting challenge to repeat same rows multiple times I am taking about SQL query (UNION ALL,IN)used to extract data  But all the three rows are having same data (first row data). That is, three copies of first row data and becuase of this I am not able to update the required column for the remaining two rows. Even I have unique id column AuditLog_Id in the table.

Comments
  • Change FROM dbo.table r in the second query to FROM DUAL. You just want to return the values of the variables once, not once for each row in the table.
  • Not sure how the top query ran successfully if your table has 89K rows as you don't seem to have a WHERE clause, but for the bottom query, remove the FROM clause completely, to select just the variables.
  • I do not believe FROM Dual is SQL Server. Or at least not SQL 2012 and prior.