Awesome.
I just had to modify the outter query:
$SQL = "SELECT TOP {$max_results}
Value1, Value2
FROM my_table
WHERE credit_card_id NOT IN (
To read:
$SQL = "SELECT TOP {$max_results}
Value1, Value2
FROM my_table
WHERE my_table.column = 'P'
AND credit_card_id NOT IN (
And it works like a champ.
Thank you very much to everyone that helped me on this!
On 9/19/07, Jim Lucas <lists@xxxxxxxxx> wrote:
>
> Dan Shirah wrote:
> > Whenever the query has the NOT IN included in it, I get the following
> error:
> >
> > Warning: mssql_query()
> > [function.mssql-query<
> http://develop1/credit%20card%20processing/Process/function.mssql-query>]:
> > message: Incorrect syntax near the keyword 'as'. (severity 15)
> >
> >>
> >> <?php
> >>
> >> # Get page number if passed to script
> >> $page = intval( empty($_GET['page']) ? 1 : $_GET['page'] );
> >>
> >> # Set number of rows to display from result set
> >> $max_results = 10;
> >>
> >> # Calculate the number of results that we have already looked at
> >> $page_results = intval(($page*$max_results)-$max_results);
> >>
> >> # Set your ORDER BY criteria. This needs to be the same for each SQL
> >> statement
> >> # This is why I have setup a variable, so no mistakes will be made
> >> $order_by_criteria = 'credit_card_id';
> >>
> >> # Figure out which SQL statement we need to use
> >> if ( $page === 1 ) {
> >>
> >> # Query for page 1
> >> $SQL = "SELECT TOP {$max_results}
> >> Value1, Value2
> >> FROM my_table
> >> ORDER BY {$order_by_criteria}";
> >>
> >> } else {
> >>
> >> # Query for page 2 and greater
> >> $SQL = "SELECT TOP {$max_results}
> >> Value1, Value2
> >> FROM my_table
> >> WHERE credit_card_id NOT IN (
> >> SELECT TOP {$page_results}
> >> credit_card_id
> >> FROM my_table
> >> WHERE my_table.column = 'P'
> >> ORDER BY {$order_by_criteria}
> >> ) AS newtbl
>
> yup, sorry, missed that in the copy/paste remove the 'AS newtbl' part
>
> >> ORDER BY {$order_by_criteria}";
> >>
> >> }
> >>
> >> ... run your $SQL statement here and work with the results
> >> ?>
>
>
> --
> Jim Lucas
>
> "Some men are born to greatness, some achieve greatness,
> and some have greatness thrust upon them."
>
> Twelfth Night, Act II, Scene V
> by William Shakespeare
>
>
