In SQL Server, can we set if it rounds or truncates by default?

I have a large number of inserts and updates coded in my program that involves money, and I cam across a serious rounding bug. The majority of the time, I want to truncate the values, instead of the SQL Server default of rounding.

Rather than changing all my existing code, is there a way I can set this at a column level? Table level? Server level?

Edit: Sorry, the column type is a decimal(10, 2). Example: I want to enter 100.555 and either make it save as 100.55 or 100.56, as it currently rounds.

SQL 2012 locating duplicate column entries in a table

Hello I am using SQL 2012 and trying to identify rows where the SourceDataID column has two unique entries in the PartyCode column and having difficulties

SELECT PartyCode, SourceDataID, count (*) as CNT
FROM CustomerOrderLocation (nolock) 
GROUP BY PartyCode, SourceDataID
HAVING (Count(PartyCode)>1)
ORDER BY PartyCode

Results are returning as such:

W3333 948_O 31 (party code/sourcedataid/CNT)

This is showing me the total entries where the Partycode and the SourceDataID are listed together in the table; However I need it to show a count of any instances where W333 lists 948_O as the SourceDataID more than once.

I'm not having luck structuring the query to pull the results I am looking to get.

Mysql function that query a table and reconstruct a string from the query-result

I'm a newbie in mysql programming, and i would create a mysql function myFunction with a string parametre; that query a table and reconstruct a string from the query-result like this example :

myTable
 ---------------
|id   |  value   |
 ---------------
| id1 |value1    |
| id2 |value2    |
| id3 |value3    |
| id4 |value4    |
 ---------------

Calling this function is like this

myFunction('value2#value1#value4')

and must return

 'id2#id1#id4'

Thank you very much

SQL permissions based on record value (if column X = Y for user1 allow Inserts/Updates etc.)

I need to run queries as a "user" which is a record in a table, with permissions based on a record value of that user.

I have a database with a tUsers Table, such as:

ID    Username       Email         Role
1      Pieman       mail.com       Admin
2     Cakedude     mail.co.uk   Receptionist
3      Muffin        gh.com        Other

I need to have it so only "users"/records with "Role" of "Admin" can view and edit the table, and "Receptionist" view it etc.

I know of and use GRANT for permissions, but don't know how to run a query as a user based on a table record and to have the permission only GRANTED if that users' role is "Admin"

So if I have:

USE DB1;
GRANT SELECT ON OBJECT::tUsers TO Admins;
GO 
SELECT * FROM tUsers

How do I make that run as say tUser with ID 1, and the GRANT if the users' role = "Admin"

I'm sure I've seen this done before.

I'm fairly new and still learning the correct terminology, so if this is a duplicate question, or is essentially just describing an sql Function sorry.

Unable to create a constant value of type .... Only primitive types or enumeration types are supported in this context

The following database query is returning the following error.

I have a feeling it is to do with the contains function within the where clause but im not too sure?

Any ideas??

     var roomToMatch = db.rooms.Where(r=>r.roomNumber==number && r.buildingCode==code).First();
        var yearID = Convert.ToInt32(year);
        var semesterID = Convert.ToInt32(semester);
        var rounds = db.rounds.Where(r => r.semester == semesterID && r.year == yearID);

        int[] days = new int[5] { 1, 2, 3, 4, 5 };
        int[] times = new int[9] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
        List<request> bookings = new List<request>();
        var requests = db.requests.Include(r => r.module.staffs).Where(r => r.deptCode == user).Where(r => r.booked == 1).Where(r => rounds.Contains(r.round)).Where(r => r.rooms.Contains(roomToMatch));
        bookings = requests.ToList();

the full error

    Unable to create a constant value of type 'Timetabling06.Models.room'. Only primitive types or enumeration types are supported in this context.

Subquery is faster using a function

I have a long query (~200 lines) that I have embedded in a function:

CREATE FUNCTION spot_rate(base_currency character(3),
                          contra_currency character(3),
                          pricing_date date) RETURNS numeric(20,8)

Whether I run the query directly or the function I get similar results and similar performance. So far so good.

Now I have another long query that looks like:

SELECT x, sum(y * spot_rates.spot)
FROM (SELECT a, b, sum(c) FROM t1 JOIN t2 etc. (6 joins here)) AS table_1,
     (SELECT
        currency,
        spot_rate(currency, 'USD', current_date) AS "spot"
      FROM (SELECT DISTINCT currency FROM table_2) AS "currencies"
     ) AS "spot_rates"
WHERE
     table_1.currency = spot_rates.currency
GROUP BY / ORDER BY

This query runs in 300 ms, which is slowish but fast enough at this stage (and probably makes sense given the number of rows and aggregation operations).

If however I replace spot_rate(currency, 'USD', current_date) by its equivalent query, it runs in 5+ seconds.

Running the subquery alone returns in ~200ms whether I use the function or the equivalent query.

Why would the query run more slowly than the function when used as a subquery?

ps: I hope there is a generic answer to this generic problem - if not I'll post more details but creating a contrived example is not straightforward.

---

EDIT: EXPLAIN ANALYZE run on the 2 subqueries and whole queries

• subquery with function: http://ift.tt/1cvVnZA
• subquery with direct query: http://ift.tt/1KRONb0
• whole query with function: http://ift.tt/1cvVpk2
• whole query with direct query: http://ift.tt/1KROMUh
• just the function body, using one set of arguments: http://ift.tt/1cvVofQ

How to get how many minutes the user is idle by using user lastActivity?

Im using PhpMyadmin Mysql

this is my sql record from database, How can I use timediff to subtract NOW(); from last activity which is user is user1 and get how many minutes user1 is idle.

id   lastActivity           user
1    2015-05-08 00:20:40    user1
2    2015-05-08 00:32:29    user2  

Having Trouble Running An SQL Update Statement

Forgive me but I'm relatively new to SQL.

