Improve company productivity with a Business Account.Sign Up

x

Query Syntax

50K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

claimRemark        masterClaimID      medinfoReferralCode       medinfoCompany      accession_no      medinfoApptno
LM3598523CT      3215274                      KNI320                               RMR                              1851300                      1669308
LM3599207XA      3215276                      SCH091                               RMR                              1851298                      1669307
LM3599207XA      3219463                      SCH091                               RMR                              1669307                      1669307

need sql query that removes only the duplicate claimremark values where accession_no <> medinfoapptno.

Results:
claimRemark        masterClaimID      medinfoReferralCode       medinfoCompany      accession_no      medinfoApptno
LM3598523CT      3215274                      KNI320                               RMR                              1851300                      1669308
LM3599207XA      3219463                      SCH091                               RMR                              1669307                      1669307
0
Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hi Experts,

We are running a server with SQL server express which has the recovery model set to simple.
Is it possible to shrink the log file from a batch file?. Id like setup a schedule to shrink the log file within task scheduler since i believe our version of MS SQL server express doesnt have the management jobs to setup tasks that the full versions of SQL server have.

Thanks
0
Write a program - Survey Analysis
 
Background There is a Survey Manager who is responsible for all the Surveys enrolled by different companies.  This survey is conducted every year. The companies who enroll themselves into an ongoing Survey answers to the questions asked to them. The Survey is taken once every year. The current problem is given to you on the 2nd year of the survey. You will have to create data for both year and other details are given below.
 
Take the below questions as an example for your user story
What is the name of your Current Project?
How much is your yearly revenue?
How many employee you have?
Less than 10
10 - 100
100 - 500
500 - 1000
1000+
What is the goal of your company in 500 words?
Do your company have health and safety team?
Yes
No
Select all applicable clients you have  
Local
City
National
International
How many employee you have per gender?
Women ________
Men       ________
Others   ________
Date of submission: dd/mm/yyyy
   
Create a set of answers for all questions on behalf of each company for year 2016 & year 2017. Make sure your answers for year 2017 is little different from 2016. (This is like creating a sample data for your user story) So now you have all the necessary prerequisite for the below mentioned story.
 
User Story: The system must read all the answers to each companies for year 2016 and 2017 then it should return the following:
Get the company name who has minimum …
0
Greetings,

This is regarding an issue I have with an SQL 2012 server.  I had obtained assistance from Microsoft on a performance issue several months ago, and they placed the server into some sort of debug or trace mode as part of their diagnostics.  I thought they turned off the trace mode after diagnostics were completed, but as it turns out they did not.  I found this out after noting that available disk space on the SQL server's C: drive was slowly being reduced over time.  There is a debug or trace mode active that is generating daily folders containing log files for apparently every single transaction being executed on the SQL server.  Folders are named by date, so today's log files folder is under a folder called LOGS, and is named 2018-04-18.  Here is a listing of the files in that folder:

- c. 25 files named all.XX.log where XX goes from 1 thru 22.  Files vary in size from c. 15K to c. 2.5MB
- a file called all.csv which is 31K in size
- 6 files called fatal.X.log where X goes fro 0 to 6.  Files are up to 10K in size
- 15 files named Info.XX.log where XX goes from 0 thru 14.  FIles are 1K to 6K in size.
- 22 files called Trace.XX.log, where XX goes from 1 thru 22.  Files are 22K to 2.2 MB in size.
- 8 files called Warn.XX.log, where XX goes fro 0 thru 7.  Files are 1K in size.

