Databases
StackOverflow2013
Postsdbo
POSQL Server row_number and datatables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server row_number and datatables
    primarykey
    data
    text
    <p>I'm working with SQL Server and Datatables in a PHP environment; I've researched a bit, and found that by using row_number I can achieve paged results, but I am getting:</p> <blockquote> <p>The multi-part identifier "Flags.FlagValue" could not be bound.</p> </blockquote> <pre><code>SELECT dbo.PlayingCharacters.PlayerName,dbo.PlayingCharacters.CurrentLevel,dbo.PlayingCharacters.XP,(SELECT CASE WHEN dbo.PlayingCharacters.Karma &gt;= 1 THEN 2 WHEN dbo.PlayingCharacters.Karma &lt;= -1 THEN 1 ELSE 0 END) as Karma,ISNULL(Flags.FlagValue,0) AS Remort FROM ( SELECT row_number() OVER (ORDER BY dbo.PlayingCharacters.XP desc) AS CI_offset_row_number, dbo.PlayingCharacters.PlayerName, dbo.PlayingCharacters.CurrentLevel, dbo.PlayingCharacters.XP, (SELECT CASE WHEN dbo.PlayingCharacters.Karma &gt;= 1 THEN 2 WHEN dbo.PlayingCharacters.Karma &lt;= -1 THEN 1 ELSE 0 END) as Karma, ISNULL(Flags.FlagValue, 0) AS Remort FROM dbo.PlayingCharacters LEFT JOIN dbo.Flags ON dbo.Flags.OwnerID = dbo.PlayingCharacters.UserID AND Flags.FlagID = 30419 WHERE dbo.PlayingCharacters.AccountName NOT IN (SELECT DISTINCT AccountName FROM UserFlags WHERE FlagBitPosition BETWEEN 0 AND 40) AND PlayingCharacters.AccountName NOT LIKE 'DeletedFrom:%' ) AS A WHERE A.CI_offset_row_number BETWEEN (141) AND (150) </code></pre> <p>Upon examining the query, and a bit of further research I found that "AS A" near the end of the query may be the culprit.. But I am unsure. I am familiar with SQL to an extent, but with this it seems I am a fish out of water.. I cannot seem to figure out how to fix this query.</p> <p>I apologize if this may have been asked prior, I found a few results regarding my error but couldn't put together any combination of answers with any success. </p> <p>As a side note, this is my query before trying to add a limit/offset with <code>row_number()</code></p> <pre><code>SELECT TOP 30 dbo.PlayingCharacters.PlayerName, dbo.PlayingCharacters.CurrentLevel, dbo.PlayingCharacters.XP, (SELECT CASE WHEN dbo.PlayingCharacters.Karma &gt;= 1 THEN 2 WHEN dbo.PlayingCharacters.Karma &lt;= -1 THEN 1 ELSE 0 END) as Karma, ISNULL(Flags.FlagValue, 0) AS Remort FROM dbo.PlayingCharacters LEFT JOIN dbo.Flags ON dbo.Flags.OwnerID = dbo.PlayingCharacters.UserID AND Flags.FlagID = 30419 WHERE dbo.PlayingCharacters.AccountName NOT IN (SELECT DISTINCT AccountName FROM UserFlags WHERE FlagBitPosition BETWEEN 0 AND 40) AND PlayingCharacters.AccountName NOT LIKE 'DeletedFrom:%' ORDER BY dbo.PlayingCharacters.XP desc </code></pre> <p>Which works, but obviously doesn't perform the desired limit/offset. The code I am using to generate the query in question was grabbed from: <a href="http://codeigniter.com/forums/viewthread/160626/P10/#985759" rel="nofollow">http://codeigniter.com/forums/viewthread/160626/P10/#985759</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmg1075
    1. USZech Walden
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server row_number and datatables
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload