Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Hi Experts,
I'm updating a field in my table with a dropdown, which is populated from a different table:
This is the dropdown:

<select name="DestQueueId">
<%
Dim rsName
set rsName=Server.CreateObject("ADODB.recordset")
sql="SELECT distinct(Name), QueueEnvironmentId FROM QueueEnvironment order by Name"
Set rsName = AdoCon.Execute(sql)
%>
<option value="">Change Environment:</option>
<%
Do While Not rsName.EOF
Response.Write "<option value='" & rsName("QueueEnvironmentId") & "'>"& rsName("Name") &"</option>"
rsName.MoveNext
Loop
Response.Write "</select>"
%>
<input type="Submit" name="Submit" value="Submit" />

Open in new window


After I submit, the DestQueueId column is updated in my table with the value of QueueEnvironmentId.
QueueEnvironmentId is an integer.
Name is a string.
When I view  <%=rs("DestQueueId")%> on my updated page, the integer is displayed.
How could I have the Name string displayed instead?

I would appreciate your help.
0
Receive 1:1 tech help
LVL 11
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

I am creating a table and I don't want duplicates in the id_user field.  I am using below SQL statements but I can still add duplicate id_users.

CREATE TABLE SYS_PromisedDateNotif (
	pdf_id	INT	NOT NULL IDENTITY(1,1) PRIMARY KEY ,
	id_user	INT NOT NULL,
	CONSTRAINT FK_id_user FOREIGN KEY (id_user) REFERENCES BASE_User(id_user),
)

Open in new window

0
Hi,
How Do I find the string has an apostrophe, single quote in the string.
I have a function
 Public Function ReplaceSingleQuote(ByRef val As String)
        '//***This function replaces the single quote to double quote***\\
        If IsNothing(val) = False OrElse val <> String.Empty Then
            val = val.Replace("'", "''")
            val = val.TrimStart
        End If
        Return val
    End Function

Open in new window

But, the problem here is that if use for multiple times for the same string it inserts too many single quotes.
Is there any way it just detects an single apostrophe and then replaces it with '' for sql compatibility.
Thanks
0
In ms sql

delete table1 from table2 where table1.field=table2.field

Works ok

How can I do the same for 2 tables in mysql

Any ideas
0
Hi Guys

I'm using the CASE function in the query below.

 SELECT
  from_unixtime(b.`date_create`) AS 'Booking  Date'
  ,from_unixtime(b.`timeslot`)   AS 'Allocated Date & Time Slot'
  ,b.`minutes`                   AS 'Minutes'
  ,b.`voucher_title`             AS 'Product Name'
  ,b.`customer_name`             AS 'Customer Name'
  ,from_unixtime(c.`birthdate`)  AS 'Customer DOB'
  ,c.address                               AS 'Address'
  ,c.city                                   AS 'City'
  ,c.postal_code                         AS 'Post Code'
  ,c.email                                 AS 'Customer Email'
  ,b.`customer_num`              AS 'Guest Number'
  ,b.`invoice_id`                AS 'Invoice Number'
  ,i.total_ht                                AS 'Gross'
  ,i.total_tva                             AS 'Tax'
  ,i.paid_ttc                               AS 'Total Received'
  ,i.origin                                     AS 'Bookig Method'
  ,(CASE WHEN b.checkin <- 1 THEN "Un-Finalized"
         WHEN b.checkin > 1  THEN "Finalized"
    END)                         AS Finalized
 
  FROM `booking` b
  JOIN customers c ON c.customer_num = b.customer_num
  JOIN invoices  i ON i.id                   = b.invoice_id

The table "checkin" has either a 0 or a 10 digits, such as 1515663909. What I'm trying to achieve is below

IF b.checkin < 0 THEN Un-Finalized
IF b.checkin > 0 THEN Finalized

I'm able to run the query above but I don't feel the CASE function works, any ideas?

Thanks All!
0
Hi experts,
How can I  differentiate oracle error between connection error and other errers in vba.
I tried the code bellow. But not only when internet cable is disconnected but also when sql syntax error happened , the code show ORACLE CONNECTING ERROR.

Public Function TEST() As Boolean
    Dim strSql As String
    Dim oraDS As OraDynaset
    On Error GoTo ERR
    strSql = ""
    strSql = " select COLUMN from TALE "
    Set OraRec = New ADODB.Recordset
    OraRec.Open strSql, OraCon, adOpenKeyset, adLockReadOnly
       
    Exit Function

