[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL

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

Here is a  sample row from a sql server 2012 table named custorders

CustID      OrderDate      OrderAmount
12345      2018-04-28      500.00


If I run the following query on custorders

SELECT TOP 25000 [CustID]
      ,[OrderDate]
      ,[OrderAmount]
  FROM [Test].[dbo].[CustOrders]

  where custid in (12345, 78901)

I would only get info for custid 12345 since custid 78901 does not exist

CustID      OrderDate      OrderAmount
12345      2018-04-28      500.00


Would like to "force" a row in the result set for custid 78901  with a date value of '01/01/1900' and a order amount of zero

so the query results look like


CustID      OrderDate      OrderAmount
12345      2018-04-28      500.00
78901  1900-01-01    0

Not sure best way to change the query above to do this
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Trying to convert Access syntax to SQL (Update Query)

UPDATE dbo.tbl_DriveSalesSidhilMatrix
Set tbl_DriveSalesSidhilMatrix.EnteredBy = case when OperatorName is null then Operator else OperatorName
From dbo.view_Z_SalesOrderAmmendmentsSums INNER JOIN
                         dbo.tbl_DriveSalesSidhilMatrix ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_DriveSalesSidhilMatrix.EnteredBy LEFT OUTER JOIN
                         dbo.tbl_Syspro_Operators ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_Syspro_Operators.OperatorCode
WHERE (dbo.tbl_DriveSalesSidhilMatrix.RecordSource = N'Sidhil')

I'm getting "Incorrect syntax near the keyword 'From'."

Can anyone help?
0
I need a query in MS SQL that shows me the count of records that meet the below criteria.

4 fields - CustomerName, Direction, Disposition, TimeStamp

I need all records where Direction = Outbound, Disposition = Voicemail, and there is a record after the timestamp from the first critera with the same customer name, direction = inbound.

Is this possible and if so how ?
0
Hi,

I need help from EEs to get the the query. I have two tables.
Table A has ID that can link to Table B's ID.

I need to bring everything from Table A without duplication. As you can see in Table B, it contains everything from TableA
and some with duplications.

The logic is to bring everything from Table A and PID from Table B

The end result should look like this

ID              PID
1545043        1-001  
10018849       8-001  
10038557       32
10032746      
10555277       10-001
10010026       6-001
10039886       58-5
10037757        
10734379       85


If there is no duplication in Table B, get the PID; if there is duplication in Table B, get the one reads "001"

thanks
test.xlsx
0
I am using PL SQL (Oracle) to run a  have a query and when I copy the results into an Excel spreadsheet the "clm_no" ( looks like this "1.7282E+14" instead of the correct format "172820014002563".  I tried using "CAST(a.clm_no AS VARCHAR2(50)) as Claim_Number", but it did work for all of the fields.

Is there a way to make sure they clm_no's will copy correctly to the spreadsheet?  Below is the code:

select distinct cin, lname, fname, dob, bill_type, net_amt as Net_Amount, srv_from_date, srv_thru_date, clm82_payee_id as Payee_ID, clm82_chk_date as Check_Date,
  clm82_chk_num as Check_Number,
  npi, project_code, clm_no AS Claim_Number, patient_control as Patient_Control_Number, ppg, prv00_prv_name as PPG_Name  
  from phdp_claims a left join phdp_evr b on substr(b.clm_no,4) = clm_no
  left join cn114261.dhcs_update dhs ON substr(dhs.ccn, 4) = clm_no  
  where dhs.ccn is null
  and npi in ('xxxxxxxxxx')
  and srv_thru_date <= '31-dec-2017'
  order by cin;
0
Hello Experts!

I have create a macro called AutoExec to run the function below:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function

Well the function links the tables in SQl Server at runtime and if the ODBC 17 path is correct a message with Re link completed Successfully pop up. All is well as long as I continue using my laptop the problem comes up if I install the same database on a different server client stand alone network not connect with my laptop because the new path is supposed to be:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Now how best I can be changing the path below to be reference the correct server name? For example if the same name changes to CHRIS is there a way to alter the path below:

constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Example where there is USER change to CHRIS,the challenge here is how to change the code manually because we always send an accde format to clients. Some are suggesting to  put this string below in …
0
Experts,

I routinely build Access Databases as desktop processes to analyze data. I'm looking to understand the best way to handle the following situation:
Every query I run joins tables on Cost Center. My Cost Centers are 10 Char Alphanumeric. I've already learned from previous questions that I should create a Cost Center master table and use the Autonumber (or other short numeric) field as a CostCenterID for use in joins because its more efficient.
So now let’s say I have a
1.      Cost Center Table with 2000 cost centers and a CostCenterID (Autonumber) field that I’ll use as my primary key, and a
2.      Transaction Table with 1mil records that I want to join on the Cost Center table, but the CostCenterID does not yet exist in the Transaction Table.

How is performance effected if I:
1. Use an Update Query to add the CostCenterID from the Master Cost Center table as a foreign key to any Transaction Tables, so Cost Center and Transaction tables can be joined directly, or
Use Interim Query/Tables
2. Use a Select query to create an interim relationship.
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber)
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window

3.      Create an interim table with a make table query
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber) INTO tbl_Bridge
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window


One opinion I was given is that option 1 is best. I just want to make sure as I will be basing many processes going forward off of this method

Thanks!!
0
Hi,
am Getting an error when importing a file using SSIS
The "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" failed because truncation occurred, and the truncation row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  
An error occurred while processing file "\\testfolder\\Inbound\test_file_123756.TXT" on data row 1.  
any guidance appreciated
0
How can I turn a single row of 15 columns into 15 rows with 2 columns for ORDER BY?

I have an online form and the SQL to score the form data we have puts everything in a single row. What we need is 15 rows so we can use ORDER BY. I have googled PIVOT mySQL, but that is the opposite of what I want.

What I am doing is summing multiple rows per column from a ninja form:

SELECT  SUM(ACCEPTANCE) as ACCEPTANCE, 
        SUM(ASSOCIATION) as ASSOCIATION, 
        SUM(CURIOSITY) as CURIOSITY,
        SUM(EGO) as EGO, 
        SUM(EMPATHY) as EMPATHY, 
        SUM(ENVY) as ENVY, 
        SUM(EXCITEMENT) as EXCITEMENT, 
        SUM(FAMILY) as FAMILY, 
        SUM(FEAR) as FEAR, 
        SUM(GREED) as GREED, 
        SUM(HUMOR) as HUMOR, 
        SUM(HUNGER) as HUNGER, 
        SUM(LAZY) as LAZY, 
        SUM(SEX) as SEX, 
        SUM(TRUST) as TRUST

FROM v_nf_questions q
JOIN v_nf_answers a on q.q_id=a.a_id
JOIN answers_table ans ON a.f_answer=ans.ARCH_NAME
WHERE nform_id = 348

Open in new window