On a typical work day, from 25 to 40 MB worth of files are generated in each daily folder.  Currently I have to manually clean these out once a month to keep the accumulation …
0
Hello experts. I have a vb.net form that connects to a hosted MySQL database which contains a table for visitor data. As a secondary check (which I won't get into the reasons why) what I would ultimately like to do is hit a button that checks for any new records that were added to the table and send out an email showing which records were added. I created a form that connects to the database and have the datagridview showing all the records just to verify I am connecting with no issues. Its the code behind the button which I described above that I have zero clue how to accomplish. Below is what I have so far. Any help for a newbie learning to code which be most helpful.

App Configure Tab:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>

  <connectionStrings>
    <add name="dbx" connectionString="server=xxxxxx;user id=xxxxx=;Password=xxxxxxxxx;database=XXXXXXX" providerName="MySqlData.MySqlClient" />
  </connectionStrings>
</configuration>

Form:

Imports System.Configuration
Imports MySql.Data
Imports MySql.Data.MySqlClient


Public Class Form2
    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = GetVisitorsList()
    End Sub

    Private Function GetVisitorsList() As DataTable
        Dim dtVisitors As New DataTable
        Dim connstring As String = …
0
I have a query in Microsoft Access that yields a list of unique numbers from one field in a table. For example, the query might yield:

1
2
17
31
1034
34348

How can I convert these results to a comma-separated string. In this example, I'd want to get:

1, 2, 17, 31, 1034, 34348

as the result.
0
I need to write a SQL Query (or View or Stored Procedure) to find records that exist and do not exist.  I can do one or the other but need to do both in the same view or stored procedure.  

I have a table that stores records of items.  I need to include all the items from this Table 1
I have a second table that stored information about items.  The tables are linked by a text field where they both equal.  I've tried right, left and outer joins.
I need to query for a field in Table 2 that has a value (this will be a parameter).  

I need to return all rows where this value exists from Table 2 and all the remaining items from Table 1 where the field is null.  So I should end up with a  table of rows that contain the information for the item and the remaining items with that field null so that I can see that I don't have data entered for the item.
0
Hello,

I am trying to write some difficult SQL, and this is a common scenario that would help illustrate what I'm trying to do...

Imagine we have customers, products and order details where each order detail has an order number, a customer and a product. Now a customer returns a product and let's say because of some operational issues, we can't get the order number from the original order, so we need to interpret it as the most RECENT order for that customer and product that is BEFORE the return date. So for example:

- On 1/1/18, Bob buys a toaster and a coffee pot
- On 1/5/18, Bob buys another one of the same toaster and an apple
- On 1/16/18, Bob buys another one of the same toaster once more

When he walks in on 1/7/18, and returns the toaster, we want the SQL to return the order # from the sale on 1/5/18 as it is the most recent sale of that same product that is BEFORE the return date. It wouldn't return the sale on 1/1/18 as that's not the most recent and it wouldn't return the sale on 1/16/18 as that occurred after the return date of 1/7/18.

How would I write SQL to accomplish this?
0
In SQL Server 2014 - I need to give users access to a view but not the underlying tables that the view queries.  I have two databases.  ODS_DHW and DDH.
The tables are populated and reside in ODSDWH and the views are created in a schema in DDH.

The error the users are getting is DataSource.Error: Microsoft SQL: The server principal "UserId" is not able to access the database "ODS_DHW" under the current security context.
Destails:
  DataSourceKind=SQL
  DataSourcePath=db05;ddh
  Message=The server principal "UserId" is not able to access the database "ODS_DHW" under the current security context.
  Number=916
  Class=14
0
There are two SQL servers connected to a PowerVault MD3200 DAS. This MD3200 is connected to a PowerVault MD1200 for additional storage.
These are both using 3.5" drives.
I am looking to expand the storage for both the SQL servers and am looking into attaching another MD1200 or maybe an MD1220.
I read the Dell guidebook and it looks like it should be possible to attach an MD1220 to this setup:
http://www.dell.com/downloads/global/products/pvaul/en/powervault-md3200-md3220-technical-guidebook-en.pdf

Even though the MD1220 uses 2.5" drives, looks like it should not be an issue as long as the disks are not used in the RAID as the 3.5"? (they won't be)
Is this guaranteed to work or is there something else on the SQL servers and/or DAS I need to check first before expanding?
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I have four(Name, address, code, Date ) columns in a table
Records are
Name    address      Code              Date
A            add1            x;y;z                01/12/2018;02/22/2018;03/09/2018
B            add2            x1;y1;z1          03/12/2018;03/22/2018;05/09/2018
Output should be
A         add1        x                        01/12/2018
                             y                        02/22/2018
                             z                        03/09/2018
B        add2         x1                      03/12/2018
                            y1                       03/22/2018
                            z1                       05/09/2018

How to achieve the output like in above by oracle sql?
0
Need to use the SQL and involve that queries to Java program and send an email to the people , The mail consists of the table what we have gathered from the sql queries as a box and , I will schedule that one in the scheduler and run it every time.

How I can achive this one, as I have only quries not program

Please suggeste
0
We have a customer who bought out another company and merged.

Right now we have both companies in the same building. they are both on different routers and different IP' ranges.
192.168.16.0 and the other is 192.168.1.0

They both have their own domain controllers.

We are going to be bringing them on all the same network.

The company that was bought out uses a sql database program called Miracle.
all the client workstations have the Miracle executable and it is pointing to the database of the server using the Host name not the IP of the server.

Our question is is it possible to demote the server which is now a DC and add it to the main network at a child server?.

or can we just bring the server to the other network as a DC but turn off DHCP and DNS server?
0
I have Oracle 11g database with  TB_user table with sample data
   
USER_ID                         USER_NAME
 
  usr_JohnSmith            John Smith
  usr_JohnSmith1          John Smith
  usr_BruceLee              Bruce Lee
  usr_BillClinton            Bill Clinton

 I have TB_Project table
  Project name        Project Manager
     Proj1                      John Smith
     Proj2                      Sam NotFound
     Proj3                      Bill Clinton

This is a poorly designed database.   But I need a query to get the result of Project Name and  User_ID   with following rules

When a Project Manager name is not found in User (E.g.  Sam NotFound)  return null
When there is duplicate (E.g John Smith) - return any one of User ID
0
The attribute key cannot be found when processing: Table: 'dbo_FACT_PROD_QTY', Column: 'CONTRACT_RATE_KEY', Value: '0'. The attribute is 'CONTRACT_RATE_KEY'.
The attribute key was converted to an unknown member because the attribute key was not found. Attribute CONTRACT_RATE_KEY of Dimension: ContractRateLevel f

- I can process the dimension (ContractRateLevel), that the Fact table is pointing to with no problem.
- The dbo_Fact_Prod_Qty table does have Nulls in column Contract_Rate_Key.
- There are no Null values in the Dimension table: ContractRateLevel.

Can someone help, as I don't have this issue with my test cube, and the Fact table as NULL values for the same column.
0
how do I pivot this table so I get a new row for each part?


SELECT part1, part2, part3, part4 FROM  test
0
Not that long ago, SQL Server was running just fine, but now it doesn't.  Every time I invoke the program, I get the following cryptic error message: "Exception has been thrown by the target of an invocation," at which point, the program shuts down.  Does anyone happen to know what this message means and what to do about it?  Thank you, ~ Peter Ferber
0
Hello Experts ,

We did couple days ago a P2V of our SQL server with GP 2010 on it.  2 days after the migration user started getting weird error that never happen prior the migration.. Any one already saw those error and know what we can do to get rid of it?

Right now it appears to be only happening in the Purchasing module when doing transaction entry...   they got an http 404 error,
Error-404.pngUsers are also seeing this one after the HTTP 404 error - Deleted record.png BUT It does not delete the record.
Deleted-record.png
It might be a transaction pending that GP still try to process at every transaction....  but incomplete because improper interruption?
 
GP is new for me any help appreciated..    Also where i can find log file?

Thanks,
0
Not sure why the modify is not updating the xml in temp catalog3 table

any help much appreciated

WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource



SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
	  INTO #CATALOG1
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY 
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;



WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
        ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
 AS rd)
,SDS AS
(SELECT SDS.name AS SharedDsName
       ,SDS.[Path]
       ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
 FROM dbo.[Catalog] AS SDS
 WHERE SDS.Type = 5)     -- 5 = Shared Datasource
SELECT [SharedDsName], 

Open in new window

0
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

i am attempting determine if the start (GPA_PRD_STDT) and end (GPA_PRD_ENDDT) dates share the same month then field1 = "Monthly and if not then check the start and end date to determine if they fall within the past quarter.  Then repeat the process to determine previous month/quarter.

 ,  CASE TRUNC(D.GPA_PRD_STDT, 'MM') = ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
                        AND TRUNC(D.GPA_PRD_ENDDT, 'MM') = ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) THEN
                           D.GPA_STAT = 'MONTHLY'
                    Else TRUNC(D.GPA_PRD_STDT, 'MM') <> ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
                        AND TRUNC(D.GPA_PRD_ENDDT, 'MM') <> ADD_MONTHS(TRUNC(D.GPA_PRD_STDT, 'MM'), -1) THEN
                             D.GPA_STAT = 'QTRLY'
                    END 