I am trying to update a column of a table I created with a function I created but when I run the Update Statement, nothing happens, I just see the underscore flashing (I'm assuming its trying to run it). The Update Statement is updating around 60,000 fields so I assume it should take a little while but it's been 10 minutes and no good.

I would just like to know if anyone knows just some general reasons that the underscore may be flashing. I know this is super general but I've just never seen this before.

Here's an image of what I'm talking about: http://ift.tt/1P6hA1q

How to query a one-to-many table based on a condition on a date field?

I have Client table with ont-to-many with Comment table as the following:

Client table:

Id name status....

Comment table:

Id description created_date client_id

this is what i tried:

    @Entity
@Table(name = "client")
public class Client implements Serializable {
/** The comments. */
    private Set<Comment> comments = new HashSet<Comment>(0);
@OneToMany(fetch = FetchType.EAGER, mappedBy = "client", cascade = CascadeType.ALL)
    @OrderBy(clause = "createdDate")
    //@Fetch(FetchMode.SELECT)
    public Set<Comment> getComments() {
        return comments;
    }
}
@Entity
@Table(name = "comment")
public class Comment implements Serializable {

    /** The created date. */
    private Date createdDate;

@ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CLIENT_ID", nullable = false)
    public Client getClient() {
        return client;
    }

    /**
     * Sets the client.
     *
     * @param client
     *            the new client
     */
    public void setClient(Client client) {
        this.client = client;
    }

}

    public List<Client> getLastEditedDateFromComment() {
        try {
            Calendar cal = Calendar.getInstance();
            //cal.add(Calendar.HOUR_OF_DAY, 2);
            cal.add(Calendar.DATE, -7);
            //.add(Restrictions.le("comments.editedDate", cal.getTime()))
            //System.out.println("ClientDAO.getLastEditedDateFromComment()"+cal.getTime());
            startOperation();
            Criteria criteria = session
                    .createCriteria(Client.class)
                    .createAlias("comments", "comments")
                    .add(Restrictions.eq("status", "Pending"))
                    .add(Restrictions.le("comments.createdDate", cal.getTime()))
                    .setProjection(
                            Projections.distinct(Projections.projectionList()
                                    .add(Projections.groupProperty("clientName"))
                                    .add(Projections.groupProperty("id"))
                                    .add(Projections.groupProperty("status"))
                                    .add(Projections.property("clientName"), "clientName")
                                    .add(Projections.property("id"), "id")
                                    .add(Projections.property("status"), "status")
                                    .add(Projections.max("comments.createdDate"))));
            // .setProjection(Projections.max("comments.editedDate"));
            // Integer maxAge = (Integer)criteria.uniqueResult();
            List<Client> clist = criteria.setResultTransformer(Transformers.aliasToBean(Client.class))
                    .list();
            tx.commit();
            return clist;
    }
    }

the above code generates:

select distinct this_.clientName as y0_, this_.ID as y1_, this_.status as y2_, this_.clientName as y3_, this_.ID as y4_, this_.status as y5_, max(comments1_.createdDate) as y6_ from zyhnavkp.client this_ inner join zyhnavkp.comment comments1_ on this_.ID=comments1_.CLIENT_ID where this_.status=? and comments1_.createdDate<=? group by this_.clientName, this_.ID, this_.status

I need a query to get all clients with the created_date passed 1 week ONLY from comment table where status ='Pending', I'm trying to do this hibernate, any sql query is acceptable :)

Thanks in advance :)

SQL: Searching X table and returning rows from Y based on X data

1. Search goals for rows where userid = 1.
2. Using returned rows, search checkin where biometricid's match, along with the userid, and filter out rows that are older than the goal date.

Note: Both the userid and biometricid are foreign tables.

How may I do this with one query?

checkin
id  | userid    | date                  | biometricid   | value
1   | 1         | 2015-01-10 00:00:00   | 1             | 9
2   | 1         | 2000-05-11 00:00:00   | 1             | 7
3   | 2         | 2015-01-10 00:00:00   | 1             | 9
4   | 1         | 2015-01-10 00:00:00   | 2             | 1
5   | 1         | 2017-01-11 00:00:00   | 1             | 4

goals
id  | userid | date                 | biometricid   | value 
1   | 1      | 2000-01-05 00:00:00  | 1             | 3
2   | 1      | 2015-01-01 00:00:00  | 2             | 2
3   | 2      | 2015-01-01 00:00:00  | 1             | 2

desired result
id  | date                  | biometricid   | value 
1   | 2015-01-10 00:00:00   | 1             | 9
2   | 2017-01-11 00:00:00   | 1             | 4

SQL: Pulling info from a column and building new columns

I’m building a report where I am combining two different databases. As it is this query is functional.

select  t.[Test ID],
          [Business Group],
          [Department],
          [SubGroup],
          [Regulation Source],
          [Risk Level],
          [Regulation Title],
          [Data Points],
          [Control Test Question(s)],
          [Compliance Test Questions],
        r.MonthSubmitted,
          CompliancePassPercent,
          ControlPassPercent


from DefaultOperationalRisk..RCSATracker             T (nolock)
left join DefaultOperationalRisk..TB_FLODTestResults R (nolock)

on T.[Test ID]=R.[TestName]

where t.[Control Testing Status 3] not like 'test retired' 
or    t.[Control Testing Status 3] not like 'pending test development'
and r.MonthSubmitted > GETDATE()-90

The problem is that the columns from TB_FLODTestResults should look more like this:

AprilCompResults|AprilControlResults|MarchCompResults|MarchControlResults|

Instead these columns come out looking like this:

MonthSubmitted|CompliancePassPercent|ControlPassPerent|

This way means a lot of my information is duplicated. I need the results to be based on the Test ID column from the RCSATracker database.

The thread from this question game me a start (SQL: Move data in one column to different columns).

SELECT a.TestName, 
       b.ControlPassPercent AS 'April Control Results', 
       c.ControlPassPercent AS 'March Control Results', 
       d.ControlPassPercent AS 'February Control Results' 
FROM   (SELECT DISTINCT testname 
        FROM   DefaultOperationalRisk..TB_FLODTestResults) a 
       LEFT JOIN (SELECT testname, 
                         ControlPassPercent
                  FROM   DefaultOperationalRisk..TB_FLODTestResults
                  WHERE  MonthSubmitted = 'March 2015') b 
              ON b.TestName = a.TestName 
       LEFT JOIN (SELECT TestName, 
                         ControlPassPercent
                  FROM   DefaultOperationalRisk..TB_FLODTestResults 
                  WHERE  MonthSubmitted = 'April 2015') c 
              ON c.TestName = a.TestName 
       LEFT JOIN (SELECT TestName, 
                         ControlPassPercent 
                  FROM   DefaultOperationalRisk..TB_FLODTestResults 
                  WHERE  MonthSubmitted = 'February 2015') d 
              ON d.TestName = a.TestName 

I would like the monthly results/columns to have a three month look back not including the current month. So basically a rolling query. Also I have not been able to fit the code I wrote from the forum thread into my query at the top.

