问:
Postgres: Want to SELECT data when user exists, else INSERT user
Still getting the hang of PostgreSQL (Using PostgreSQL-12, so any advice is very much appreciated.

I have an application that allows the user to save and create playlists of songs.

My tables are: users, playlists, songs
Then I have two junction tables: user_playlist, playlist_song

I have a query that when given a userid will return all of the songs, from that user's first playlist (app defaults to first playlist on login), and it currently works how I want it to.

My next issue to tackle is that I want to now combine that query, with a second, that would add the userid to the user table if it didn't currently exist.

So the flow would be user logins in, then on login a userid is sent to my db.
In my database I want to check if that userid already exists, if it does I want the SELECT query that return songs to execute, but if that userid does not exist, I want to insert the userid into my user table

I've read about 10 StackOverflow solutions trying to tweak them to work for me, but haven't had any luck. I'm not sure if I should post all of the things I've already tried or not.

```<code>$$ BEGIN
      IF EXISTS (SELECT 1 FROM users WHERE userid = '1') THEN

        SELECT
          s.songid,
          s.title,
          s.artists,
          s.album,
          s.year,
          s.duration,
          s.url,
          ps.songOrder
        FROM
          playlist_song ps
        JOIN
          songs s
        ON
          ps.songid = s.songid
        JOIN
          playlists p
        ON
          ps.playlistid = p.playlistid
        WHERE
          p.playlistid = (
            SELECT
              p.playlistid
            FROM
              user_playlist up
            JOIN
              playlists p
            ON
              up.playlistid = p.playlistid
            JOIN
              users u
            ON
              up.userid = u.userid
            WHERE
              u.userid = '1'
            LIMIT 1
          )
        ORDER BY ps.songOrder;

    ELSE
      INSERT INTO users (userid, username) VALUES ('1', 'Haley')
        ON CONFLICT DO NOTHING;
    END IF;
END $$;
</code>```

This is the error it is currently giving me when I test it in PG admin:

```<code>ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement
SQL state: 42601
</code>```

So I then added RETURN to my SELECT and the error changed to

```<code>LINE 5:         RETURN (SELECT
                       ^
SQL state: 42804
Character: 98
</code>```

Adding RETURN QUERY gave the error was Cannot use return query in a non-setof function

Adding RETURN SETOF gave the error was RETURN cannot have a parameter in function returning void

I've been stuck on this for a day and a half and at this point and don't want to break it up into multiple calls to the db unless it's not possible (my pride needs to squash this bugger)

I can't tell you how much your help will be appreciated! Thanks in advance

EDIT: Here are my tables

```<code>CREATE TABLE IF NOT EXISTS users (
      userid TEXT NOT NULL,
      username TEXT NOT NULL,
        CONSTRAINT u_id_pk PRIMARY KEY (userid)
    );

    CREATE TABLE IF NOT EXISTS playlists (
      playlistid SERIAL NOT NULL,
      playlistname TEXT NOT NULL,
        CONSTRAINT p_id_pk PRIMARY KEY (playlistid)

    );

    CREATE TABLE IF NOT EXISTS songs (
      songid SERIAL NOT NULL,
      url TEXT,
      href TEXT,
      title TEXT,
      artists TEXT,
      album TEXT,
      year integer,
      duration integer,
        CONSTRAINT s_pk PRIMARY KEY (songid)
    );

    CREATE TABLE IF NOT EXISTS user_playlist (
      userid TEXT,
      playlistid INTEGER,
        CONSTRAINT u_up_fk FOREIGN KEY (userid) REFERENCES users(userid),
        CONSTRAINT p_up_fk FOREIGN KEY (playlistid) REFERENCES playlists(playlistid)
    );

    CREATE TABLE IF NOT EXISTS playlist_song (
      playlistid INTEGER,
      songid INTEGER,
      songOrder INTEGER,
        CONSTRAINT p_ps_fk FOREIGN KEY (playlistid) REFERENCES playlists(playlistid),
        CONSTRAINT s_ps_fk FOREIGN KEY (songid) REFERENCES songs(songid)
    );
</code>```

Next attempt:

```<code>CREATE TYPE songs_type AS (
          songid integer,
          title character varying(25),
          artists character varying(25),
          album character varying(25),
          year integer,
          duration integer,
          url character varying(25),
          songOrder integer
);

CREATE OR REPLACE FUNCTION func() RETURNS songs_type AS
$$
DECLARE results_record songs_type;
BEGIN
    IF EXISTS (SELECT 1 FROM auxium.users WHERE userid = '1') THEN

      SELECT
          s.songid,
          s.title,
          s.artists,
          s.album,
          s.year,
          s.duration,
          s.url,
          ps.songOrder

        INTO
          results_record.songid,
          results_record.title,
          results_record.artists,
          results_record.album,
          results_record.year,
          results_record.duration,
          results_record.url,
          results_record.songOrder
        FROM
          auxium.playlist_song ps
        JOIN
          auxium.songs s
        ON
          ps.songid = s.songid
        JOIN
          auxium.playlists p
        ON
          ps.playlistid = p.playlistid
        WHERE
          p.playlistid = (
            SELECT
              p.playlistid
            FROM
              auxium.user_playlist up
            JOIN
              auxium.playlists p
            ON
              up.playlistid = p.playlistid
            JOIN
              auxium.users u
            ON
              up.userid = u.userid
            WHERE
              u.userid = '1'
            LIMIT 1
          )
        ORDER BY ps.songOrder;

    ELSE
      INSERT INTO auxium.users (userid, username) VALUES ('1', 'Haley')
        ON CONFLICT DO NOTHING;
    END IF;
    return results_record;
END;
$$

LANGUAGE plpgsql;
</code>```

答:
Create type and then return it.

I have created users(userid,username) table and then created type with same schema as i am selecting the same. You will have to change it to the columns you are selecting(while creating TYPE).

It is working fine.

```<code>CREATE TYPE users_type AS (userid integer,username character varying(10));

CREATE OR REPLACE FUNCTION fun() RETURNS users_type AS
$$
DECLARE result_record users_type;
BEGIN
    IF EXISTS (SELECT 1 FROM users WHERE userid = 1) THEN
         SELECT userid,username into result_record.userid,result_record.username from users;
    ELSE
      INSERT INTO users (userid, username) VALUES (1, 'Haley');
    END IF;
    return result_record;
END;
$$

LANGUAGE plpgsql;
</code>```

Body:
Still getting the hang of PostgreSQL (Using PostgreSQL-12, so any advice is very much appreciated.

I have an application that allows the user to save and create playlists of songs.

My tables are: users, playlists, songs
Then I have two junction tables: user_playlist, playlist_song

I have a query that when given a userid will return all of the songs, from that user's first playlist (app defaults to first playlist on login), and it currently works how I want it to.

My next issue to tackle is that I want to now combine that query, with a second, that would add the userid to the user table if it didn't currently exist.

So the flow would be user logins in, then on login a userid is sent to my db.
In my database I want to check if that userid already exists, if it does I want the SELECT query that return songs to execute, but if that userid does not exist, I want to insert the userid into my user table

I've read about 10 StackOverflow solutions trying to tweak them to work for me, but haven't had any luck. I'm not sure if I should post all of the things I've already tried or not.

$$ BEGIN
      IF EXISTS (SELECT 1 FROM users WHERE userid = '1') THEN

        SELECT
          s.songid,
          s.title,
          s.artists,
          s.album,
          s.year,
          s.duration,
          s.url,
          ps.songOrder
        FROM
          playlist_song ps
        JOIN
          songs s
        ON
          ps.songid = s.songid
        JOIN
          playlists p
        ON
          ps.playlistid = p.playlistid
        WHERE
          p.playlistid = (
            SELECT
              p.playlistid
            FROM
              user_playlist up
            JOIN
              playlists p
            ON
              up.playlistid = p.playlistid
            JOIN
              users u
            ON
              up.userid = u.userid
            WHERE
              u.userid = '1'
            LIMIT 1
          )
        ORDER BY ps.songOrder;

    ELSE
      INSERT INTO users (userid, username) VALUES ('1', 'Haley')
        ON CONFLICT DO NOTHING;
    END IF;
END $$;

This is the error it is currently giving me when I test it in PG admin:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement
SQL state: 42601

So I then added RETURN to my SELECT and the error changed to

LINE 5:         RETURN (SELECT
                       ^
SQL state: 42804
Character: 98

Adding RETURN QUERY gave the error was Cannot use return query in a non-setof function

Adding RETURN SETOF gave the error was RETURN cannot have a parameter in function returning void

I've been stuck on this for a day and a half and at this point and don't want to break it up into multiple calls to the db unless it's not possible (my pride needs to squash this bugger)

I can't tell you how much your help will be appreciated! Thanks in advance

EDIT: Here are my tables

CREATE TABLE IF NOT EXISTS users (
      userid TEXT NOT NULL,
      username TEXT NOT NULL,
        CONSTRAINT u_id_pk PRIMARY KEY (userid)
    );

    CREATE TABLE IF NOT EXISTS playlists (
      playlistid SERIAL NOT NULL,
      playlistname TEXT NOT NULL,
        CONSTRAINT p_id_pk PRIMARY KEY (playlistid)

    );

    CREATE TABLE IF NOT EXISTS songs (
      songid SERIAL NOT NULL,
      url TEXT,
      href TEXT,
      title TEXT,
      artists TEXT,
      album TEXT,
      year integer,
      duration integer,
        CONSTRAINT s_pk PRIMARY KEY (songid)
    );

    CREATE TABLE IF NOT EXISTS user_playlist (
      userid TEXT,
      playlistid INTEGER,
        CONSTRAINT u_up_fk FOREIGN KEY (userid) REFERENCES users(userid),
        CONSTRAINT p_up_fk FOREIGN KEY (playlistid) REFERENCES playlists(playlistid)
    );

    CREATE TABLE IF NOT EXISTS playlist_song (
      playlistid INTEGER,
      songid INTEGER,
      songOrder INTEGER,
        CONSTRAINT p_ps_fk FOREIGN KEY (playlistid) REFERENCES playlists(playlistid),
        CONSTRAINT s_ps_fk FOREIGN KEY (songid) REFERENCES songs(songid)
    );

Next attempt:

CREATE TYPE songs_type AS (
          songid integer,
          title character varying(25),
          artists character varying(25),
          album character varying(25),
          year integer,
          duration integer,
          url character varying(25),
          songOrder integer
);

CREATE OR REPLACE FUNCTION func() RETURNS songs_type AS
$$
DECLARE results_record songs_type;
BEGIN
    IF EXISTS (SELECT 1 FROM auxium.users WHERE userid = '1') THEN

      SELECT
          s.songid,
          s.title,
          s.artists,
          s.album,
          s.year,
          s.duration,
          s.url,
          ps.songOrder

        INTO
          results_record.songid,
          results_record.title,
          results_record.artists,
          results_record.album,
          results_record.year,
          results_record.duration,
          results_record.url,
          results_record.songOrder
        FROM
          auxium.playlist_song ps
        JOIN
          auxium.songs s
        ON
          ps.songid = s.songid
        JOIN
          auxium.playlists p
        ON
          ps.playlistid = p.playlistid
        WHERE
          p.playlistid = (
            SELECT
              p.playlistid
            FROM
              auxium.user_playlist up
            JOIN
              auxium.playlists p
            ON
              up.playlistid = p.playlistid
            JOIN
              auxium.users u
            ON
              up.userid = u.userid
            WHERE
              u.userid = '1'
            LIMIT 1
          )
        ORDER BY ps.songOrder;

    ELSE
      INSERT INTO auxium.users (userid, username) VALUES ('1', 'Haley')
        ON CONFLICT DO NOTHING;
    END IF;
    return results_record;
END;
$$

LANGUAGE plpgsql;

Body:
Create type and then return it.

I have created users(userid,username) table and then created type with same schema as i am selecting the same. You will have to change it to the columns you are selecting(while creating TYPE).

It is working fine.

CREATE TYPE users_type AS (userid integer,username character varying(10));

CREATE OR REPLACE FUNCTION fun() RETURNS users_type AS
$$
DECLARE result_record users_type;
BEGIN
    IF EXISTS (SELECT 1 FROM users WHERE userid = 1) THEN
         SELECT userid,username into result_record.userid,result_record.username from users;
    ELSE
      INSERT INTO users (userid, username) VALUES (1, 'Haley');
    END IF;
    return result_record;
END;
$$

LANGUAGE plpgsql;


来源: stackexchange

问:
On select change get select data-id attribute value
I am trying this Code That is my select value

```<code><select class="select2 lang_change">
                            <option selected value="english" data-id="" <?php
                            if (isset($_SESSION['lang'])) {
                                set_selected($_SESSION['lang'], 'english');
                            }
                            ?> >English</option>
                            <!--<option value="2">Chinese</option>-->
                            <option value="spanish" data-id="es" <?php
                            if (isset($_SESSION['lang'])) {
                                set_selected($_SESSION['lang'], 'spanish');
                            }
                            ?>>Spanish</option>
                            <!--<option value="4">Hindi</option>-->
                            <!--<option value="5">Filipino</option>-->
                        </select>
</code>```

That is my Jquery Or ajax value

```<code>$('.lang_change').on('change', function () {
 var dataid = $(".lang_change option:selected").attr('data-id');
 var val = this.value;
            $.ajax({
                url: "<?php echo base_url('lang_change/'); ?>" + "?"+val+"&"+dataid,
                type: "GET",
                cache: false,
                dataType: "JSON",
                success: function (dataResult) {
                    if (dataResult.statusCode == 200)
                    {
                        location.reload();
                    }
                }
            });
        });
    </script>
</code>```

That is my code how can i get onchange get select data-id attribute value
Help

答:
I have corrected your script, and its getting data-id.

$('.lang_change').on('change', function() {
  var dataid = $(".lang_change option:selected").attr('data-id');
  alert(dataid)
  var val = this.value;
  $.ajax({
    url: "<?php echo base_url('lang_change/'); ?>" + val,
    type: "GET",
    cache: false,
    dataType: "JSON",
    success: function(dataResult) {
      if (dataResult.statusCode == 200) {
        location.reload();
      }
    }
  });
});```
<pre class="snippet-code-html lang-html prettyprint-override"><code><script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<select class="select2 lang_change">
  <option selected value="english" data-id="es" <?php if (isset($_SESSION[ 'lang'])) { set_selected($_SESSION[ 'lang'], 'english'); } ?> >English</option>
  <!--<option value="2">Chinese</option>-->
  <option value="spanish" data-id="spanish" <?php if (isset($_SESSION[ 'lang'])) { set_selected($_SESSION[ 'lang'], 'spanish'); } ?>>Spanish</option>
  <!--<option value="4">Hindi</option>-->
  <!--<option value="5">Filipino</option>-->
</select></code>```

ContentLicense: CC BY-SA 4.0

Body:
I am trying this Code That is my select value

<select class="select2 lang_change">
                            <option selected value="english" data-id="" <?php
                            if (isset($_SESSION['lang'])) {
                                set_selected($_SESSION['lang'], 'english');
                            }
                            ?> >English</option>

                            <option value="spanish" data-id="es" <?php
                            if (isset($_SESSION['lang'])) {
                                set_selected($_SESSION['lang'], 'spanish');
                            }
                            ?>>Spanish</option>

                        </select>

That is my Jquery Or ajax value

$('.lang_change').on('change', function () {
 var dataid = $(".lang_change option:selected").attr('data-id');
 var val = this.value;
            $.ajax({
                url: "<?php echo base_url('lang_change/'); ?>" + "?"+val+"&"+dataid,
                type: "GET",
                cache: false,
                dataType: "JSON",
                success: function (dataResult) {
                    if (dataResult.statusCode == 200)
                    {
                        location.reload();
                    }
                }
            });
        });
    </script>

That is my code how can i get onchange get select data-id attribute value
Help

Body:
I have corrected your script, and its getting data-id.

$('.lang_change').on('change', function() {
  var dataid = $(".lang_change option:selected").attr('data-id');
  alert(dataid)
  var val = this.value;
  $.ajax({
    url: "<?php echo base_url('lang_change/'); ?>" + val,
    type: "GET",
    cache: false,
    dataType: "JSON",
    success: function(dataResult) {
      if (dataResult.statusCode == 200) {
        location.reload();
      }
    }
  });
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<select class="select2 lang_change">
  <option selected value="english" data-id="es" <?php if (isset($_SESSION[ 'lang'])) { set_selected($_SESSION[ 'lang'], 'english'); } ?> >English</option>
  <!--<option value="2">Chinese</option>-->
  <option value="spanish" data-id="spanish" <?php if (isset($_SESSION[ 'lang'])) { set_selected($_SESSION[ 'lang'], 'spanish'); } ?>>Spanish</option>
  <!--<option value="4">Hindi</option>-->
  <!--<option value="5">Filipino</option>-->
</select>


问:
How to verify that a Type can be passed to a generic method in C# also restore generic parameter types based on passed arguments
Let me try to explain what I mean. For example I have the following code:

```<code>public static class MyClass
{
   public static void DoSmthWithCollection<T>(IEnumerable<T> collection)
   {
       ...
   }
}
</code>```

Of course, compiler would allow to pass to it an object of List<int> type. Moreover, the compiler will figure out that the type T for the method is resolved to int type based on the argument type.

I need to do the same only dynamically, either by using System.Reflection or Roslyn. Say I get the MethodInfo for the method, I get the generic ParameterInfo. Is there any easy way to figure out that an object of type typeof(List<int>) actually fits to be an argument to the method and that when you pass it, the method resolves T to int type? And I am talking about some relatively easy solution without examining types and subtypes and the generic type constraints. I think at least Roslyn should have one, since this is what the C# compiler is doing.

Thanks for your help.

答:
I wouldn't call it simple, but I've written something that seems to work.

        private IEnumerable<Type> BaseTypes(Type t)
        {
            do
            {
                yield return t;
                t = t.BaseType;
            } while (t != null);
        }

            var method = new Action<IEnumerable<object>>(MyClass.DoSmthWithCollection).Method.GetGenericMethodDefinition();
            var testParms = new Type[] { typeof(List<int>) };

            var methodParms = method.GetParameters();
            var methodTypes = method.GetGenericArguments();
            var actualTypes = new Type[methodTypes.Length];

            for (var parmIndex = 0; parmIndex < methodParms.Length; parmIndex++)
            {
                var methodParmType = methodParms[parmIndex].ParameterType;
                if (!methodParmType.ContainsGenericParameters)
                    continue;

                var methodParmBaseType = methodParmType.GetGenericTypeDefinition();
                var testParmType = testParms[parmIndex];
                IEnumerable<Type> compareTypes = methodParmType.IsInterface ? testParmType.GetInterfaces() : BaseTypes(testParmType);
                var match = compareTypes.Where(i => i.IsGenericType && i.GetGenericTypeDefinition() == methodParmBaseType).Single();
                var testArgs = match.GetGenericArguments();
                var parmArgs = methodParmType.GetGenericArguments();
                for (var i = 0; i < parmArgs.Length; i++)
                {
                    if (parmArgs[i].IsGenericMethodParameter)
                    {
                        for(var idx = 0; idx < methodTypes.Length; idx++)
                            if (methodTypes[idx] == parmArgs[i])
                            {
                                actualTypes[idx] = testArgs[i];
                                break;
                            }
                    }
                }
            }

            var genericMethod = method.MakeGenericMethod(actualTypes);
```

I hope there's an easier way, but I haven't found one yet.

Body:
Let me try to explain what I mean. For example I have the following code:

public static class MyClass
{
   public static void DoSmthWithCollection<T>(IEnumerable<T> collection)
   {
       ...
   }
}

Of course, compiler would allow to pass to it an object of List<int> type. Moreover, the compiler will figure out that the type T for the method is resolved to int type based on the argument type.

I need to do the same only dynamically, either by using System.Reflection or Roslyn. Say I get the MethodInfo for the method, I get the generic ParameterInfo. Is there any easy way to figure out that an object of type typeof(List<int>) actually fits to be an argument to the method and that when you pass it, the method resolves T to int type? And I am talking about some relatively easy solution without examining types and subtypes and the generic type constraints. I think at least Roslyn should have one, since this is what the C# compiler is doing.

Thanks for your help.

Body:
I wouldn't call it simple, but I've written something that seems to work.

        private IEnumerable<Type> BaseTypes(Type t)
        {
            do
            {
                yield return t;
                t = t.BaseType;
            } while (t != null);
        }

            var method = new Action<IEnumerable<object>>(MyClass.DoSmthWithCollection).Method.GetGenericMethodDefinition();
            var testParms = new Type[] { typeof(List<int>) };

            var methodParms = method.GetParameters();
            var methodTypes = method.GetGenericArguments();
            var actualTypes = new Type[methodTypes.Length];

            for (var parmIndex = 0; parmIndex < methodParms.Length; parmIndex++)
            {
                var methodParmType = methodParms[parmIndex].ParameterType;
                if (!methodParmType.ContainsGenericParameters)
                    continue;

                var methodParmBaseType = methodParmType.GetGenericTypeDefinition();
                var testParmType = testParms[parmIndex];
                IEnumerable<Type> compareTypes = methodParmType.IsInterface ? testParmType.GetInterfaces() : BaseTypes(testParmType);
                var match = compareTypes.Where(i => i.IsGenericType && i.GetGenericTypeDefinition() == methodParmBaseType).Single();
                var testArgs = match.GetGenericArguments();
                var parmArgs = methodParmType.GetGenericArguments();
                for (var i = 0; i < parmArgs.Length; i++)
                {
                    if (parmArgs[i].IsGenericMethodParameter)
                    {
                        for(var idx = 0; idx < methodTypes.Length; idx++)
                            if (methodTypes[idx] == parmArgs[i])
                            {
                                actualTypes[idx] = testArgs[i];
                                break;
                            }
                    }
                }
            }

            var genericMethod = method.MakeGenericMethod(actualTypes);

I hope there's an easier way, but I haven't found one yet.


问:
Accepting request from specific ip in Spring Boot
I referred this tutorial to restrict certain ip in jboss and Spring Boot application.
http://www.mastertheboss.com/jboss-web/jbosswebserver/how-to-restrict-access-to-jboss-web-application-by-ip-or-host

But the tutorial is not complete. Where should I place those codes? Are there any other methods to do that?

If any one know any other methods please mention that here. Thanks in advance.

答:
I'd approach this problem in the following way:

If the IP where connections are accepted is subject to change, I'd configure reverse proxy like nginx. This would mean you don't have to redeploy or restart your application if the IP address changes. Please see this guide for details on how to configure nginx: https://docs.nginx.com/nginx/admin-guide/security-controls/controlling-access-proxied-tcp/

Otherwise I'd refer to this article: https://access.redhat.com/solutions/18412 describing the following configuration in WEB-INF/jboss-web.xml:

```<code>   <valve>
      <class-name>org.apache.catalina.valves.RemoteAddrValve</class-name>
      <param>
          <param-name>allow</param-name>
          <param-value>127.0.0.1,127.0.0.2</param-value>
      </param>
   </valve>
</jboss-web>
</code>```

Body:
I'd approach this problem in the following way:

If the IP where connections are accepted is subject to change, I'd configure reverse proxy like nginx. This would mean you don't have to redeploy or restart your application if the IP address changes. Please see this guide for details on how to configure nginx: https://docs.nginx.com/nginx/admin-guide/security-controls/controlling-access-proxied-tcp/

Otherwise I'd refer to this article: https://access.redhat.com/solutions/18412 describing the following configuration in WEB-INF/jboss-web.xml:

   <valve>
      <class-name>org.apache.catalina.valves.RemoteAddrValve</class-name>
      <param>
          <param-name>allow</param-name>
          <param-value>127.0.0.1,127.0.0.2</param-value>
      </param>
   </valve>
</jboss-web>


问:
How do I insert a new TR into the MIDDLE of a HTML table using JQuery?
I know how to append a new row to a table using JQuery:

```<code>var newRow = $("<tr>..."</tr>");
$("#mytable tbody").append(newRow);
</code>```

The question is how do I create a new row that precedes some existing row.

答:
Rather than this:

```<code>$("#mytable tbody").append(newRow);
</code>```

you are going to want to do something like this:

```<code>$("#id_of_existing_row").after(newRow);
</code>```

OwnerDisplayName: Decker

ContentLicense: CC BY-SA 2.5

Body:
I know how to append a new row to a table using JQuery:

var newRow = $("
..."
");
$("#mytable tbody").append(newRow);

The question is how do I create a new row that precedes some existing row.

Body:
Rather than this:

$("#mytable tbody").append(newRow);

you are going to want to do something like this:

$("#id_of_existing_row").after(newRow);

OwnerDisplayName: Andrew Hare


问:
What do you consider good API documentation?
I have always liked the documentation on Java APIs, generally speaking, but I know some people consider them lacking. So I'm wondering, what do you consider a good example of API documentation?

Please, include a link or an actual example in any answer. I want to have references that I (and others, of course) can use to improve our own documents.

答:
Most things about good documentation have already been mentioned, but I think there is one aspect about the JavaDoc way of API documentation that is lacking: making it easy to distinguish between the usage scenarios of all the different classes and interfaces, especially distinguishing between classes that should be used by a library client and those that should not.

Often, JavaDoc is pretty much all you get and usually there is no package documentation page. One is then confronted with a list of hundreds or even more of classes: where and how to start? What are typical ways of using the library?

It would be good if there were conventions of how to make it easy to provide this information as part of JavaDoc. Then the generated API documentation could allow for different views for different groups of people -- at a minimum two groups: those who implement the library and those who use it.

ContentLicense: CC BY-SA 3.0


问:
Find values from list of map using streams
```<code>List<Map<String, Object>> sqlResult = new ArrayList<>();
</code>```

The list of map above contains following records:

```<code>1. facilityId="1", shipmentMethod="NEXT_DAY"

2. facilityId="2", shipmentMethod="STANDARD"
</code>```

I want to find shipmentMethod where facilityId is "1" using java streams.

Also, I want to fetch only one record.

答:
You can simply use filter:

```<code>Object shipmentMethods = sqlResult.stream() // stream maps
    .filter(m -> m.get("facilityId").equals("1")) // filter only those, that have facilityId = "1"
    .map(m -> m.get("shipmentMethod")) // get only shipmentMethod
    .findAny().orElse(/* default value */); // get only one value
</code>```

Body:
List<Map<String, Object>> sqlResult = new ArrayList<>();

The list of map above contains following records:

1. facilityId="1", shipmentMethod="NEXT_DAY"

2. facilityId="2", shipmentMethod="STANDARD"

I want to find shipmentMethod where facilityId is "1" using java streams.

Also, I want to fetch only one record.

Body:
You can simply use filter:

Object shipmentMethods = sqlResult.stream() // stream maps
    .filter(m -> m.get("facilityId").equals("1")) // filter only those, that have facilityId = "1"
    .map(m -> m.get("shipmentMethod")) // get only shipmentMethod
    .findAny().orElse(/* default value */); // get only one value


问:
How to use tumbling window to group XML elements by content?
How do I group based on whether there's a match to [0-9] for digits with a tumbling window?

desired output:

```<code>...
<record>
    <name>joe</name>
    <data>phone1</data>
    <data>phone2</data>
</record>
...
</code>```

current output, not grouped:

```<code><xml>
  <record>
    <person key="$s" data="name">phone1</person>
  </record>
  <record>
    <person key="$s" data="name">phone2</person>
  </record>
  <record>
    <person key="$s" data="name">phone3sue</person>
  </record>
  <record>
    <person key="$s" data="name">cell4</person>
  </record>
  <record>
    <person key="$s" data="name">home5alice</person>
  </record>
  <record>
    <person key="$s" data="name">atrib6</person>
  </record>
  <record>
    <person key="$s" data="name">x7</person>
  </record>
  <record>
    <person key="$s" data="name">y9</person>
  </record>
  <record>
    <person key="$s" data="name">z10</person>
  </record>
</xml>
</code>```

input:

```<code><text>
  <line>people</line>
  <line>joe</line>
  <line>phone1</line>
  <line>phone2</line>
  <line>phone3</line>
  <line>sue</line>
  <line>cell4</line>
  <line>home5</line>
  <line>alice</line>
  <line>atrib6</line>
  <line>x7</line>
  <line>y9</line>
  <line>z10</line>
</text>
</code>```

The notion is that each "person" will have a name (no digits) and perhaps additional data.  So looking to read in each line and then group based on where the names are found.

code:

```<code>xquery version "3.0";

<xml>
{
for tumbling window $line in db:open("foo.txt")//text()
start $s when matches($s, '[0-9]')
return
<record>

       <person key='$s' data="name">{$line}</person>

 </record>
}
 </xml>
</code>```

Looking at the output, "phone3sue" is obviously doing some matching and grouping, although not exactly as desired because "phone3" should be in its own element, nested within "joe" rather than "sue".  But, still, there's some matching happening there.

from the saxon mailing list:

On Wed, Feb 19, 2020 at 10:31:37AM -0800, thufir scripsit:

I'll re-read the section on windowing; my impression was that it was more
    for display or report purposes.

Windowing is how you take chunks out of a stream of data.

What you've got is effectively a stream of line elements; you can
  identify the "name" lines, but you don't now how far part they are/how
  much data is between any particular pair of names.

Windows lets you say "I want the chunk of this stream that starts with
  a name line and continues up to (but not including) the next name
  line".

Would you elaborate on what you mean by two steps, a bit more concretely?

You're trying to take some input XML and turn it into different output
  XML.

If this is pure transformation -- change all of the elements named FOO
  to element named BAZ -- XQuery's not the best tool choice. Use XSLT if
  you can.  They're computationally the same but the languages have
  different biases and XSLT does transforms more naturally.

If the output XML is a representation of an abstraction of your input
  -- morally some sort of report -- it helps a lot to have the abstraction, and then present it.

So in your case, what you have is a stream containing an implicit
  association between names and data.  (It's a stream of lines; the only
  way you know these data lines go with that name line is position.  So
  implicit.)  If you turn that into an explicit mapping between names
  and data -- such as by creating a map variable where the keys are the
  contents of the name line (with spaces handled somehow) and the
  entries for each key are the data lines associated with that name --
  you have done the abstraction part.

You can then take that map and produce the XML output you want from
  it, which is much simpler than trying to combine the "create new XML"
  and "do the abstraction steps".  The last thing I posted has an
  example of turning a map into elements, but as a pattern it's just

map:keys($map) ! {.}{$map(.)}

(it gets more complicated if you've got nodes or a sequence in the
  entry, but not much more.)

That make something a little closer to sense?

-- Graydon

_______________________________________________ saxon-help mailing list archived at http://saxon.markmail.org/
  saxon-help@lists.sourceforge.net
  https://lists.sourceforge.net/lists/listinfo/saxon-help

答:
This is probably quite close:

```<code>thufir@dur:~/flwor/foo.txt.database$
thufir@dur:~/flwor/foo.txt.database$ basex dennis.xq
<person name="joe">
  <person id="3" x="0" numerical="true">phone1</person>
  <person id="4" x="0" numerical="true">phone2</person>
  <person id="5" x="0" numerical="true">phone3</person>
</person>
<person name="sue">
  <person id="7" x="0" numerical="true">cell4</person>
  <person id="8" x="0" numerical="true">home5</person>
</person>
<person name="alice">
  <person id="10" x="0" numerical="true">atrib6</person>
  <person id="11" x="0" numerical="true">x7</person>
  <person id="12" x="0" numerical="true">y9</person>
  <person id="13" x="0" numerical="true">z10</person>
</person>thufir@dur:~/flwor/foo.txt.database$
</code>```

code:

```<code>xquery version "3.0";

declare variable $XML := <xml>
 <person id="1" x="0" numerical="false">people</person>
 <person id="2" x="0" numerical="false">joe</person>
 <person id="3" x="0" numerical="true">phone1</person>
 <person id="4" x="0" numerical="true">phone2</person>
 <person id="5" x="0" numerical="true">phone3</person>
 <person id="6" x="0" numerical="false">sue</person>
 <person id="7" x="0" numerical="true">cell4</person>
 <person id="8" x="0" numerical="true">home5</person>
 <person id="9" x="0" numerical="false">alice</person>
 <person id="10" x="0" numerical="true">atrib6</person>
 <person id="11" x="0" numerical="true">x7</person>
 <person id="12" x="0" numerical="true">y9</person>
 <person id="13" x="0" numerical="true">z10</person>
</xml> ;

for $P in $XML/person
where $P[@numerical="true"]
let $PREV := $P/preceding-sibling::person[@numerical="false"][1]
group by $PREV
return <person name="{$PREV}" >  { $P } </person>
</code>```

Body:
How do I group based on whether there's a match to [0-9] for digits with a tumbling window?

desired output:

...
<record>
    <name>joe</name>
    <data>phone1</data>
    <data>phone2</data>
</record>
...

current output, not grouped:

<xml>
  <record>
    <person key="$s" data="name">phone1</person>
  </record>
  <record>
    <person key="$s" data="name">phone2</person>
  </record>
  <record>
    <person key="$s" data="name">phone3sue</person>
  </record>
  <record>
    <person key="$s" data="name">cell4</person>
  </record>
  <record>
    <person key="$s" data="name">home5alice</person>
  </record>
  <record>
    <person key="$s" data="name">atrib6</person>
  </record>
  <record>
    <person key="$s" data="name">x7</person>
  </record>
  <record>
    <person key="$s" data="name">y9</person>
  </record>
  <record>
    <person key="$s" data="name">z10</person>
  </record>
</xml>

input:

<text>
  <line>people</line>
  <line>joe</line>
  <line>phone1</line>
  <line>phone2</line>
  <line>phone3</line>
  <line>sue</line>
  <line>cell4</line>
  <line>home5</line>
  <line>alice</line>
  <line>atrib6</line>
  <line>x7</line>
  <line>y9</line>
  <line>z10</line>
</text>

The notion is that each "person" will have a name (no digits) and perhaps additional data.  So looking to read in each line and then group based on where the names are found.

code:

xquery version "3.0";

<xml>
{
for tumbling window $line in db:open("foo.txt")//text()
start $s when matches($s, '[0-9]')
return
<record>

       <person key='$s' data="name">{$line}</person>

 </record>
}
 </xml>

Looking at the output, "phone3sue" is obviously doing some matching and grouping, although not exactly as desired because "phone3" should be in its own element, nested within "joe" rather than "sue".  But, still, there's some matching happening there.

from the saxon mailing list:

On Wed, Feb 19, 2020 at 10:31:37AM -0800, thufir scripsit:

I'll re-read the section on windowing; my impression was that it was more
    for display or report purposes.

Windowing is how you take chunks out of a stream of data.

What you've got is effectively a stream of line elements; you can
  identify the "name" lines, but you don't now how far part they are/how
  much data is between any particular pair of names.

Windows lets you say "I want the chunk of this stream that starts with
  a name line and continues up to (but not including) the next name
  line".

Would you elaborate on what you mean by two steps, a bit more concretely?

You're trying to take some input XML and turn it into different output
  XML.

If this is pure transformation -- change all of the elements named FOO
  to element named BAZ -- XQuery's not the best tool choice. Use XSLT if
  you can.  They're computationally the same but the languages have
  different biases and XSLT does transforms more naturally.

If the output XML is a representation of an abstraction of your input
  -- morally some sort of report -- it helps a lot to have the abstraction, and then present it.

So in your case, what you have is a stream containing an implicit
  association between names and data.  (It's a stream of lines; the only
  way you know these data lines go with that name line is position.  So
  implicit.)  If you turn that into an explicit mapping between names
  and data -- such as by creating a map variable where the keys are the
  contents of the name line (with spaces handled somehow) and the
  entries for each key are the data lines associated with that name --
  you have done the abstraction part.

You can then take that map and produce the XML output you want from
  it, which is much simpler than trying to combine the "create new XML"
  and "do the abstraction steps".  The last thing I posted has an
  example of turning a map into elements, but as a pattern it's just

map:keys($map) ! {.}{$map(.)}

(it gets more complicated if you've got nodes or a sequence in the
  entry, but not much more.)

That make something a little closer to sense?

-- Graydon

_______________________________________________ saxon-help mailing list archived at http://saxon.markmail.org/
  saxon-help@lists.sourceforge.net
  https://lists.sourceforge.net/lists/listinfo/saxon-help

Body:
This is probably quite close:

thufir@dur:~/flwor/foo.txt.database$
thufir@dur:~/flwor/foo.txt.database$ basex dennis.xq
<person name="joe">
  <person id="3" x="0" numerical="true">phone1</person>
  <person id="4" x="0" numerical="true">phone2</person>
  <person id="5" x="0" numerical="true">phone3</person>
</person>
<person name="sue">
  <person id="7" x="0" numerical="true">cell4</person>
  <person id="8" x="0" numerical="true">home5</person>
</person>
<person name="alice">
  <person id="10" x="0" numerical="true">atrib6</person>
  <person id="11" x="0" numerical="true">x7</person>
  <person id="12" x="0" numerical="true">y9</person>
  <person id="13" x="0" numerical="true">z10</person>
</person>thufir@dur:~/flwor/foo.txt.database$

code:

xquery version "3.0";

declare variable $XML := <xml>
 <person id="1" x="0" numerical="false">people</person>
 <person id="2" x="0" numerical="false">joe</person>
 <person id="3" x="0" numerical="true">phone1</person>
 <person id="4" x="0" numerical="true">phone2</person>
 <person id="5" x="0" numerical="true">phone3</person>
 <person id="6" x="0" numerical="false">sue</person>
 <person id="7" x="0" numerical="true">cell4</person>
 <person id="8" x="0" numerical="true">home5</person>
 <person id="9" x="0" numerical="false">alice</person>
 <person id="10" x="0" numerical="true">atrib6</person>
 <person id="11" x="0" numerical="true">x7</person>
 <person id="12" x="0" numerical="true">y9</person>
 <person id="13" x="0" numerical="true">z10</person>
</xml> ;

for $P in $XML/person
where $P[@numerical="true"]
let $PREV := $P/preceding-sibling::person[@numerical="false"][1]
group by $PREV
return <person name="{$PREV}" >  { $P } </person>


问:
How to print date in the format of mm/dd/yyyy in VB
I need to print the date in the format of mm/dd/yyyy.
if the date is 4/24/2009 it should print the date as 04/24/2009.
that is zero padding is also needed..
I used date function to get the current date...but the date is getting in the format of m/dd/yyyy...</p>

答:
You can try like this also depending upon your requirement</p>

```<code>Dim strDate As String
Dim strDate1() As String
strDate = FormatDateTime(Now, vbGeneralDate)

If InStr(strDate, " ") > 0 Then
    strDate1 = Split(strDate, " ")
    Dim datDate1 As Date
    If Month(strDate1(0)) < 10 Then
        txtDate.Text = "0" + strDate1(0)
    Else
        txtDate.Text = strDate1(0)
    End If

Else

End If
</code>```

Body:
You can try like this also depending upon your requirement

Dim strDate As String
Dim strDate1() As String
strDate = FormatDateTime(Now, vbGeneralDate)

If InStr(strDate, " ") > 0 Then
    strDate1 = Split(strDate, " ")
    Dim datDate1 As Date
    If Month(strDate1(0)) < 10 Then
        txtDate.Text = "0" + strDate1(0)
    Else
        txtDate.Text = strDate1(0)
    End If

Else

End If


问:
What interface type is suited for implementing strategy with different signature?
consider the following game code:

public class Player : MonoBehaviour {
    public void UseItem(Item item) {
        item.Use(this);
    }
    public void GetDrunk() {}
}

public class Item {
    public WhatInterface[] itemUsages;

    public void Use(Player player) {
        foreach(var usage in itemUsages) {
            usage.Execute(new ItemUsageArgs {itemUser = player, itemUsed = this})
        }
    }
}

public class GameManager : MonoBehaviour {

    public Player mainCharacter;
    public Item beer = new Item {itemUsages = new [] {
        new TestConsole(),
        new DamageFromItem (),
        new DrunkFromITem ()
    }}

    private void Start() {
        mainCharacter.Use(beer);
    }
}

public class TestConsole : WhatInterface {
    public void Execute(BaseArgs args) {
        Debug.Log("function call executed");
    }
}

public class DamageFromItem : WhatInterface {
    public void Execute(ItemUsageArgs args) {
        Debug.Log(args.itemUser + " take damage from " + args.itemUsed);
    }
}

public class DrunkFromITem : WhatInterface {
    public void Execute(ItemUsageArgs args) {
        args.itemUser.GetDrunk();
    }
}

public class BaseArgs {}

public class ItemUsageArgs : BaseArgs {
   public Player itemUser;
   public Item itemUsed;
}
```

<p>so how to create interface type code that is suited for itemUsages?</p>

<p>Or do I wrongly create the design for this context?</p>

<p>Basically I'm trying strategy pattern so that item usages could be vary for every kind of item.</p>

<p>Things I tried, creating IItemUsage interface:</p>

```public interface IItemUsage {
    void Execute(ItemUsageArgs args);
    // but then anything that needs to implement this interface must use this method, even though it only needs BaseArgs.
    // TestConsole class must conform to Execute(ItemUsageArgs) signature..
}

public class TestConsole : IItemUsage {
    public void Execute(BaseArgs args) {
        Debug.Log("function call executed");
    }
    // this won't compile
}
```

答:
If you want to implement interface with some method, which has input arguments, that can be different types, you must define base argument class or use interface parameter instead.

For example:

```<code>public interface IItemUsage
{
    void Execute(IItemUsageArgs args);
}
public interface IItemUsageArgs
{
    //place public part of all ItemUsageArgs
}

public class ItemUsageArgs1 : IItemUsageArgs
{

}

public class ItemUsageArgs2 : IItemUsageArgs
{

}
public class ItemUsage1 :IItemUsage
{
    public void Execute(ItemUsageArgs1 args)
    {
        //do you need
    }
    void IItemUsage.Execute(IItemUsageArgs args)
    {
        Execute(args as ItemUsageArgs1);
    }
}

public class ItemUsage2 : IItemUsage
{
    public void Execute(ItemUsageArgs2 args)
    {
        //do you need
    }
    void IItemUsage.Execute(IItemUsageArgs args)
    {
        Execute(args as ItemUsageArgs2);
    }
}
</code>```

Body:
consider the following game code:

public class Player : MonoBehaviour {
    public void UseItem(Item item) {
        item.Use(this);
    }
    public void GetDrunk() {}
}

public class Item {
    public WhatInterface[] itemUsages;

    public void Use(Player player) {
        foreach(var usage in itemUsages) {
            usage.Execute(new ItemUsageArgs {itemUser = player, itemUsed = this})
        }
    }
}

public class GameManager : MonoBehaviour {

    public Player mainCharacter;
    public Item beer = new Item {itemUsages = new [] {
        new TestConsole(),
        new DamageFromItem (),
        new DrunkFromITem ()
    }}

    private void Start() {
        mainCharacter.Use(beer);
    }
}

public class TestConsole : WhatInterface {
    public void Execute(BaseArgs args) {
        Debug.Log("function call executed");
    }
}

public class DamageFromItem : WhatInterface {
    public void Execute(ItemUsageArgs args) {
        Debug.Log(args.itemUser + " take damage from " + args.itemUsed);
    }
}

public class DrunkFromITem : WhatInterface {
    public void Execute(ItemUsageArgs args) {
        args.itemUser.GetDrunk();
    }
}

public class BaseArgs {}

public class ItemUsageArgs : BaseArgs {
   public Player itemUser;
   public Item itemUsed;
}

so how to create interface type code that is suited for itemUsages?

Or do I wrongly create the design for this context?

Basically I'm trying strategy pattern so that item usages could be vary for every kind of item.

Things I tried, creating IItemUsage interface:

public interface IItemUsage {
    void Execute(ItemUsageArgs args);
    // but then anything that needs to implement this interface must use this method, even though it only needs BaseArgs.
    // TestConsole class must conform to Execute(ItemUsageArgs) signature..
}

public class TestConsole : IItemUsage {
    public void Execute(BaseArgs args) {
        Debug.Log("function call executed");
    }
    // this won't compile
}

Body:
If you want to implement interface with some method, which has input arguments, that can be different types, you must define base argument class or use interface parameter instead.

For example:

public interface IItemUsage
{
    void Execute(IItemUsageArgs args);
}
public interface IItemUsageArgs
{
    //place public part of all ItemUsageArgs
}

public class ItemUsageArgs1 : IItemUsageArgs
{

}

public class ItemUsageArgs2 : IItemUsageArgs
{

}
public class ItemUsage1 :IItemUsage
{
    public void Execute(ItemUsageArgs1 args)
    {
        //do you need
    }
    void IItemUsage.Execute(IItemUsageArgs args)
    {
        Execute(args as ItemUsageArgs1);
    }
}

public class ItemUsage2 : IItemUsage
{
    public void Execute(ItemUsageArgs2 args)
    {
        //do you need
    }
    void IItemUsage.Execute(IItemUsageArgs args)
    {
        Execute(args as ItemUsageArgs2);
    }
}


问:
How use image in api get by axios request in html tags in ReactJS?
I use axios to get data from the api, now I need to display the image in the td tag in HTML, the text response are working fine but the image is not displaying.

my request code is:

```<code>componentDidMount(){

      var headers = {
        "Content-Type":"application/json",
        "AccessToken":localStorage.TOKEN,
      }

      axios.get(window.$_APIurl+"myProviders/5e463d53e7179a2f011c4104", {headers: headers})
        .then((response) => {
          console.log(response.data[0].provider.image);
          const doctor = response.data[0].provider.image;
        if(response.data.length > 0)   this.setState({"listOfProviders": response.data});
      })
  }
</code>```

My html to use is:

```<code>  {this.state.listOfProviders.length > 0 ?
                      <tbody>
                        {this.state.listOfProviders.map((providerData,indx)=>
                          <tr>
                            <td className="font-weight-medium"> <img className="logo" src={doctor} alt="pam-logo" /> {providerData.provider.firstName} {providerData.provider.lastName} </td>
                            <td>{providerData.provider.email}</td>
                            <td>{providerData.provider.phoneNumber}</td>
                            <td>{(providerData.provider.specialities)}</td>
                            <td > {providerData.provider.qbid}</td>
                            <td> Mountain view,Ave </td>
                            <td><Icon icon={editIcon} width="20px"/>  <Icon icon={deleteIcon} width="20px"/></td>

                        </tr>
                        )}
                      </tbody>
</code>```

I need image in src tag.

答:
I don't know the use case here. But in my opinion, the variable declared in the componentDidMount() is not accessible in the render method. Just declare that variable as class variable in the constructor and then you can use it using this.doctor.

Or instead of declaring the variable you can just read it's value from the iterator. Like, providerData.provider.image

Body:
I use axios to get data from the api, now I need to display the image in the td tag in HTML, the text response are working fine but the image is not displaying.

my request code is:

componentDidMount(){

      var headers = {
        "Content-Type":"application/json",
        "AccessToken":localStorage.TOKEN,
      }

      axios.get(window.$_APIurl+"myProviders/5e463d53e7179a2f011c4104", {headers: headers})
        .then((response) => {
          console.log(response.data[0].provider.image);
          const doctor = response.data[0].provider.image;
        if(response.data.length > 0)   this.setState({"listOfProviders": response.data});
      })
  }

My html to use is:

  {this.state.listOfProviders.length > 0 ?

                        {this.state.listOfProviders.map((providerData,indx)=>

                              {providerData.provider.firstName} {providerData.provider.lastName}
                            {providerData.provider.email}
                            {providerData.provider.phoneNumber}
                            {(providerData.provider.specialities)}
                             {providerData.provider.qbid}
                             Mountain view,Ave
                            <Icon icon={editIcon} width="20px"/>  <Icon icon={deleteIcon} width="20px"/>

                        )}

I need image in src tag.


问:
Where is the MIT/GNU scheme reference on threads?
I've had a look here - reference. Also, looked at some other places but they turned out to be other scheme dialects, which means the functions there are not necessarily present in MIT/GNU scheme.

I obviously know that there must be thread related procedures since things like create-thread or current-thread or thread-dead works.
Can anyone point to the reference for threads. I'm using the Release 9.2 of MIT/GNU Scheme.

答:
There are not many volunteers who are working for mit-scheme, in general they are alumni of EECS of MIT, where they learned exactly how mit-scheme was implemented, and they are the only competent ones to understand it; this is why it is the best to ask at mit-scheme-devel@gnu.org and somebody will surely answer you some specific questions.  Apart from the documentation from src/compiler/documentation  and from here, there is no other documentation.

Using git log | less and searching for thread also returns nothing suggestive.

Here is the only available documentation and here is asked the same question.  He surely received a private answer.


问:
EXISTS clause ISSUE with TOP clause in sub query
I have a query sub query below (Purpose search top 2 orders from order table whose freight_charges=2 and get the customers of those orders)

Working query with IN clause

```<code>    SELECT *
    FROM Customers C
    WHERE C.CUST_ID IN (
      SELECT TOP 2 CUST_ID
      FROM Orders O
      where FREIGHT_CHARGES = 2
    )
</code>```

However I want to convert the IN clause into a more efficient EXISTS clause

Code not working with exists clause

```<code>    SELECT *
    FROM Customers C
    WHERE EXISTS (
        SELECT TOP 2 CUST_ID
        FROM Orders O
        where FREIGHT_CHARGES = 2 AND C.CUST_ID = O.CUST_ID
    )
</code>```

In case of the second query I am not retrieving the top 2 cust_id BUT all the records.

Please let me know any implementation to get the desired result.

Edited: Using INNER JOIN as suggested in the answers . I am able to get the correct result. However since I don't want to retrieve any record from Orders table I thought exists would be a better approach performance wise.

```<code>    SELECT C.*
    FROM Customers C
    INNER JOIN (
      SELECT TOP 2 CUST_ID
      FROM Orders
      where FREIGHT_CHARGES = 2
    ) O ON C.CUST_ID=O.CUST_ID
</code>```

答:
If you mix EXISTS with TOP there no difference than you use it without TOP, because EXISTS "Returns TRUE if a subquery contains any rows".

In your case, you can check queries:

```<code>SELECT *
FROM Customers C
  WHERE EXISTS (
    SELECT 1
    FROM Orders O
    JOIN Orders O2
      ON O.FREIGHT_CHARGES = O2.FREIGHT_CHARGES
      AND O.CUST_ID = O.CUST_ID
      AND O.ID <> O2.ID
    where O.FREIGHT_CHARGES = 2 AND C.CUST_ID = O.CUST_ID
   )
</code>```

Or

```<code>SELECT *
FROM Customers C
WHERE EXISTS (
    SELECT 1
    FROM Orders O
    where FREIGHT_CHARGES = 2 AND C.CUST_ID = O.CUST_ID
    GROUP BY FREIGHT_CHARGES, CUST_ID
    HAVING COUNT(1) > 1
)
</code>```

You must test which one is better in your case

Body:
I have a query sub query below (Purpose search top 2 orders from order table whose freight_charges=2 and get the customers of those orders)

Working query with IN clause

    SELECT *
    FROM Customers C
    WHERE C.CUST_ID IN (
      SELECT TOP 2 CUST_ID
      FROM Orders O
      where FREIGHT_CHARGES = 2
    )

However I want to convert the IN clause into a more efficient EXISTS clause

Code not working with exists clause

    SELECT *
    FROM Customers C
    WHERE EXISTS (
        SELECT TOP 2 CUST_ID
        FROM Orders O
        where FREIGHT_CHARGES = 2 AND C.CUST_ID = O.CUST_ID
    )

In case of the second query I am not retrieving the top 2 cust_id BUT all the records.

Please let me know any implementation to get the desired result.

Edited: Using INNER JOIN as suggested in the answers . I am able to get the correct result. However since I don't want to retrieve any record from Orders table I thought exists would be a better approach performance wise.

    SELECT C.*
    FROM Customers C
    INNER JOIN (
      SELECT TOP 2 CUST_ID
      FROM Orders
      where FREIGHT_CHARGES = 2
    ) O ON C.CUST_ID=O.CUST_ID

Body:
If you mix EXISTS with TOP there no difference than you use it without TOP, because EXISTS "Returns TRUE if a subquery contains any rows".

In your case, you can check queries:

SELECT *
FROM Customers C
  WHERE EXISTS (
    SELECT 1
    FROM Orders O
    JOIN Orders O2
      ON O.FREIGHT_CHARGES = O2.FREIGHT_CHARGES
      AND O.CUST_ID = O.CUST_ID
      AND O.ID <> O2.ID
    where O.FREIGHT_CHARGES = 2 AND C.CUST_ID = O.CUST_ID
   )

Or

SELECT *
FROM Customers C
WHERE EXISTS (
    SELECT 1
    FROM Orders O
    where FREIGHT_CHARGES = 2 AND C.CUST_ID = O.CUST_ID
    GROUP BY FREIGHT_CHARGES, CUST_ID
    HAVING COUNT(1) > 1
)

You must test which one is better in your case