Go Premium for a chance to win a PS4. Enter to Win

x

Query Syntax

49K

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

I have an SSRS combo box that will pass 1 of 3 values to my stored proc.

'ALL'
'NATIVE'
'LINKED'


I need the report to do the following based on one of these 3 options being picked.

If the parameter Native is picked...then it should be be equal to the columns value in the report of Native.
the same if Linked is chosen..

If All is chosen it should return anything in this column...which could be "Native" , "Linked", '' , NULL

Please help me build the where clause to handle this.

Here is what I have ..but its not working.


(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or  @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hi,

I have an Excel CSV file with appr. 40.000 lines. This file contains in one column text holding links which I need to use in SQL.
For example:
Sk-rmbillede-2017-11-18-12.58.32.png
When I import the CSV file to MySQL this column is imported as raw text. The field type i use for this column is VARCHAR.

My question is if there is any way that I can import this file in a way so the links embedded in the text gets active and working?
0
I have this method that gets the connection string. I first have a method that builds the connection string. Then I open the connection and finally I try to execute a SQL stored procedure against this connection. As soon as I try to execute the sp I get the message "Connection property has not been initialized" What am I missing and how do I correct this?

        public static SqlConnection GetConnection(string ConnectionString)
        {
            /* Create and open a connection to the SQL Server */
            /* Return open connection object */
            SqlConnection SQLConn = new SqlConnection();

            string loginType = Properties.Settings.Default.SQLLogintype.ToUpper();

            try
            {
                if (loginType == "WINDOWS")
                {
                    SQLConn.ConnectionString = ConnectionStringWindows(ConnectionString);
                    SQLConn.Open();
                }
                if(loginType == "SQL")
                {
                    SQLConn.ConnectionString = ConnectionStringSQL(ConnectionString);
                    SQLConn.Open();
                }

                return SQLConn;
            }
            catch (Exception ex)
            {
                Log.Write("Failed to get SQL connection to the database " + Controller.Instance.Model.SQLDatabase + ": " + ex.Message);
                Log.Write(SQLConn.ConnectionString);
                throw ex;
            }

        }
0
Hi

I have a SQL query where I want to show information in the last record entered on a field called [Machine number] rather than just the Maximum.
Is this possible?
0
I need to alter a table and add a field that can hold multiple values but I need to this using Access sQL statement / VBA not the GUI

Once the constraint is created I am unable to set the "allow multiple value" field

how can this be achieved programatically ?

thank you
0
Let's say I have a data set like

FLD1,FLD2,FLD3,FLD4,FLD5
010,1000,2000,3000,4000
020,1000,2000,3000,4000
030,1000,2000,3005,4000
...
998,1000,2000,3000,4000
999,1000,2000,3000,4000

Using the first row as the template I need to loop through thousands of records based on the value of the first field looking for any records that do not contain the same four values of the first record. So in this case it would find row 30. But how do I get the query to loop back on itself to find the mismatches? So I would define the first record as the master
010,1000,2000,3000,4000 so for every record that begins with 010 I need to search through all the other records for every FLD1 value and make sure that every combination for FLD1 010 exists in 020 through 999.


SELECT '010' AS F1,'1000' AS F2,'2000' AS F3,'3000' AS F4,'4000' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1001' AS F2,'2001' AS F3,'3001' AS F4,'4001' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1002' AS F2,'2002' AS F3,'3002' AS F4,'4002' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1003' AS F2,'2003' AS F3,'3003' AS F4,'4003' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1004' AS F2,'2004' AS F3,'3004' AS F4,'4004' AS F5 INTO #MyTempTable

SELECT '999' AS F1,'1000' AS F2,'2000' AS F3,'3000' AS F4,'4000' AS F5 INTO #MyTempTable
SELECT '999' AS F1,'1001' AS F2,'2001' AS F3,'3001' AS F4,'4001' AS F5 INTO #MyTempTable
SELECT '999' AS F1,'1002' AS F2,'2002' AS F3,'3002' AS F4,'4002' AS F5 INTO #MyTempTable
0
What am I doing wrong in this sequence.  I keep getting an error when I execute

      ELSE IF EXISTS (Select c.product_nbr from product_CHECKOUT c WHERE c.product_nbr = @product_nbr) AND                              
             IF EXISTS (Select u.UserID from u_USERS u  WHERE u.UserID = @CURRENT_USER AND u.Rule = 1 AND u.ACTIVEFLAG = 1)

Can I express  ELSE IF EXISTS   AND   IF EXISTS ?  Can I use the AND here before I give instructions to do something if they both return true?
0
Hi Expert,

Please help me find out the best solution for my query. I have dynamic sql with coalesce in where clause but it did not function well.

SET @sql = 'SELECT * FROM tbl1 WHERE ID = COALESCE(''' + @ID + ''', ID)'
EXEC(@sql)

and when I run this SP and i call

Exec sp_GetID @ID  = null,

it did not return a value.

But when i tried something like this

SELECT * FROM tbl1 WHERE ID = COALESCE(@ID , ID)

it works fine as expected.
0
hi,

now I want to setup SQL 2016 AOG and before that we have to setup Windows failover cluster , and it seems that all SQL server nodes which is Windows 2016 datacenter server need to join domain first before setting up the Windows failover cluster.

is that right? so no domain join not failover cluster and therefore no SQL 2016 AOG too?

so it seems that SQL server 2016 install in a workgroup configuration cant' install failover cluser and therefore, can't setup AOG as well ?
0
Hello ,

Im trying to find a quick easy way to import the results from a Web API into a sql server database , which will run every minute?

the example of the API is the following

https://bittrex.com/api/v1.1/public/getmarketsummaries   

Can anyone advise on this please?

Thanks
0
Important Lessons on Recovering from Petya
LVL 11
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Hello experts,

I got to do a replacement of my T320 server failed hard disk drive.  I am currently in RAID 10 with 4 Hard disk drive, while each drive is (Part number:F617N, HARD DRIVE, 300G, SAS6, 15K, 3.5, S-EG, E/C), one hard disk is failed.

The supplier sale offer me "400-AJRR 300GB 15K RPM SAS 12Gbps 2.5in Hot-plug Hard Drive,3.5in HYB CARR" , I am not sure if this model of hard disk drive compatible with my server or not. the sale said it is compatible, so please help to advise any problem when I replace with this Hard disk drive, the server are running Win2008R2 and SQL 2008.

My Raid is PERC H710 Integrated RAID Cont roller, 512MB NV Cache, Full H eight

Thanks
0
Hi I am trying to use SQL to update a table  (tEmailRx) with information from a form.  The table is laid out as:

CreateDate  - Date
MR - Number (long Int)
RxType - Text
Office - Text
Ws - Text

I am trying to use the following code:
    sSQL = "UPDATE tEmailRx tEmailRx.CreateDate = #" & Date & "#, tEmailRx.MR = " & Me.ID & ", tEmailRx.RxType='CL', tEmailRx.Office='" & sOffice & ",tEmailRx.Ws = Environ$(""computername"")"
    DoCmd.RunSQL sSQL

Open in new window

Which results in:

UPDATE tEmailRx tEmailRx.Date = #11-15-2017#, tEmailRx.MR = 126011, tEmailRx.RxType='CL', tEmailRx.Office='WB,tEmailRx.Ws = Environ$("computername")

But I am getting an error.  What am I doing wrong with my syntax?  SQL is a very weak link for me - Thanks!
0
I need help with a SQL Select Script that will remove duplicates and combine values.  I have a table with two columns.  Right now each ID is duplicated by the validity.  I just need need to combine all validity values and separate by a ";".

ID                Validity        
------             -----------
12A             Product
12A             Family
12A             FO
31C             Product
87A             FO
87A             Product

Expected Output:

ID                Validity
------             -----------
12A             Product; Family;FO
31C             Product
87A             FO; Product
0
i have a access query i am trying to update a bit field on a linked table on sql server, but i am getting a n error saying it must be an updateable query.

task = " UPDATE [dbo_Load Table] INNER JOIN [WorkTable] ON [dbo_Load Table].ID = [WorkTable].ID SET [dbo_Load Table].Processed = 1 WHERE [dbo_Load Table].ID = [WorkTable].ID"
DoCmd.RunSQL (task)

Open in new window


i have a worktable where user load data on to it from the main load table then edits the data and when successful clicks ona settoprocessed button which has the above code in it and i want all the matching data from work table to be set to 1 from load table so next time user load new data the values with 1 dont show up.
0
Select  
 
faa.AssetID, faa.AssetNumber, faa.Status, faa.Dept, faa.Class, faa.Description, faa.PropertyNumber,faa.AcquiredDateTime,

case  When fayr.ValueID = 'COST'  THEN fayr.Amount  else 0       end 'Cost',

case        when fayr.ValueID = 'ACC DEPR'  THEN fayr.Amount   else 0  end 'Accum Depr',

case        when fayr.ValueID = 'BOOK'  THEN fayr.Amount   else 0      end 'Book value'

 FROM [Livendb].[dbo].[FaAssets]          faa          with (nolock)

 join  [Livendb].[dbo].[FaAssetAmountsByYear]  fayr    with (nolock)
            on      faa.AssetID = fayr.AssetID  and faa.SourceID = fayr.SourceID

            where faa.[Status] = 'ACTIVE'   and faa.AssetID = '6'   and fayr.FiscalYearID = '2018' and fayr.ValueID in ('BOOK','ACC DEPR','COST')

I am getting multiple line, would like to combine 3 lines in to one so the Last 3 columns (cost, Accum Depr and Book Value) are all shown into on line, here is what I have right now:

AssetID      AssetNumber      Status      Dept      Class      Description      PropertyNumber      AcquiredDateTime      Cost      Accum Depr      Book value
6      0000000005      ACTIVE      15.9950      15BLDG      GENERAL HOSPITAL      NULL      2013-12-01 00:00:00.000      0.00      0.00      83722355.32
6      0000000005      ACTIVE      15.9950      15BLDG      GENERAL HOSPITAL      NULL      2013-12-01 00:00:00.000      0.00      9087644.68      0.00
6      0000000005      ACTIVE      15.9950      15BLDG      GENERAL HOSPITAL      NULL      2013-12-01 00:00:00.000      92810000.00      0.00      0.00
0
hi - thanks for looking.

would you please help me with the following sql statement using sqlite?

table d
ID int    
trxDate text
(no records in table - the text field is a string of date/time)

table t
ID int    
trxDate text

values for table t:
1, '11/15/2017 13:41'
2, '11/15/2017 13:45'

i need the following:

max of the date/time from table d.  if it is null, return the max date/time from table t.  if that is null, return the current date/time.

i tried to use the IFNULL function, but the second part of the function doesn't seem to be able to have a new SELECT statement within it.

any help would be appreciated.... thank you.
0
The setup of my question:
data:
customer_id                   Item_ID                order_date
1                                      apples                  11/1/17
2                                      apples                   1/1/17
3                                      apples                   5/1/17

My question is I want to see what customer purchase apples last and what was the sales date

Below is my script but when I run this it show them all since I have customer_id in the group by.

SELECT DISTINCT item_id, MAX(order_date) AS last_sales_date, DATEDIFF(day, MAX(order_date), GETDATE()) AS days_since_last_sold, customer_id
FROM            p21_order_view
GROUP BY item_id, customer_id
HAVING        (item_id = 'apples')
ORDER BY last_sales_date

Below are the results I want to see:
customer_id                   Item_ID                order_date
1                                      apples                  11/1/17

Thanks
0
I have a dataset in SSRS that will return a  list of comma delimited strings.

based on the values returned...I need this list of strings turned into a temp table...so I can evaluate the temp table in the stored proc in an In clause.


Please help
0
What SQL account will give me access to create?

•      bulk updates
•      stats monitoring
•      insert
•      VIEW SERVER STATE
0
Free Tool: SSL Checker
LVL 11
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 need to replicate a Database hosted on a SQL 2005 server std (Publisher) to SQL 2012 server std (Subscriber), I understand that there is no issue there because they are 2 major releases apart.

I wish to use a separate Distributor Server for performance, what version can be used here.  I can't seem to find this documented for my scenario.  I would use 2016 if possible?

The replication type is most likely to be snapshot.

Many thanks,
0
Hello,
I am writing a query to change the datatype of the sql table column from navarchar to varbinary(max).
The query i am referring is to :

ALTER TABLE dbo.Fie
   ALTER COLUMN DrIgLoc varbinary(max)

But, I get this error :
Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.


Any suggestions  without recresting the table as this is live.


Cheers
0
I have a field like this with values like this:  NJ001013205.  there are all the same length starting with NJ and then the numbers.  how would I get the mad field + 1

so, if the max value was this  NJ001013205 I would return  NJ001013206
0
Hi,

I have this page: http://kroweb.dk/gfdev/canvas/
What I want to do is to create a DB query from clicking a selection, and where the query is the text in the text field when hovering (myText). But I seem not to be able address "myText" correctly - because I actually don't get a query. it displays the headline but not anymore. What am I doing wrong?

I have this relevant HTML:
    <div class="w3-col w3-mobile" style="width:50%; margin-top: 0px; margin-bottom:0px; padding-bottom: 0px; padding-top:100px; border: solid 0px #cccccc;"> 


        <div id="AmtsKort">
            <?php include('amter3.php');?> 

        </div>      

        <div class="w3-row"> <!-- for content inside this column -->

        </div>
    </div>
    <!-- Column1, end-->

    <!-- Columns2, start--> 
    <div class="w3-col w3-mobile" style="width:45%;"> 

        <div id="sogne_list"> </div>

</div>

Open in new window



I have this JS in the amter3.php file:
<script>  

var allPaths=document.getElementsByTagName('path');
var rectText=document.getElementById('rectText');
var myText=document.getElementById('myText');
var hrefLoc='https://kroweb.dk/gfdev/canvas/'; <!-- Denne justeres -->



for(var i=0;i<allPaths.length;i++){
    allPaths[i].onclick=function (e){ 
    /* Here I need that clicking a selection fires a query in the DB*/       
$.ajax({
       url: 'ajaxSognHtml.php',
       data: {query:myText.value},
       method:'POST',
       dataType: 'html'
     }).done(function (res){             
            $('#sogne_list').html(res);
          });         

    };
    
}




for(var i=0;i<allPaths.length;i++){
    allPaths[i].onmouseenter=function (e){
    rectText.style.fill='#cc9900';
    myText.innerHTML=this.id;
    };
    
}
for(var i=0;i<allPaths.length;i++){
    allPaths[i].onmouseleave=function (e){
    rectText.style.fill='none';
    myText.innerHTML='';
    };
    
}

Open in new window


And I have this php db query code in the ajaxSognHtlm.php file:
           
<?php
if ((isset($_POST['myText']))) {
  $con=mysqli_connect("db credentials") or die("Error connecting to database: ".mysql_error());

  mysqli_select_db($con,"genealogiskforum_dk_db9");
  mysqli_set_charset($con,"utf8");
  ?> 
  <br>

  <?php
  $query = trim($_POST['myText']); 
  $raw_results = mysqli_query($con, "SELECT `Sogn`, `URL`, `Amt` FROM sogne
    WHERE (`Amt` = '$query')") or die(mysql_error());
  var_dump($query);
    ?>

    <div class="sognecont" style="font-family:arial; font-size: 14px; font-weight:normal;"><h3>Sogne:</h3> 
      <?php
      while($row = $result=mysqli_fetch_array($raw_results)) {
        $txt= sprintf ("<a href=\"".$row["URL"]."\"target=\"_blank\">" . $row["Sogn"]."</a>"); 
        $txt1= sprintf (", ");
        echo $txt;
        echo $txt1;
    ?>
</div>
    <?php

      }

      mysqli_close($con);
    }else{  print ("POST is not set");
  }

  ?>

Open in new window

0
Hi, I need install sql server express on domain controller.
The propose is install ADMT 3.2.
the installation finish, but cannot start the service.
Any idea?
0
Hi experts,
I need to reproduce this query in CodeIgniter, using ActiveRecord.  I've got very close but CI is putting backquotes where they are not wanted (the yellow-highlighted ones).
Here's the query and CI's interpretation of it...
query as interpreted by CIand here's the code I used to get it.  Part of this stems from CI's lack of support for UNION.
public function get_squad ($fixture_id, $side)
	{	
		$team_clause = 'X';
		if($side=='1') {$team_clause = ' p_1sts ';}
		if($side=='2') {$team_clause = ' p_2nds ';}
		if($side=='3') {$team_clause = ' p_3rds ';}
		if($side=='4') {$team_clause = ' p_4ths ';}
		if($side=='S') {$team_clause = ' p_sundays ';}
		if($side=='T') {$team_clause = ' p_sundays_res ';}
		if($side=='V') {$team_clause = ' p_young_vets ';}
		if($side=='W') {$team_clause = ' p_senior_vets ';}
		
		$this->db->select('first_name, surname, id AS "player_id", "Y" AS "player_picked", fp_keeper AS "keeper", fp_owes_subs AS "subs", fp_if_fit AS "fit", fp_if_available AS "available"');
		$this->db->from('fixtures_players');
		$this->db->join('players_members', 'fp_player_id = id', 'INNER');
		$this->db->where('fp_fixture_id', $fixture_id);
		$this->db->group_by(array('first_name', 'surname'));
		$first_half_inner_union = $this->db->get_compiled_select(); 		
		
		$this->db->select('first_name, surname, id AS "player_id", "N" AS "player_picked", "" AS "keeper", "" AS "subs", "" AS "fit", "" AS "available"');
		$this->db->from('players_members');
		

Open in new window

0

Query Syntax

49K

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.