Counting total records vs status

I'm looking to do an update for a batch when all the jobs for that batch are completed. To do this I want to compare the total number of jobs vs the total completed. I'm doing that with the query below, however it's a pretty slow query. Any suggestions to improve this? Or alternate ways to approach the main update?

SELECT DISTINCT j.BatchId, 
    j.JobStatusId,
    COUNT(j.BatchId) OVER(PARTITION BY j.BatchID, j.JobStatusID),
    COUNT(j.BatchId) OVER(PARTITION BY j.BatchID) 
FROM [Job] j 
ORDER BY j.BatchID

Want specific years between date joined and now

I am trying to get the members of a company that qualify for 'EMERITUS' status.

To qualify, one must be a member for 35 years from the date joined 'JOIN_DATE' and must be >=65 years of age to qualify 'BIRTH_DATE'. I want to see >= 2015 under the 'EMERITUS' column. Does this query make sense?

SELECT 
  N.ID, N.FULL_NAME, N.MEMBER_TYPE,
  N.JOIN_DATE,DA.BIRTH_DATE,
  (SELECT CASE 
     WHEN DATEDIFF(YEAR,N.JOIN_DATE,GETDATE()) + 35 > DATEDIFF(YEAR,DA.BIRTH_DATE,GETDATE()) + 65 
       THEN CONVERT(VARCHAR(4),YEAR(N.JOIN_DATE) + 35)
     WHEN DATEDIFF(YEAR,N.JOIN_DATE,GETDATE()) + 35 < DATEDIFF(YEAR,DA.BIRTH_DATE,GETDATE()) + 65 
       THEN CONVERT(VARCHAR(4),YEAR(DA.BIRTH_DATE) + 65)
     ELSE NULL
   END) AS 'EMERITUS'

Get the last transaction where date is less than 1 year ms access

I have this code:

    cnn.Open()
        query = "SELECT LAST(AnnualPayments.DueDate), Members.FirstName, Members.LastName " & _
        "FROM AnnualPayments INNER JOIN Members ON AnnualPayments.MemID = Members.ID " & _
        "WHERE (((DateDiff('d', Now(), AnnualPayments.DueDate)) < 1)) " & _
        "GROUP BY  Members.FirstName, Members.LastName,  AnnualPayments.DueDate " & _
        "ORDER BY AnnualPayments.DueDate DESC"

        cmd = New OleDbCommand(query, cnn)

        Dim dt As New DataTable
        ds = New DataSet
        ds.Tables.Add(dt)
        da = New OleDbDataAdapter(cmd)
        da.Fill(dt)

        Dim newRow As DataRow

        For Each newRow In dt.Rows

            MainForm.lstView1.Items.Add(newRow.Item(0)) 'this is for ID
            MainForm.lstView1.Items(MainForm.lstView1.Items.Count - 1).SubItems.Add(newRow.Item(1)) 'this is for date
            MainForm.lstView1.Items(MainForm.lstView1.Items.Count - 1).SubItems.Add(newRow.Item(2)) 'this is for firstname
        Next

    Catch ex As Exception
        GetErrorMessage(ex)
    Finally
        CloseConnection()
    End Try

What it's supposed to do is show only the data if the due date is more than 1 day from the date today. Can you correct my mistake please. Thanks.

SQL: Not Like produces different results than what would be Like's opposite

So, I'm practicing for an exam (high school level), and although we have never been thought SQL it is necessarry know a little when handling MS Access.

The task is to select the IDs of areas which names does not correspond with the town's they belong to.

In the solution was the following example:

SELECT name 
FROM area 
WHERE id not in (SELECT areaid 
           FROM area, town, conn 
           WHERE town.id = conn.townid 
           AND area.id = conn.areaid AND 
               area.name like "*"+town.name+"*");

It would be the same with INNER JOINS, just stating that, because Access makes the connection between tables that way.

It works perfectly (well, it was in the solution), but what I don't get is that why do we need the "not in" part and why can't we use just "not like" instead of "like" and make the query in one step.

I rewrote it that way (without the "not in" part) and it gave a totally different result. If I changed "like" with "not like" it wasn't the opposite of that, but just a bunch of mixed data. Why? How does that work? Please, could someone explain?

Edit (after best answer): It was more like a theoretical question on how SQL queries work, and does not needed a concrete solution, but an explanation of the process. (Because of this I feel like the sql tag however belongs here)

How can I link PHP with a SQL services over bluemix?

I am deploying a web app using bluemix. I am using php and SQL DB service. But I have a question, How Can I link my php code to make a query in SQL service in bluemix ?

Running a count of something against something else to find duplicates in MS SQL

I've not really done much with count before outside of basic stuff. The basic thing I'm trying to do find any point in a table these two specific Vendor IDs (say xxx8 and xxx5) are sharing the same batch number.

I'm assuming I want to run a count vendor id against batch number, but I'm not sure now to structure that.

sql Loader read file with more or less column

I need to create a .ctl file that can read source file wit less or more row... Example

file a: a;b;c

file b: a;b

load data
APPEND 
into table table_of_parameter
fields terminated by ";" optionally enclosed by '"'
TRAILING NULLCOLS
(
parameters1,
parameters2,
parameters3
)

and I want this:

select * from table_of_parameter

parameters1 parameters2 parameters3

 a              b              c
 a              b           null (o something else)

there's a way to do this on ctl file?

Sort group of records that are resulted from ORDER BY FIELD

In a MySQL table, I want to sort the group of records that I get by ORDER BY FIELD()

More specifically, Suppose, I ran the following query:

SELECT name,status,date FROM memberTickets ORDER BY FIELD(status,7,10,3,4) ASC

and got the following result:

---------------------------------------------------------
|    name    |    status    |            date           |
---------------------------------------------------------
|    A       |      7       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    B       |      7       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    C       |      7       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    D       |      10      |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    E       |      10      |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    F       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    G       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    H       |      3       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    I       |      3       |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    J       |      3       |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    K       |      4       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    l       |      4       |    2015-05-07 00:00:00    |
---------------------------------------------------------

Now, I want to sort the records in such an way so that the position of the group of records remain same(as set by order by field), but the records in each group are sorted with the date attribute as descending order, like the following:

---------------------------------------------------------
|    name    |    status    |            date           |
---------------------------------------------------------
|    A       |      7       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    B       |      7       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    C       |      7       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    D       |      10      |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    E       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    F       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    G       |      10      |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    H       |      3       |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    I       |      3       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    J       |      3       |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    K       |      4       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    l       |      4       |    2015-05-05 00:00:00    |
---------------------------------------------------------

Any idea or suggesions how to do that?

Importing and Splitting Excel Sheets into Tables

I have 6 excel sheets that I'm importing into sql server and each sheet will be its own table. They all have a Compound Name and Compound Type column which I would like to make into a Compound table that they all would refer to by ID. Is there an easy way to do this with minimal sql?

The closest I can get is a wizard that will split up each table into the two tables I want, but doing this for each table gives me 6 Compound tables. I'm new to sql and I can't immediately think of a way to combine these tables and preserve the references while not creating duplicates.

Thank you for any help.

SQLITE3 Insert Error

I'm definitely new to SQL but I feel like inserting is pretty simple. I can't figure out the issue

def insert(title, name):
    time = datetime.now()
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    query = """INSERT INTO test ('{}', '{}', '{}')""".format(title, name, time)
    c.execute(query)
    conn.commit()

When I pass the following:

insert(1, 2)

I get the error:

OperationalError: near "'1'": syntax error

All fields are text if that helps.

Thanks in advance

Hibernate Search- Select Query SQLSyntaxErrorException

I am trying to run the below query in Hibernate Search and I am getting the below exception in the log. I am not sure what I am missing to make it work.

String hsql="SELECT * FROM (SELECT dest.ID,dest.postal_code AS ZIP, ACOS(SIN ( RADIANS( src.latitude) ) * SIN ( RADIANS ( dest.latitude )) + COS ( RADIANS ( src.latitude)) * COS ( RADIANS ( dest.latitude )) * COS ( RADIANS( src.longitude ) - RADIANS ( dest.longitude ))) * 3959 AS DISTANCE FROM post_codes dest CROSS JOIN post_codes src WHERE src.ID = ( SELECT ID FROM post_codes WHERE postal_code = :zipCode GROUP BY ID ) AND ( dest.ID <> src.ID OR  dest.ID = src.ID )) HAVING DISTANCE <= :miles GROUP BY ID,ZIP,DISTANCE;ORDER BY DISTANCE";

org.hibernate.SQLQuery query = getSession().createSQLQuery(hsql);
query.setParameter("zipCode", 60195);
query.setParameter("miles", 5);

List list= query.list(); 
System.out.println("***************"+list.size()); 

Exception in the log

16:24:34,387 WARN  [JDBCExceptionReporter] SQL Error: 911, SQLState: 22019
16:24:34,387 ERROR [JDBCExceptionReporter] ORA-00911: invalid character
16:24:34,409 ERROR [[spring]] Servlet.service() for servlet spring threw exception
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)

What is the syntax for adding a row to a table that has an auto-incremented column?

I'm new to SQL and have a question about how to insert a row into a table that has an auto-incremented column. In my script I create the table

CREATE TABLE orgs ( O_Id int NOT NULL identity(1,1), org varchar (255) NOT NULL, PRIMARY KEY (O_Id) );

which is supposed to look like

                   orgs
-----------------------------------------------------
      O_Id           |            orgname
-----------------------------------------------------
        1            |          "StackOverflow"
-----------------------------------------------------
        2            |          "Madoff Investments"

However, I don't know how to insert rows into this table. W3Schools shows the syntax

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

but I'm not sure how to use that when one of the columns is auto-incremented. I've tried

INSERT INTO orgs (O_id, orgname) VALUES (DEFAULT, 'StackOverflow');

but get the error

DEFAULT or NULL are not allowed as explicit identity values.

I've also tried overriding,

INSERT INTO orgs (O_id, orgname) VALUES (1, 'StackOverflow');

but get the error

Cannot insert explicit value for identity column in table 'orgs' when IDENTITY_INSERT is set to OFF.

What should I be doing?

a query in sql server

I have a table with this field in sql server : user_id , seller_id , cat_id , brand_id , action_type

action_type values is 0 , 1 , 2 , 3

I want a query that can show me per "user_id and seller id "

the number of 0 fileds from action type

the number of 1 fileds from action type

the number of 2 fileds from action type

the number of 3 fileds from action type

fo example :

user1 seller1 cat1 brand1 0

user1 seller1 cat1 brand1 0

user1 seller1 cat2 brand1 1

user1 seller1 cat2 brand1 0

user1 seller1 cat1 brand1 0

user1 seller1 cat3 brand1 2

user1 seller1 cat3 brand1 2

user1 seller1 cat4 brand1 2

i want a table in output that can show me the number of action type: the column of output table is :

user_id seller_id , seller action_type_0 , action_type_1 ,action_type_2 action_type_3

output:

user1 seller1 4 1 3

description"

4:number of 0 in action_type per seller_id and user_id

1:number of 1 in action_type per seller_id and user_id

2:number of 2 in action_type per seller_id and user_id

Database value being truncated at special character

I've exported my database from MySQL Workbench locally and I've just tried to run it on my live server and everything inserts successfully.

Some of the fields are being truncated whenever there is a special character, so 'This is a £ test' becomes 'This is a '.

I'm assuming this is an encoding issue however both the database on live and local have UTF8_unicode_CI as the collation.

Any ideas? Thanks

Select specific row based on one field vs a default

I am looking to do something I'm not even sure is possible, say I have the following table

ID Part   Setting   Shop
1  A      1    
2  B      2
3  B      1.5       B
4  C      3
5  D      4

I am trying to find a query that will allow me to select all unique part type settings, but if I pass in a shop I will get the specific part for that shop.

So default would be

ID Part   Setting   Shop
1  A      1    
2  B      2
4  C      3
5  D      4

But if I send in WHERE SHOP = B then the result would look like

ID Part   Setting   Shop
1  A      1    
3  B      1.5       B
4  C      3
5  D      4

PostgreSQL Function Returning Table or SETOF

Quick background. Very new to PostgreSQL. Came from MS SQL Server. I am working on converting stored procedures from MS SQL Server to PostgreSQL. I have read Postgres 9.3 documentation and looked through numerous examples and questions but still cannot find a solution.

I have this select statement that I execute weekly to return new values

select distinct id, null as charges
              , generaldescription as chargedescription, amount as paidamount
from  mytable
where id not in (select id from myothertable)