Open in new window

What am I missing?
0
How to split by comma and join each value from table1 value field with table2 symbol

Table1(ID, Value)
1,   [AB, BC, DF, TD]
2,   [BC, AC, TD]


Table2(ID, Symbol)
1, AB
2, AC
3, BC
4, DF
5, TD
0
Once i make a connection to my database from Oracle Forms 10g i want to execute this statment from Forms
 execute immediate 'alter session set current_schema=CRM';
Please can you provide me the script to run this statement from oracle forms.

Thanks ALL
0
hi,

is MS SSMS for SQL server is free for all desktop ?
0
Hello,

I have a column in a table that i want to update with the current date and time every time the row is updated (or inserted).

Basically this is a last updated datetime column and used by another process to find recently updated rows in the database.

To avoid a minumim amount of changes in our legacy system i thought i would add a trigger on INSERT and UPDATE that would find the updated row and update the column.

However the only way i can find the row is by its identity column as this is the only column that is unique in the table.

It appears that it is impossible to get the value of the identity column in a trigger.  Is this correct?

And triggers should be written to handle multiple rows so using scope_identity would not be a solution?

So is there a solution?  Either using a trigger, or some other mechanism that ensures that the column is always updated with the cirrent date and time?
0
I have a MySQL table w/ the following columns:

ip_address
timestamp
fan1
fan2
fan3
fan4
fan5
fan6
fan7
fan8

The value of fan1 to fan8 cols can either be "failed" or "operational". This is reporting data via snmp OIDs to this table/cols.

I need an SQL query that gets a count/total of the row that it's reading where cols fan1 to fan8 have value "failed". So the total can be 0 to 8.  Normally I would use select count(*) as total, but that counts rows not cols. So I need the total columns value for the current row where fans have failed for that device.

Example:

row1:
ip - 192.168.1.1
timestamp - blahhhhhh...
fan1 - operational
fan2 - failed
fan3 - failed
fan4 - operational
fan5 - operational
fan6 - operational
fan7 - failed
fan8 - operational

So in the example above after running the mysql query the total should be 3 in the output of the query.

I'm not sure how to approach this. I tried doing something using SUM w/ IF statement but couldn't get it working.

Thanks.
0

Query Syntax

50K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.