ERR:
    If OraCon.State <> adStateOpen Then
        MsgBox "ORACLE CONNECTING ERROR"
    Else
        MsgBox "ORACLE SQL SYNTAX ERROR"
    End If
End Function
0
I have been trying to figure out how to bind sql data to a radCombo box that is nested within a radGrid's automatic Edit Form.
Oddly enough, I was able to figure out how to "cascade" the radCombo boxes Department & ActivityNum as shown in the screenshot.  What I have not figured out is how to initially bind ActivityNum onDataBound.  The exact problem I am having right now is that the following line results in cboActivityID=Nothing resulting in the errors in the screenshots below...

Dim cboActivityID As RadComboBox = e.Item.FindControl("cboActivityNum")

editForm
ss.PNG
error.PNG
.aspx
<telerik:RadGrid ID="grdDetail" runat="server" CellSpacing="0" DataSourceID="sqldsDetail" DataKeyNames="TID" GridLines="None" Skin="Telerik" Height="350px" AllowSorting="True" AllowAutomaticUpdates="True" >
            <GroupingSettings CollapseAllTooltip="Collapse all groups"></GroupingSettings>
        <ClientSettings>
            <Selecting AllowRowSelect="True" />
            <Scrolling UseStaticHeaders="True" AllowScroll="True"/>
            <Resizing AllowColumnResize="True" />
        </ClientSettings>
        <MasterTableView AutoGenerateColumns="False" DataSourceID="sqldsDetail" DataKeyNames="TID" EditMode="EditForms" AllowAutomaticUpdates="True">
            <RowIndicatorColumn Visible="true">
            </RowIndicatorColumn>
            <ExpandCollapseColumn Created="True">
            </ExpandCollapseColumn>
            

Open in new window

0
I am trying to write a simple query to show me my "groups" of PC's I have in an Access database. I have a table called PC_EQUIP with 4 fields that I need: type, model, warranty and serial. This query works fine:

SELECT DISTINCT Type, Model, Warranty FROM PC_EQUIP

This shows me the information I mostly need since we order PC's in groups so the type, model and warranty date of each group will be the same and this query shows me the different groups that I want to see.

I really want to add two more pieces of information. Even though I am using DISTINCT, I would like to see one of the serial numbers for each DISTINCT group, dont care which. Obviously, if I add serial to the query above, I get a list of all records and the "DISTINCT" is useless. I would also like to add a COUNT to the query so it will show me how many rows are rolled up in that DISTINCT clause effectively showing me how many PC's I have on hand for that group,

The serial number part is optional but I cant seem to figure out how to make it return the count for each grouping.

Thanks
0
Hello-   I am using a IN SELECT statement  to add a date to my query in MSACCESS,  In (select AD from Admit_Date). Where AD is a field from the local table Admit_Date.

I want to call a date range from two local tables  the two statements I have is  In (select AD from Admit_Date)   and

I tried something like these, but it does not  seem to work:

Between (In (select AD from Admit_Date) and (In (DC from Discharge_Date))
or
Between In (select AD from Admit_Date) and In (DC from Discharge_Date)
 

Is there a better way?
0
Hi all,
is there a documented process to do replication with log shipping where the only thing available to the destination database is the log files?  If not I will have to setup an SSIS package to do restores of the log files to the destination database, which seems problematic?

thanks,
john.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 5
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

It is for a entry level server. Windows Server 2016 Standard. 26 users (don't want Essentials LOL!). They don't use SQL or any other data intensive apps. Need maybe 1TB usable storage but let's be safe and say two. So what would you recommend as far a fault tolerance and type of drives in this day and age?
0
I'm trying to monitor counters on my SQL server(2008) from my Windows 10 laptop.
Here's what I did:
  1. I set up a User Defined data collector, added counters(via my laptop)
  2. Created a database on my laptop via SSMS
  3. Created an ODBC entry to my new database(System DSN)
  4. Went to the collector and changed the Log Format to "SQL"
  5. hit the dropdown for "Data Source Name", expecting my new dsn to appear...
Nothing appears in the dropdown.
I tried recreating the Data Collector, and also a User Defined DSN

Thoughts?
Rich
0
I'm using the code below and it's in a never ending loop. Thoughts

Public Function Import_System_Access_Reports()

Dim strFolder As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFile As String
Dim strTable As String
Dim strExtension As String
Dim lngFileType As Long
Dim strSQL As String
Dim strFullFileName As String
Dim varPieces As Variant

 With Application.FileDialog(4) ' msoFileDialogFolderPicker
     If .Show Then
         strFolder = .SelectedItems(1)
     Else
         MsgBox "No folder specified!", vbCritical
         Exit Function
     End If
 End With
 If Right(strFolder, 1) <> "\" Then
     strFolder = strFolder & "\"
 End If
 strFile = Dir(strFolder & "*.xls*")
 Do While strFile <> ""

     lngPos = InStrRev(strFile, ".")
    strTable = "RawData" '<- this could be a constant instead of a variable
Set db = CurrentDb()
' make the UPDATE a parameter query ...
strSQL = "UPDATE [" & strTable & "] SET FileName=[pFileName]" & vbCrLf & _
    "WHERE FileName Is Null OR FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)

strFile = Dir(strFolder & "*.xls*")
Do While Len(strFile) > 0
    varPieces = Split(strFile, ".")
    strExtension = varPieces(UBound(varPieces))
    Select Case strExtension
    Case "xls"
        lngFileType = acSpreadsheetTypeExcel9
    Case "xlsx", "xlsm"
        lngFileType = acSpreadsheetTypeExcel12Xml
    Case "xlsb"
        lngFileType = acSpreadsheetTypeExcel12
    End Select
    strFullFileName = strFolder & 

Open in new window

0
I've got two related Oracle tables: (1) WORKORDER and (2) ASSIGNMENT. The WORKORDER table has two attributes: (1) WONUM and (2) PARENTWONUM. If the PARENTWONUM has a value in it, that means that the record is a child of another WORKORDER. So, in that case, WONUM is the child and PARENTWONUM references the parent WORKORDER. One WORKORDER can have many children. The attached word document does a better job of illustrating this.

The ASSIGNMENT table has two attributes: (1) WONUM and (2) ASSIGNEE. The relationship between WORKORDER and ASSIGNEE is one to many (there can be 0, 1, or many ASSIGNMENT records for a single WORKORDER). Again the attached document does a better job of illustrating this.

I need to construct a query that returns who is assigned to which workorder. The attached document shows a sample result set.

The "rub" here is that this has to be done via sub selects instead of joins. I'm able to do the SQL for Work Orders and their children but adding in the ASSIGNMENT table is a bit to complex for me. For example:

select wonum, parent from workorder
where
      wonum = '45296' or wonum in (select wonum from workorder wo2 where (wo2.wonum = '45296' or wo2.parent = '45296'))

That would get me work order 45296 and each of its children but I'm not sure how to get a subselect in there for the ASSIGNMENT table.

Let me know if you can help me.

Thanks in advance
ASSIGNMENTS.docx
0
I have written the below code, which works fine until it has to pass the sql code to the query definition (CurrentDb.QueryDefs(qry2).sql = sql), as then I get a "System Resource Exceeded" error.   In the example I am testing, the number of OR clauses in qString will be 131.  Any ideas?

Thank you.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Public Function ImportServicePOs()
On Error GoTo ImportServicePOs_Err

'This function imports the spreadsheet with te POs
Dim tbl As String, fd As FileDialog, fn As String, qry As String, tbl1 As String, tbl2 As String, db As DAO.Database, rst As DAO.Recordset, qString As String, qdExtData As QueryDef, qry2 As String, pos As String, sql As String
tbl = "tbl_TempPos"
tbl2 = "tbl_PoNumbers"
qry = "qry_ServicePosWithVendorDetails"
qry2 = "qry_ServicePosWithVendorDetailsPQ"
tbl1 = "tbl_ServicePosWithVendorDetails"
Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

MsgBox "Browse for the PO file to import...", vbOKOnly, "Attention!"

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        fn = fd.SelectedItems(1)
    End If
Else
    'Exit code if no file is selected
    End
End If

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, tbl
DoCmd.TransferSpreadsheet acImport, , tbl, fn, -1

Set rst = …
0
I have 2 columns in SQL table.

Combine or concatenate these 2 columns and the output should be in single column but in row order. See attached.

Please help.

Thank you for your help.
Concatenate2ColumnsIntoOneButInRowO.xlsx
0
Hi ,
  I have an Sql Server database with table Documents
  There are many duplicates rows with same Document_No  . But I need to get only one row from each document no

From the following sample data , I am looking for a query

   Doc1 Proj1 Path1 ......
   Doc1 Proj1 Path2
   Doc2 Proj1 Path2
   Doc3 Proj1 Path3

Query Should return rows with  Doc2 and Doc3 and one of the Doc1 row (it does not matter which one)
0
I have a table called myUsers with 1000 UserID's.  This table is the result of a query.  

I have another table called EmailSent that records every email sent to a UserID and which EmailTemplate was used.

How do I get the subset of those 1000 myUsers that have not yet received  EmailTemplate = "Template1.cfm"

This is so I can send the remainder of the UserIDs, the email.

I could do this with spaghetti code but prefer to do it within a CFquery tag.
0
Hi experts,

I'm using sql server 2008.

I'm using the Employees table from the Northwind sample database.

Query 1A

Filter by EmployeeID

I have a query that works fine, it looks like this:

DECLARE @EmployeeID INT
SET @EmployeeID = -3
SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]
WHERE ( (@EmployeeID = -3) OR ([EmployeeID] = @EmployeeID) )

Open in new window


I'm using -3 to display all records.
When I set the @EmployeeID parameter to -3 the query returns all records like this:

p1.PNG
When I set the @EmployeeID parameter to 3 the query returns all records for EmployeeID of 3 like this:

p2.PNG
Query 1B

Filter by EmployeeID

I'm practicing writing dynamic sql queries.
So then I wrote this query. It works just fine. It gives the same results as Query 1A.

DECLARE @EmployeeID INT
SET @EmployeeID = -3

DECLARE @mSQL as varchar(5000)

---------------- Select ----------------

Select @mSQL = 'SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]' 

Select @mSQL = @mSQL + ' Where ( ([EmployeeID] = ' + RTrim(@EmployeeID) + ') Or (' + RTrim(@EmployeeID) + ' =  -3) )' 

---------------- Select ----------------

Exec(@mSQL)

Open in new window



Query 2A

Filter By LastName

So now I want to do the same query as Query 1A but filter by LastName.
I wrote this query and it works fine.

DECLARE @LastName VARCHAR(30)
SET @LastName = 'ALL'
SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]
WHERE ( (@LastName = 'ALL') OR ([LastName] = @LastName) )