What can I do to turn this into a function where I can just right click and execute on a weekly basis. Eventually I will automate this using a program another developer built. So it will execute with no user involvement and send results in a spreadsheet to the user. Not sure if that last part matters to how the function is written. Below is one of my many failed attempts:

CREATE FUNCTION newids
RETURNS TABLE (id VARCHAR, charges NUMERIC
             , chargedescription VARCHAR, paidamount NUMERIC) AS Results
Begin
SELECT DISTINCT id, NULL AS charges, generaldescription AS chargedescription, amount AS paidamount
FROM mytable
WHERE id NOT IN (SELECT id FROM myothertable)
END;
$$ LANGUAGE plpgsql;

Also I am using Navicat and the error is:

function result type must be specified

Data is not updating into database

I want to update the values of textboxes into my database.

The code does not show any syntax error and redirects easily to the page I'm redirecting but still database is not updating the new data in it.

conn.Open();
string str_id = Session["userid"].ToString();
int id;
id = Convert.ToInt32(str_id);
id = Int32.Parse(str_id);

string updatequery = "Update empdata set fname='" + updatename.Text + "',education='" + updateeducation.Text + "',position='" + updateposition.Text + "',email='" + updateemail.Text + "',address='" + updateaddress.Text + "',contact='" + updatecontact.Text + "',account='" + updateaccount.Text + "',postal='" + updatepostal.Text + "',password = '" + updatepwd.Text + "' Where id = '" +id.ToString()+ "'";
SqlCommand updateinfo = new SqlCommand(updatequery, conn);
updateinfo.ExecuteNonQuery();
updateinfo.Dispose();
updationmessage.Text="<p style='color:green;'>Information updated successfully</p>";

Execute a sql query and assign result to a variable in FTL file

I want to execute a SQL query (if a value exist in a table or not) inside ftl file and based on the result 1 or 0 I want to do further operations.

Like:

IF EXISTS(SELECT 1 from DataDb:tbl_conf
                                WHERE name="IsEnabled")
THEN

I do not want to execute this query again and again, i just want to store this Query's value to a variable and then use it anywhere in my ftl file.

MySQL SELECT email where field contains a value

I would like to do a simple select - but the condition is a bit tricky for me (because I'm a SQL-beginner).

I got this table:

userid | email             | newsletters
     1 | test@example.com  | 1,2
     2 | test2@example.com | 1

Now I would like to get all email-addresses of users, which want to get newsletter "2".

This would be:

email | newsletters
test@example.com | 1,2

And of course: In another query all users, which are subscribing newsletter number 1:

Result:

email | newsletters
test@example.com | 1,2
test2@example.com | 1

What would be the correct sql-query? I think this should be the right beginning, but I don't know which condition I have to use:

SELECT email FROM users WHERE newsletter CONDITION?

Could you please help me out? :-)

PHP Query issue in setting values?

Quick question. I've got a SQL query which works with my database. However, I am to sure how to set parameters in my PHP file so that it works with my query?

Any Ideas? I'm having trouble with the Set parameters in terms of php and making my query work.

<?php

/*
 * Following code will list all the products
 */

// array for JSON response
$response = array();

// include db connect class
require_once __DIR__ . '/db_connect.php';

// connecting to db
$db = new DB_CONNECT();

set $orig_lat=51.42; 
set $orig_long=-0.550; 
set $bounding_distance=1;

// get all products from products table
$result = mysql_query(" SELECT * ,((ACOS(SIN($orig_lat * PI() / 180) * SIN(`latitude` * PI() /
180) + COS($orig_lat * PI() / 180) * COS(`latitude` * PI() / 180) *
COS(($orig_long - `longitude`) * PI() / 180)) * 180 / PI()) * 60 *
1.1515) AS `distance` FROM `Location` WHERE ( `latitude` BETWEEN
($orig_lat - $bounding_distance) AND ($orig_lat + $bounding_distance)
AND `longitude` BETWEEN ($orig_long - $bounding_distance) AND
($orig_long + $bounding_distance) ) ORDER BY `distance` ASC limit 1") or die(mysql_error()); 

// check for empty result
if (mysql_num_rows($result) > 0) {
    // looping through all results
    // products node
    $response["Location"] = array();

    while ($row = mysql_fetch_array($result)) {
        // temp user array
       $Location = array();
       $Location["id"] = $row["id"];
       $Location["latitude"] = $row["latitude"];
       $Location["longitude"] = $row["longitude"];



        // push single product into final response array
        array_push($response["Location"], $Location);
    }
    // success
    $response["success"] = 1;

    // echoing JSON response
    echo json_encode($response);
} else {
    // no products found
    $response["success"] = 0;
    $response["message"] = "No Profiles found";

    // echo no users JSON
    echo json_encode($response);
}
?>

How to determine what type of database - Old legacy corporate database

I have access to a legacy corporate database that I can connect to via Microsoft's ODBC for Oracle driver through Visual Studio 2010. Can I query the database to determine what type and version the database is?

Also, will the Microsoft ODBC for Oracle driver ONLY connect to Oracle servers, or will it connect to others as well?

At this point all I need is a "Top-N" query but haven't been able to get it to work. Here are a few simple queries I've tried, all give syntax errors.

SELECT     X_DATETIME_INSERT, X_USERID_INSERT, DB_ACC, ROW_ID, ACD_ACCIDENT_NO, GROUP_ROW_ID, NAME, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, 
                  PHONE, INJURY, DELETE_ROW
FROM         EMSDBA.ACD_VICTIM
WHERE rownum <= 100;

SELECT     TOP 100 X_DATETIME_INSERT, X_USERID_INSERT, DB_ACC, ROW_ID, ACD_ACCIDENT_NO, GROUP_ROW_ID, NAME, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, 
                  PHONE, INJURY, DELETE_ROW
FROM         EMSDBA.ACD_VICTIM

SELECT     X_DATETIME_INSERT, X_USERID_INSERT, DB_ACC, ROW_ID, ACD_ACCIDENT_NO, GROUP_ROW_ID, NAME, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, 
                  PHONE, INJURY, DELETE_ROW
FROM         EMSDBA.ACD_VICTIM
LIMIT 100

how can i display message below a table created using while loop in php

this is the display page.i want to dislay message after while loop gets over below the table .the code below displays the message beside the table. I want to echo a message such as "data fetched sucessfully" below the table