So here is what I get
what_i_have.jpeg
But here is what I want
what_i_want.jpegwhat_i_have.jpeg
what_i_want.jpeg
0
I need regular expression for following in Sql Server ( I hope it is same in vb.net or c#)
I am looking for a pattern  someletters somenumbers  some letters- someletters somenumbers  some letters     ( All capital letters)
  examples  AA111WW-B123SS
                      AB1DFG-S3ERT

   Another pattern I am looing is   somenumbers someletters somenumbers -someletters
     examples   11AAA123-SDFG
                          1AB23-AB
1
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

We have a SQL Server database with assets (financial products and linked objects, like loans, mortgages, real estate). An asset can be related to one or more customers. A customer can be related to one or more assets. See this table for an example.

Source data
I need to group all customers that share an asset - even when it's an indirect relation. For example, customer a shares assets with customers b and c. But c also shares an asset with d. This links d not only to c, but also to a and b. The same goes for e, f and g. Customers f and g share no asset, but they both share an asset with e - that makes them part of the same group.

Customer groups
I'm looking for a way to find all customer groups in the database. For your information: there will be about 2 million customers and 3 million assets. A customer group is expected to have 1 to 20 members. The result in this example would be something like this:

Query result
Can anyone help me to find a solution, keeping in mind the data volume? Your assistance is of course much appreciated!

This code will create and populate a table with the data from the example above.

create table dbo.AssetCustomer
( assetId int
, customerId char(1))

Insert into dbo.AssetCustomer values (1000, 'a')
Insert into dbo.AssetCustomer values (1000, 'b')
Insert into dbo.AssetCustomer values (2000, 

Open in new window

1
We're trying to build a datagrid connected to an SQL database in UWP as a proof of concept. We're running into an issue declaring the DataTemplate that it doesn't understand the DataType, and I can't get it to process it properly. This has to be defined as we're binding the textboxes to the SQL database. We were using this site for reference https://docs.microsoft.com/en-us/windows/uwp/data-access/sql-server-databases    (we've made changes to suite our needs)

Code segment throwing errors
<DataTemplate x:DataType="local:Product">

The.xaml page with the errors (line 48)
<Page
    x:Class="App3.Views.TicketsPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    Style="{StaticResource PageStyle}"
    mc:Ignorable="d">

    <Grid x:Name="ContentArea">
        <Pivot
            x:Name="Items">
            <PivotItem Header="Open">
                <Grid Background="{ThemeResource SystemControlPageBackgroundChromeLowBrush}">
                    <RelativePanel>
                        <ListView Name="WorklogList"
                            SelectionMode="Single"
                            ScrollViewer.VerticalScrollBarVisibility="Auto"
                            ScrollViewer.IsVerticalRailEnabled="True"
                            

Open in new window

0
Hello Experts.

I'm afraid this question might be a little vague, but I'm almost certain that it is possible to do.

Basically, I want to build a LINQ query in my vb.net project that compares two object's key fields and reports back which ones in Object A are NOT in Object B, and vice versa.

More specifically, I have a non-databound DataGridView which contains a current list of member ID's (Object A). I also have a bound DataGridView that contains an older dataset (say for example last week's member ID's).

To be perfectly honest, every time I read about SQL joins, my mind turns into a pretzel, but I feel that it's time I overcome this mental block and try to get a handle on the difference between and inner-join and an outer-join.

So, if anyone has a good example of a LINQ query that might be close to what I've described above, I would be interested in seeing what that looks like.

Always Learning,
Tony G.
0
I have a sqlcmd command which I would like to not execute manually in the cmd window or in a Windows batch file, but to run as a SQL Server job, but putting the sqlcmd command line in the job results in an error on every single part of the command line, although the SQL Server doc gives an example of a sqlcmd call as a job step. So I'm a bit lost.

My SQL Server agent job looks like this: 1 step with the following content:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S MyServerName -i "C:\temp\My T-SQL.sql" -v DWH = MyDBName -o "C:\temp\My T-SQL Log.txt"

When parsing this, the job window already complains about the -S bit. If I eliminate that, it complains about the -i bit, so clearly, something is completely wrong with the whole thing, but what ? Must be simple, but I can't figure it out. And, needless to say, the job runs but fails with the laconic error message "incorrect syntax near" and mentions the sqlcmd.exe part of the call.

Can someone help me ?

Thanks
Bernard
0
Hi
I have Oracle SQL Dev version v17.4 with Java Platform 1.8.

I would like to connect to an Ms Access table but i can't see the tab. How can I add it?
Oracle Missing Ms Access tab
In an older Oracle SQL Dev, (1.5), i do have that tab.
0
Hi,

Hi Experts,

Not sure I have this correct as my source field has a space in it.  That might be problem, but not sure if I did this correctly.  I'm trying to change '100' to 'KABOOM'.  I'm not sure if 100 is stored as a CHAR or NUM though.  So I think that matters?


Case Statement:

CASE WHEN (Document Number) IN '100' THEN 'KABOOM ' ELSE (Document Number) END

Getting this error:    Oracle SQL Error:  ORA-00920: invalid relational operator

SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
1
Hi Expert,

I wants to archive and purge my table data based on following condition, could you please guide me how to do this using rowid .

--This is based on create_daytime column
Archive_criteria  --365*3    
Purge_criteria  -- 365*8    

Please let me know if you need more info.

Thanks in Advance
Mihir
0
Hi Expert,

I have a issue with a job, i'm trying to fetch and insert into common_error_log table.

FYI.. I have mentioned the code chunk.
This is failing before insert into Common_Error_Log table as the cursor has no record to fetch .
   OPEN C_GET_MAX_RETRY;
   FETCH C_GET_MAX_RETRY INTO L_max_retry_count;
   CLOSE C_GET_MAX_RETRY;
   
   IF L_max_retry_count IS NULL THEN
      O_error_message :=  'INVALID_MAX_RETRY_COUNT';
      RETURN FALSE; 
   END IF;

Open in new window


But after checked the value from table it's there, could you please throw an light if entry is there than why going this failure.

Please find the following attachment select stmt for cursor.

CEL
Please let me know if there need more info.
Thanks,
Mihir
1
I have a table 'userlogin'  that has duplicates or tripiliates, even more for the  'loginid'  field. And we need to clean it up and leave only the most recent.

There are two relevant fields:

The 'userloginid'  is a consecutive number.  (Autoid), the higher the number the most recent the entry
And the 'Loginid' field which is the one we need to check for duplicates.

I need an SQL that will delete any entries that are duplicates (or more) and leave only one of those entries, leaving the one where the 'userloginid'  has the higher number (Most recent)

Any entries without duplicates should be left as is, we should not delete those. Only dup entries.

Any help is appreciated.
1
Making Bulk Changes to Active Directory
LVL 8
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

HI,

I am working on a project where I get an error I don't understand.
The error is this:
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /var/www/kroweb.dk/public_html/ourdev/demo/classes/Post.php on line 42
and I put in the Post.php file here below.
You can see the page here: https://kroweb.dk/ourdev/demo/index.php

I hope that somebody can help me to fix this :)

	
<?php
  class Post { //ændret fra class User
    private $user_obj; //ændret fra class User
    private $con;

    public function __construct($con, $user) {
      $this->con = $con;
      $this->user_obj = new User($con, $user); //ændret fra class User
    }

    public function submitPost($body, $user_to) { //ændret fra class User - ALT HERFRA OG NED
      $body = strip_tags($body); // fjerner HTML tags
      $body = mysqli_real_escape_string($this->con, $body); //fjerner enkelt '' fra $body, således at det ikke skaber problemer i en sql query
      $check_empty = preg_replace('/\s+/', '', $body); //Fjerner alle mellemrum i $body (fordi man ikke skal kunne lave en post med mellemrum alene)

        if($check_empty != "") { //hvis der er indhold i $check_empty - som er er $body uden mellemrum

          $date_added = date("Y-m-d H:i:s"); //aktuel dato og tid variabel
          $added_by = $this->user_obj->getUsername(); // brugernavn variabel
            if($user_to == $added_by) {
              $user_to = "none"; //hvis der laves en post fra egen 

Open in new window

0
Just need to append new rows from one SQL Table to another.   In the example below, I just need to append Table A to Table B.

SQL Table A

FOLID
---------
1234
3333

SQL Table B
FOLID   AttributeID  Name  
--------     --------------    -------
99998   33333333     Test

Expected Results:

SQL Table B
FOLID   AttributeID  Name  
--------     --------------    -------
99998   33333333     Test
1234
3333
0
I am trying to create a sql query for a report that I will eventually create. In our erp software we have a manage shipping transactions screen where you put in a packingslip number and it returns details on the records. For example:

PACKING SLIP              Sales Order
PS-000000002511        SO-0001165
PS-000000002512        SO-0001165
PS-000000002500        SO-0001165

I can then double click on a record and it returns the detail. So in my erp software if I select  PS-000000002511, it will return 90 records of detail.

I have the following query based off of the two tables that house the packing slip.

SELECT SHIPTRNISSUE.PS_ID, SHIPTRNISSUE.SO_ISSUE_KEY, SHIPTRANS.SO_ID, SHIPTRANS.CUST_ID
FROM DELTEK.SHIP_TRN_ISSUE AS SHIPTRNISSUE INNER JOIN DELTEK.SHIP_TRN AS SHIPTRANS
ON SHIPTRNISSUE.PS_ID = SHIPTRANS.PS_ID
where SHIPTRNISSUE.PS_ID = 'PS-000000002511'

This returns a correct result of      PS_ID                              SO_ISSUE_KEY     SO_ID                  CUST_ID
                                                            PS-000000002511                2698           SO-0001165             TEST

The third table I need to add which contains the remaining detail data is called SO_ISSUE_LN. So as stated bove when querying the PS_ID and selecting the sales order number, the returned amount of records coming from the SO_ISSUE_LN table is 90.

The SO_Issue_LN table has the following fields:

SO_ISSUE_KEY,   SO_LN_KEY,   SO_ID,  PS_ID,   …
0
I'm using Sql server 2008

I have this query:

SELECT [Column1]
      ,[Column2]      
      ,Column3 = REPLACE(CONVERT(VARCHAR(MAX), Column3), '<', '< ')
      ,[Column4]
FROM [DMS].[dbo].[TestTable]


Column3 is ntext datatype.
Column 3 holds notes that are typed into a textbox.

With the existing query above, if column 3 contains a value like this:

test notes <br/> this is a test

then it changes it to this(it adds a space after the less than sign):

test notes < br/> this is a test


How do i revise my existing query so that if the value in column 3 has a less than symbol followed by a space already,
then don't add a space after it.

Only add a space after then less than symbol, when it's followed by any character other than a space.
0
I'm working in SQL Server 2012 and I'm having difficulty casting varchar as int for comparison between two tables.  

Here's some example DDL/DML:

CREATE TABLE #tbl1 (
      col1 int,
      col2 int
      );
CREATE TABLE #tbl2 (
      col1 varchar(255),
      col2 varchar(255)
      );
INSERT INTO #tbl1 (col1, col2)
VALUES (1, 2);
INSERT INTO #tbl2 (col1, col2)
VALUES (1.0, 2.0);

SELECT * FROM #tbl1 as t1
inner join #tbl2 as t2
on t1.col1=CAST(t2.col1 AS int)
where t1.col2=CAST(t2.col2 AS int)

And the error I'm getting:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2.0' to data type int.

If I do this:
SELECT 1.0 AS Original, CAST(1.0 as int) Casted
I get this:
-----------------------------
| Original |Casted |
-----------------------------
|      1.0     |    1       |
------------------------------
which is what I would expect.

I think I'm missing some basic foundational concept here.  Any help will be greatly appreciated.

Thanks!
1
Nagios Core 4.3.4   running on Ubuntu 17.4
Nsclient 4.3
Windows 2012 R2
SQL 2012 and 2014

I have a Nagios plugin check_sqldbsize.vbs that checks the size of my sql databases.

On my SQL 2012 server I have one instance and the check works fine from the server and from Nagios.

On my SQL 2014 server I have three (3) instances I have 1 of the three working the other two give me this error CINT



/usr/local/nagios/etc/objects/windowsservers# /usr/lib/nagios/plugins/check_nrpe -t 50 -H 'SERV014-N1' -c check_sqldbsize -a 'SERV014-N1-SQLC\ORF 8000 5000'
C:\Program Files\NSClient++\scripts\check_sqldbsize.vbs(25, 15)


/usr/local/nagios/etc/objects/windowsservers# /usr/lib/nagios/plugins/check_nrpe -t 50 -H 'SERV014-N1' -c check_sqldbsize -a 'SERV014-N1-SQLS\WSS_Content 8000 5000'

C:\Program Files\NSClient++\scripts\check_sqldbsize.vbs(25, 15) Microsoft VBScript runtime error: Type mismatch: 'cint'


from the server I ran the command against both instances and it works fine.

C:\Program Files\NSClient++\scripts>cscript check_sqldbsize.vbs SERV014-N1-SQLC ORF 6000 9000
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

OK: ORF database size is 3 MB.

C:\Program Files\NSClient++\scripts>cscript check_sqldbsize.vbs SERV014-N1-SQLS WSS_Content 6000 9000
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

OK: WSS_CONTENT database size is 45 MB.…
0

SQL

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.