Open in new window


I'm using the string 'ALL'  to display all records.
When I set the @LastName parameter to 'ALL' the query returns all records.
When I set the @LastName parameter to 'Leverling' the query returns all records with LastName 'Leverling.


Query 2B

Filter By LastName

So now I have the query that looks like this.
The results of this query should be the same as the results of Query 2A. But I'm getting an error.  
In my where clause where i set ALL i'm not sure of the syntax to use for a string.


Open in new window

0
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

We are using MS SQL 2012R2.  One of our tables has a field which is varchar(MAX), we are changing it to nvarchar(MAX) in order to allow non-latin characters to be entered.

We already tested and worked fine. My question is:  For all the entries we already have in that varchar field, will they be affected in ANY way if we change the field to nvarchar?
0
How can i do random balance in ms access? as like attached file
Bank-Statement.xlsx
0
Hi,

How do I join these two databases?

db1: Visitors
Table: Users
This table contains all users on the system.  each one has a unique "UserID"


db2:  PT_UserData
Table: Client_Scores
This table contains only those users who have completed a test
If they complete a test, the field "YourType" IS NOT NULL
All rows contain the field "UserID"


Both tables have "UserID" in common

I'm looking to output those users who have completed the test, but I need their Email and LastName which is in the Users table
0
UPDATE_structure_code
SET ORGANIZATION = 'IT'+'&'+'DA'
WHERE ORGANIZATION = 'IT$DA';

I am getting error on the set statement - what is the proper syntax to include the ampersand in my text?
0
I have a vb.net App attached to a SQL MDF database.  Inno Script supports .net 45  The database is version 852.(2016+) MsSQLLocalDB will only run version 782database.   since inno script doesn't check for or install dotnet46, or the local db version, how do either:
ADD the new files to connect to this database or
Is there an existing script for the new version of dotnet.

OR should i consider a different installer.  This is not an area of my expertise and this will be installed on many different machines as a local APP.   I could use some advise.  Thanks
0
Hi

What is the most performant way to select the min and max value

here is my query

select (min odometer) and (max odometer) 
From MyTable
Where gpsDateTime between '2018-01-01' and '2018-01-17' and eventID = 1

Open in new window


i basically need  to return 2 item, the max odometer and min odometer reading based on the datetime
I'm looking for the most efficient SQL that can achieve this

SQL server 2014
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.