<?php
session_start();
$con=mysql_connect("localhost","root","") or die("Could not Connect");
$db=mysql_select_db("practice",$con);
if($_SESSION['user']=="")
{
    header("location:login.php");

}
if(isset($_GET['status']))
{
    $status=$_GET['status'];
    if($status==sucess)
    {
        echo "<h2>Details Updated Succesfully....!!</h2>";
    }
}
echo "Welcome ".$_SESSION['user'];
echo "<br>";
$page=$_GET['page'];
if($page=="" || $page=="1")
{
$page1=0;
}
else
{

  $page1=($page*4)-4;
  }
$query="SELECT * FROM `details` LIMIT $page1,4";
$result=mysql_query($query,$con);
$numrows=mysql_num_rows($result);
if($numrows>0)
{?>
<table  align="left"  border="1" >
<thead>
<tr>
  <th>NAME</th>
  <th>USERNAME</th>
  <th>PASSWORD</th>
  <th>Hobbies</th>
  <th>E-MAIL</th>
  <th>GENDER</th>
  <th>CONTACT</th>
  </thead>
  <tbody>
 <?php
  while($row=mysql_fetch_array($result))
 {?>
 <tr>
  <td><?php  echo $row['name'];?></td>
  <td><?php  echo $row['username'];?></td>
  <td><?php  echo $row['password'];?></td>
   <td><?php echo $row['hobbies'];?></td>
  <td><?php  echo $row['email'];?></td>
  <td><?php  echo $row['gender'];?></td>
  <td><?php  echo $row['contact'];?></td>
  <td><a href="update.php?id=<?php echo $row['id'];?>">Update</a></td>
  <td><a href="delete.php?id=<?php echo $row['id'];?>">Delete</a></td>
 </tr>
<?php

}
echo "data fetched sucessfully";


  ?> 
</tbody>
</table>
</br></br></br></br></br></br></br>
  <?php
$query1="SELECT * FROM `details`";
$result=mysql_query($query1,$con);
$count=mysql_num_rows($result);
$a=ceil($count/4);

for($b=1;$b<=$a;$b++)
{
?><a href="welcome.php?page=<?php echo $b; ?>" style="text-decoration:none"><?php echo $b."";?> </a>



<?php

}

}
else
{
echo "NO Record";

}
mysql_close($con);
    ?>

Difference between EXEC('UPDATE') and UPDATE for new column

Today i run into a problem with my sql script. I must add a not nullable column but i don't have a known value for default so i create the column as a nullable one, update all rows and then change the column to be not nullable.

Running the following script finished with success.

    BEGIN
    ALTER TABLE driver ADD DriverLocationId INT NULL
    EXEC('UPDATE driver 
    SET DriverLocationId = (SELECT TOP(1) CountryId FROM Country WHERE CountryCode IN (''USA'',''CAN'', ''MEX''))')
    ALTER TABLE driver ALTER COLUMN DriverLocationId INT NOT NULL   
    END

And the following is failing.

BEGIN
 ALTER TABLE driver ADD DriverLocationId INT NULL
 UPDATE driver 
 SET DriverLocationId = (SELECT TOP(1) CountryId FROM Country WHERE CountryCode IN (''USA'',''CAN'', ''MEX''))
 ALTER TABLE driver ALTER COLUMN DriverLocationId INT NOT NULL  
 END

Probably when the update script from 1st attempt is executed correctly after altering.

Do you have any explanation for this? (i have searched already on stack & other forums, but i'm not so familiar with the sql context language and probably i failed at wording and finished finding nothing)

How do I select a given number of rows for one table for each parent primary key in another table in sql server 2012?

I have two tables in sql. One is a table of test cases and the other is a table of test runs with a foreign key that links back to a test case. I want to get the most recent 10 test runs for each test case. I don't want to loop through if I don't have to, but I don't see any other way to solve this problem. What is the most effective way to handle this sort of thing in sql server?

Oracle SQL: How to replace a character in an existing VARCHAR2 field with CR/LF

I'm selecting from a field that has a bunch of ugly jammed-together text where each 'section' ends in a semi-colon. What's the method to replace the semi-colon with line-feed 0d0a (crlf) "non-printable" characters? I've searched but most people want the opposite (to remove CR/LF from existing text.)

Example: The existing Varchar2 field XYZ contains

'blah blah blah; and more stuff; and even more;'

(This is not something I control, I just have read access to it.)

If I REPLACE (XYZ,';',' CHAR(13)||CHAR(10)) ') on output the output is just this with no active line feeding. It literally adds the characters CHAR(13)||CHAR(10)) and not the function of a CR/LF. Like this:

Select REPLACE(XYZ',';','CHAR(13)||CHAR(10)') from XYZTable;

Unwanted Result:

'blah blah blah CHAR(13)||CHAR(10)) and more stuff CHAR(13)||CHAR(10)) and even more CHAR(13)||CHAR(10))'

What I'd like to see in an output:

blah blah blah
and more stuff
and even more

Really hoping there's a way to insert HEX or something. Or am I stuck doing a loop? Rather not, this is a construction from separate lines with sequences where the table designer should have used a CLOB and I'm using LISTAGG to paste it together.

Is there a name for aggregation queries that record '0' values?

I see questions all the time (and perhaps a Meta post could be made about how to handle them) that follow the lines of:

Get the count of [some field] for [some object].

Where the problem usually lies in:

SELECT myField, COUNT(*)
FROM myTable
GROUP BY myField;

Which will not return rows that have 0 counts, so it typically involves preforming an outer join back to the table to get those counts.

Is there a name for this type of procedure? Is it still just simply Aggregation? The reason I wonder if it's different, is because it involves using a join to aggregate data that doesn't exist in the table.

Also, I have heard of special types of aggregation such as conditional aggregation, so I thought there might be a term [slang, or standard] for this type of operation.

SQL workaround to substitute FOLLOWING / PRECEEDING in PostgreSQL 8.4

I have a query that does a basic moving average using the FOLLOWING / PRECEDING syntax of PostgreSQL 9.0. To my horror I discovered our pg server runs on 8.4 and there is no scope to get an upgrade in the near future.

I am therefore looking for the simplest way to make a backwards compatible query of the following:

SELECT time_series,
       avg_price AS daily_price,
       CASE WHEN row_number() OVER (ORDER BY time_series) > 7 
        THEN avg(avg_price) OVER (ORDER BY time_series DESC ROWS BETWEEN 0 FOLLOWING
                                                                     AND 6 FOLLOWING)
        ELSE NULL 
       END AS avg_price
FROM (
   SELECT to_char(closing_date, 'YYYY/MM/DD') AS time_series,
          SUM(price) / COUNT(itemname) AS avg_price
   FROM auction_prices 
   WHERE itemname = 'iphone6_16gb' AND price < 1000
   GROUP BY time_series
   ) sub

It is a basic 7-day moving average for a table containing price and timestamp columns:

closing_date timestamp
price        numeric
itemname     text

The requirement for basic is due to my basic knowledge of SQL.

Android Studio & SQL Server Connection Issue

I am practicing some development for Android using Android Studio and I am currently trying to establish an SQL connection between the app I'm working on and the SQL Server I have got set up on my local machine. I would like to note that I am debugging the program through my LG G2 SmartPhone.

Here is the code I've got out of the net after a long search. It's pretty much impossible for me to find anything better:

public class DB_Handler {
public static void ConnectToDatabase(){

    new Thread(new Runnable() {
        public void run() {
            try {

                // SET CONNECTIONSTRING
                Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
                String username = "DB_9C48A0_androidApp_admin";
                String password = "xx";
                Connection DbConn = DriverManager.getConnection("jdbc:jtds:sqlserver://208.118.63.19:1433/DB_9C48A0_androidApp;user=" + username + ";password=" + password);

                Log.w("Connection", "open");
                Statement stmt = DbConn.createStatement();
                ResultSet reset = stmt.executeQuery(" select * from users ");

                String s = reset.getString(1);

                DbConn.close();

            } catch (Exception e) {
                Log.w("Error connection", "" + e.getMessage());
            }
        }
    }).start();
}

}

No errors occur, my debugging just stops and the application shuts down.

Any ideas?

Oracle, two tables with different attributes

I have a question that requires me to find the total profit for a month. For example I have two tables, one table transactions has the attribute agreedprice, the other table called car has an attribute called pricepurchased. How can I display these two tables side by side?

I've tried all types of joins however it's only giving me matching values between the two. I want to see the agreedprice and the pricepurchased in my SQL file, the price purchased never meets the agreedprice so for every statement I've done it only gives me one side.

Change data source dynamically - CakePHP

I want to allow a user to pass in database config data in from the front-end and with that data i want to try switch to that datasource.

I am using CakePHP 2.3.

Embedding image in email with SQL [sp_send_dbmail]

I want to embed an image into an HTML email with SQL server. The emails are getting sent but the images do not show. My code is as follows:

    DECLARE @body_custom VARCHAR(MAX)
SET @body_custom = '<head>
                        <title> Embedded Logo Example</title>
                        <meta name="Generator" content="EditPlus">
                        <meta name="Author" content="">
                        <meta name="Keywords" content="">
                        <meta name="Description" content="">
                    </head>
                    <body>
                        <table>
                            <tr>
                                <td valign="top" align="left">MyHeader</td>
                            </tr>
                            <tr>
                                <td valign="top" align="left">
                                    <img src="image_1.jpg" width="235" height="70" alt="">
                                </td>
                                <tr>
                                <td valign="top" align="left">
                                    <img src="image_2.png" width="235" height="70" alt="">
                                </td>
                            </tr>
                            <tr>
                                <td valign="top" align="left">
                                    <img src="image_3.gif" width="235" height="70" alt="">
                                </td>
                            </tr>
                            </tr>
                        </table>
                    </body>'
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'Google_Gmail_Account'
    , @recipients = '???@gmail.com'
    , @subject = 'SQl 2008 R2 Email Test'
    , @body = @body_custom
    , @body_format = 'HTML'
    , @file_attachments = 'C:\Users\User\Desktop\image_1.jpg;C:\Users\User\Desktop\image_2.png;C:\Users\User\Desktop\image_3.gif'

This sends the email and the images as attachments, but they do not display in the email.

I have also added the images to my google drive and taken their links and used that, but still no success...

SQL request and loop trough result best pattern (Java)

I didn't really know a good title for this but I'm asking myself what would be considered "good" for the following scenario

Table Place: id / name / address / etc

Table Tag: id / tag_name / score

Table Place_Tag id / place_id / tag_id / numberX

For example, my application allow a user to search a place based on a tag. the result should contain the name of the place as well as the top 3 tag for that place (ordered by numberX)

My question is what is the best way to achieve this in Java using SQL ?

1) Select p.name, t.tag_name FROM Place p LEFT JOIN ... GROUP BY p.name,t.tag_name ORDER BY p.id,pt.numberX DESC

And then in Java I loop though the result (note the ORDER BY p.id) create some logic to only get the top 3 tag and when I detected that the n+1 id is different that means I'm processing the next place..

2) places = SELECT DISTINCT p.name FROM..... ORDER BY p.id
   tags = SELECT ... FROM Place_Tag pt WHERE pt.place_id IN (places.ids)

I make two separate SQL call one to get the places one to get the tags and then I find some kind of logic to link both together in Java

3 (this is the easier but I think the worst) ) places = Select p.name FROM Place...

and then I loop through places and make a call to the database to get its tag (meaning if I loop trough 100 place I will make 100 extra call everytime..)

4) ? any other pattern ?

Could someone with more experience alive me on this?

Thank you!

Oracle SQL Select Unique Value AND Only one value from matching rows with non-unique values

I have two tables, a master table and a general information table. I need to update my master table from the general table. How can I update the master table when the general info table can have slightly different values for the descriptions?

Master

+------+---------+
| Code |  Desc   |
+------+---------+
|  156 | Milk    |
|  122 | Eggs    |
|  123 | Diapers |
+------+---------+

Info

+------+---------------+--------+
| Code |     Desc      | Price  |
+------+---------------+--------+
|  156 | Milk          | $3.00  |
|  122 | Eggs          | $2.00  |
|  123 | Diapers       | $15.00 |
|  124 | Shopright Cola| $2.00  |
|  124 | SR Cola       | $2.00  |
+------+---------------+--------+

As you can see item 124 has 2 descriptions. It does not matter which description.

My attempt is returning 124 with both codes, I understand my code is looking for both the unique Code and description in the master which is why it returns both 124 but I'm unsure how to fix it.

INSERT INTO MASTER
(
SELECT UNIQUE(Code), Desc FROM INFO A
WHERE NOT EXISTS
   (SELECT Code FROM MASTER B
   WHERE A.Code = B.Code )
);

I have also tried:

INSERT INTO MASTER
(
SELECT UNIQUE(PROC_CDE), Desc FROM FIR_CLAIM_DETAIL A
WHERE Code NOT IN
    (SELECT Code FROM FIR_CODE_PROC_CDE_MSTR B
    WHERE A.Code = B.Code )
);

Match two SQL database records using Python by a database column field

I got a problem when i tried to compare two databases that i read using python. The problem is to write proper for cycle. Databases are read using:
output_po = psql.read_frame(sql_po, conn) output_stock = psql.read_frame(sql_stock, conn)

SQL_PO records: PO PN
FTP1 1111
FTP2 2222

SQL_STOCK records PN WHS NAME 1111 VLN A 1111 VLN B 1111 ZRS A 2222 DLN Q

So the result i want to get is to get all SQL_STOCK info based on SQL_PO info where Whs == 'VLN'. Result: PO PN WHS NAME FTP1 1111 VLN A FTP1 1111 VLN B

I have tried using:

    for index, row in sql_po.iterrows():

        if sql_stock[sql_stock['PN']==row['PN']].empty:
            send_email = 'F'
        else:
            if (sql_stock[sql_stock['Whs'] =='VLN'].any ):
               send_email = 'T'

But it gives all 3 records for PN 1111 from sql_stock as for 1111 there are 2 with vln and 1 with zrs whs. COuld you guys help me solve that problem to write cycle for in another cycle for?

Retrieving values from multiple tables

I am working on a school project and I'm stuck.

The requirement is to write a single query that gathers data from nine different tables and returns a result.

It needs the client names, contract names, project names, employees that logged hours for April, total hours logged by employee, employee rate, charges per employee for the projects, and the contact names for each contract.
(I know, that's a lot of data to pull.)

The tables are:

 Clients   
-------------------------------
ClientID | CommonName  

 ContractTypes   
--------------------------------------  
ContractTypeID | ContractType   

 Contracts   
---------------------------------------------------------  
ContractID | ClientID (FK) | ContractType (FK)   

 Projects   
---------------------------------------------------  
ProjectID | ProjectName | ContractID (FK)   

 Contacts   
-----------------------------------------  
ContactID | FirstName | LastName  

 ContractContacts   
--------------------------------------------------  
ContractID (PK/FK) | ContactID (PK/FK)   

 Employees   
-----------------------------------  
EmpID | name | titleID | level   

 BillingRates   
----------------------  
titleID | level | rate  

 WorkHours   
----------------------------  
EmpID | HoursWorked  

I ran the following, but it resulted is over 23,000 rows - not what is needed:

select 
   clients.CommonName, projects.ProjectName, employees.FirstName,  
   employees.lastname, sum(workhours.hoursworked) as TotalHours,  
   BillingRates.Rate,contacts.FirstName, contacts.LastName,     
   contacts.AddrLine1, contacts.AddrLine2, contacts.City, 
   contacts.State_Province, contacts.Zip
from 
   employees, BillingRates, Clients, Projects, WorkHours, Contracts, Contacts
group by 
   clients.CommonName, projects.ProjectName, employees.FirstName,  
   employees.lastname, BillingRates.Rate, contacts.FirstName, 
   contacts.LastName, contacts.AddrLine1, contacts.AddrLine2, 
   contacts.City, contacts.State_Province, contacts.Zip

I honestly have no idea how to pull in all that data into one query to generate one report.

I received help on generating a query to return the employees that logged hours for April, total hours logged by the employee, and the charges per employee for the projects in the following:

select 
   employees.EmpID, 
   sum(workhours.hoursworked) as 'TotalHours',
   firstname, lastname, 
   ISNULL(sum(workhours.hoursworked), 0) * ISNULL(min(rate), 0) AS 'TotalRate'
from 
   employees 
inner join 
   workhours on employees.empid = WorkHours.EmpID
left join  
   BillingRates on employees.titleid = billingrates.titleid
                and employees.level = billingrates.level
where 
   WH_Month = 4
group by 
   LastName, firstname, employees.EmpID

I assume this query would be nested into the outer query, but I don't know how to construct it.

Would someone be able to assist with writing this query?

Does simple-minded implementation of optional criteria in SQL risk serious inefficiency?

This is a non-urgent question about Oracle (~11g) query optimisation. It is not urgent because the query is currently performing well within requirements, but I should like to know if it is advisable to optimise my query anyway.

The query joins a table of at most a few thousand records via foreign keys to 4 others, so there is just one matching row in the other tables, which have about 30 to 300 records. It selects almost all (c. 40) columns of the main table and 3 columns from the other tables. The DB is accessed using embedded SQL in a source file pre-processed with sed to adapt it to one of two platforms: (normal) Oracle on AIX or Oracle Rdb on VMS. In both cases the query runs on the same machine as the DB, Rdb tends to perfom slightly better.

The point about which I have doubts, is that in order to implement optional selection criteria I have a condition :pid = 0 OR t.pid = :pid and two like t.name like :name, where :pid and :name are host variables and :name is set to "%" when this criterion is not needed and otherwise the actual name.

I have written it like this to keep the code simple, namely to avoid the complications of dynamic SQL and the redundancies of writing several variants of what is (textually) quite a large query – either of those options would probably increase maintenance costs. Since the DB is not large (and is expected to stay much the same size) and performance is well within bounds, I currently feel justified.

Is this approach reckless, sensibly pragmatic or somewhere in between?

SQL SERVER 2012 - Joining an ID with a NULL

I'm trying to join a table of data to another table, but one of the ID that I'll be joining on is NULL. However, I have a specific ID I want it to link to with the NULLs.

I might be oversimplifying this question too much with this example, but I'm hoping this will point me in the right direction. So suppose we have the two tables below.

TABLE1:

    Name    ID
    A       1
    B       2
    C       NULL

TABLE2:

    ID      Value
    1       4
    2       5
    3       6

What would I need to do in the query to get an output like this?

OUTPUT:

    Name    Value
    A       4
    B       5
    C       6

Thanks in advance for any assistance.

SQL query doesnt bring out results when queried using date

I see that a table has the data value as "18-May-2012". But when i query looking for the same date using the below query, no results are available.

Select Submit_Dt From Siebel.S_Order_Dtl
where submit_dt = '18-May-2012'

Could you pls help me sort this issue?

How to delete all the records except the one with the latest date and based on some where condition

I have a table with 20 records. Now I want to delete records with column name as Action "system sent reminder notice to A"(15 records with same message) apart from the one with the latest date (I cant specify the date on or before, as I wanted this to be in general).