Query Syntax

52K

Solutions

20K

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

Hello,

I'm working on a python script to generate SQL CREATE TABLE statements from a list of delimited files.  
I often receive data in delimited format with each field surrounded by double-quotes to prevent issues importing when someone has typed the delimiter in a text field.

Here's a simple example of this:

ID|Text1|Text2|Year
"123"|"more data"|"Here is a pipe symbol | typed by an end user"|"2014"

In this example, the end user has typed a pipe symbol into a text field.  The double quotes are intended to prevent this row from resulting in 5 columns.  

I'm using the Python csv Sniffer module to deduce a "dialect"; specifically what delimiter is used and whether fields are quoted (text-qualified) as above.

Sniffer is correctly identifying the pipe delimiter but I think it's not giving the correct value for "doublequote".  
Here's what sniffer is returning for a dialect:
['delimiter', 'doublequote', 'escapechar', 'lineterminator', 'quotechar', 'quoting', 'skipinitialspace']
['|', False, None, '\r\n', '"', 0, False]

It is getting the delimiter, the line terminator, and I think the quotechar is correct.  
I'm thinking 'doublequote' should be True.  

Any input on this will be greatly appreciated.  

TIA!
0
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Hi,
I have a report on which there is a control called CtrlCode, if the information in this control ends with an X, I need the information already in control AccessTotalsSumOfWeight to be copied in control Total_X
I tried the following formula:
= IIF([CtrlCode] Like "*X","[AccessTotalsSumOfWeight]"
I get a mismatch

Any help would be greatly appreciated
Thanks
Jeannie
0
Hi,
 Please find attached Image.
My query
I have Created Data warehouse. For example
Dimension Table : DimSite, DimCustomer,DimSalesperson
FactTable : FactinvoiceDetails

DimSite is Include

SiteID    Sitename

1            United Kigdom
2           USA
3           France

DimCustomer
SiteID       CustomerID CustomerName
1                R001             Ryan
2                R001             Bryan
3                R001            Stephen

DimSalesperson
SiteID       SalespersonID      SalespersonName
1                S001                       Mark
2                S001                       Stacy
3                S001                       Franck


My question is

I have to connect DimCustomer and DimSalesPerson to DimSite Table also
FactTables.png
0
I'm trying to get a count of all SQL tables that start with "comp", but I also want to filter out the count to only records inside these tables that have "Type" column > 0 (some are null, I don't want to count them), and "List" column should be NULL. The query below will get me as far as the count by table name, but I can't figure out how to update the query so it filters the count based on the values as well.

SELECT t.name, s.row_count from sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
AND t.type_desc = 'USER_TABLE'
AND t.name like 'comp%'
AND s.index_id IN (0,1)
0
Hi , I would like to load data from 3 different SQL Command (3 different queries) in 3 different excel files in one folder with each excel file with Date time feature in One package .
I tried this solution however getting issue as It wont open mapping window for 2nd Excel destination. However 1 excel file is loaded successfully., Your help is appreciated

Error is:

Excel destination: Opening a rowset failed. Checked that objects exists in Database.
0
I am importing from excel to ms sql table. I keep getting   in all my empty fields. Can someone help me to fix this so that the empty fields are just empty? All the data is correct just the blank fields.
0
Hello Experts,

I wish to make a query like the attached image. I want to Sum the [Total] field for each record in the parent table "tblEOPs". I think this can be achieved with nested SELECT's. I just don't know how. Txs in advance.
Capture.PNG
0
Good morning,

I've been playing with integrating Python into our data processing workflow.  We are a MS SQL shop (2008-2012) and it's served us well for years and I think it will remain the core of our workflow but I would like to start inyegrating Python and specifically Jupyter Lab and Jupyter Notebooks due to the ability to integrate code, documentation, and visualization of output in a single "document" format.

The other reason I'm considering integrating python into our process is because, for me, the native tools provided for importing and exporting data to/from SQL haven't been intuitive for me and so I'm looking to python as an alternative.  I know the native tools in SQL Server are incredibly powerful and could do everything I need and more - they just seem cumbersome to me and so I'm looking for an alternative.

Man - get to the point already right?  :)

The main goal is to use python connecting through pyodbc to create a database on a SQL Server (2008/2012) instance, read in a series of delimited text files (e.g., pipe | or comma separated, sometimes text quoted sometimes not) and import the files into tables on the SQL Server instance and perform any required transformations like date conversions or numeric coding of text values etc (executing SQL from Jupyter notebook cells).  

I've got the pyodbc connection down and I've managed to execute commands to create databases and tables.  What I'm looking for are code "templates" that others may have used …
0
I am trying to migrate a MSSQL database to MySQL. I tried the migrate feature of MySQL Workbench, but it does not work. Even though the connection to MSSQL tested without a problem, the migration program does not find any schemas to import.

I then created a SQL file using SQL Server Management Studio and tried to import it using myphpadmin. It also failed, even though the compatibility was set to mssql.

Can someone help me move this database? I don't care how. Either to help me figure out what is going wrong with the above two methods, or introduce a third method.

Thank you.
0
How can I create an encrypted ODBC connection to sql server using the Windows  10 DSN wizard?  If I check the option  "Use Strong Encryption for Data", then the connection fails.  If I uncheck the option, then the connection is fine.  Do I have to have a certificate on the server?    Is there a setting on the server I have to change to make the connection work?
None of what I googeled on this makes sense.
0
Rowby Goren Makes an Impact on Screen and Online
LVL 12
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Hi,

I have an issue with a chart not updating.
The structure is:
A main form (frmMain)
A subform in the main form (subfrmAge) - It contains a crosstab query (XAge)
A chart in a subform (subChartAge) - The record source is (XAge)

The problem is that when I filter XAge with a button on my main form, the values from subChartAge is updated, however the chart isn't.

I've tried adding
        
    Me.Requery
    Me.Refresh
    Me.ChartAge.Requery
    Me.ChartAge.Requery

Open in new window

However, the chart is not updating with the query. Why is this happening?
ChartNotUpdating.png
0
I need to create one query to run every day , using Microsoft Access that finds records from the previous work day. Tues to Friday, the previous work day is date()- 1. Simple enough.  But on Monday, if I use date()-1, it will miss records from that previous Friday or Saturday.   its an iif statement  but I need help with the rest please.
0
Hi,

I'm using SSIS 2008.

I have 2 ssis packages.
QUESTION:
1. can packageA call packageB ?   How (please show example code)?
2. Will packageA task that executes packageB wait until packageB is completed before packageA goes on to process a subsequent task?

Thanks.
0
Hi.
I have a weird issue running a SQL proc that returns a picture as a blob.

My laptop, LAP1, is on our corporate domain "CORP", and I am a domain user "CORP\bgood".
On that domain is a MS SQL Server "SQL1".   SQL1 runs a database DATA1.
I also have a SQL Login on SQL1 called "good_b".

There are picture files on a domain server FILE1, which LAP1 and SQL1 seem to be able to access, using File Explorer from each machine.  Both have the the picture folder mapped as "L".

From LAP1, I connect to DATA1 using SQL Server Management Studio with Windows Authentication (CORP\bgood).  Executing the procedure (similar to code below) returns a row but the Pic1Blob column is NULL.   HOWEVER -- Using  SQL SERVER authentication (good_b), the row returns nicely with the picture in Pic1Blob!

This seems backwards to me -- I would have expected Windows Authentication to work since the Windows Domain user bgood has access to the domain files.

ALSO -- If I log on to the actual SQL1 machine as "CORP\bgood" and connect to DATA1 using Windows Authentication, I can run the procedure fine, with data in the Pic1Blob column.  So, I am thinking there may be some sort of NT SERVICE or NT AUTHORITY permissions issue.

I hope I've described this right.  Any suggestions?     I can give you any more information you may need.
Thanks!
Rob

BEGIN TRY	
	CREATE TABLE #Temp1(Pic1Blob VarBinary(max))

	DECLARE @PathToPhoto varhar(50) = 'L:\Images\MyFace.jpg'
	DECLARE @SQL 

Open in new window

0
i recently had a backup job go dormant for a few weeks and didn't notice the problem soon enough.  is there a strategy that someone might point me to that is not overly complex, but its pretty rock solid as far as making sure your backups are working properly.

all the backup jobs and maintenance are running through ssms maintenance plans.

i have 3 maintenance plan set ups.
Backup full
Backup differential
and one called "maintenance" - this one cleans out old back up files

on the email notifications on the sql agent job... question.
Look at attachment 1 for reference.  I have this set to "when the job completes".  does this mean it will fire off an email when the job succeeds or fails?

2 follow up questions.

i want to test this job to see what it does when it fails.  i see what it does when it completes successfully.  is there an easy way to fire this backup job off and make it fail... a certain setting i should try or something like that.

follow up question 2.  i would like to be immediately notified if the sql agent service turns off... best way for this?
Attachment1.JPG
0
I have a table having fields as Country,Product,Years,Value. I want to subtract Values of two product having same Country and Years.

Below is a sample data:

ID      Country      Years      Product      Values
1      4                 2010      CO                50
2      4              2011      CO               200
3      4              2010      TO               50
4      4             2011      TO              122

Desire Result:
Country      Years      Product      Values
4             2010      CO-TO             0
4             2011       CO-TO         78
...
Thank you.
0
We've split our floor plan project in 2 parts:

1. FP Builder
2. FP Viewer

For FP Builder we've used SVG Edit (https://github.com/SVG-Edit) and for FP Viewer we've used MAPPLIC FP Viewer (https://www.mapplic.com/plugin/docs/).

Currently in FP Builder when we do changes it gets saved in svg file. And we load the same svg in Viewer. We need to save svg shapes in database and retrieve svg from database. E.g. If there are 10 stands present in our floor plan there should be 10 database rows in database table. So that we have connectivity between our FP Builder and database.

And we need to add split and merge stand functionality for stands.
0
I need to create a query that calculate the car average mileage  per day. I have the query but it is calculating the average per ride not per day. Because you may have multiple rides per the same day and tat will be the sum not the average for that day


SELECT carkey , TotalMilesDriven , ISNULL([Monday],0) [Monday] ,
			ISNULL([Tuesday],0)[Tuesday],ISNULL([Wednesday],0)[Wednesday],ISNULL([Thursday],0)[Thursday],ISNULL([Friday],0)[Friday]
			,ISNULL([Saturday],0)[Saturday],ISNULL([Sunday],0) [Sunday],carnumber,model,make FROM 
			(
				SELECT DISTINCT model,make,carnumber,carkey, _WEEKDAY , AVG(milesDriven) OVER() TotalMilesDriven, AVG(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
				FROM 
				(	
					select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY,C.carnumber,C.model,C.make
					from car_details CD
					inner join Car C on C.carKey = CD.carKey
					inner join Users U on U.userKey = CD.updateuser
					inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
					WHERE 1=1
					and CD.carKey = 32
					and SU.sessionStart BETWEEN '09/14/2018' AND '09/15/2018'
				)AS T
				WHERE T.milesDriven > 0
			)k
			PIVOT ( MAX(MilesPerCarWeekDay) FOR _WEEKDAY IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) )y1

Open in new window

0
Hi Experts,

I'm getting the following error when trying to import an Access table to SQL Server (2008).

- Copying to [dbo].[Skilled_Nursing_Visit_Note] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid date format".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with input column "Date_Of_Birth" (382) on input "Destination Input" (284). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (284)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (284)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Skilled_Nursing_Visit_Note" (271) failed with error code 0xC0209029 while processing input "Destination Input" (284). The identified

Open in new window

0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi,

Many thanks for taking a look at my question.

Given the below SQL query that works

;with cte as
(
      select tr.tr_rowid_debtor as lp_rowdebtor_id, tr.tr_posted_date,
      (0-tr.tr_to_agency+tr.tr_to_client) as amount,tr.tr_account,
      ROW_NUMBER() over (partition by tr.tr_rowid_debtor order by tr.tr_posted_date desc) idx
      from de_transaction as tr
    where tr.tr_account = 16
)
Select d.de_number, lp_rowdebtor_id, tr_posted_date, amount, tr_account 
from cte 
inner join debtor as d on d.de_rowid  = lp_rowdebtor_id
where idx = 1

Open in new window


I need it to do exactly what it does above but also - 1 additional item I need, is that somehow I need a count of the each transaction it finds per by "tr.tr_rowid_debtor" and only give back a result for the files that have multiple instances of where tr.tr_account = 16.

many thanks!!
0
Dear Experts,

 How do I know in Oracle SQL for specific table when ( the time ) it was last updated or changed ?

Thanks
0
I have written a procedure to update SSRS subscription schedules. The procedure appears to work because the values are updated in ReportServer.dbo.Schedule. However when I view the schedule through SSRS Report Manager, the dates are not updated.

The code I am using is below -

declare 
--@SubscriptionID varchar(100) = 'E65E6D4A-A631-4C17-A8DB-001FB0AD0855'
@Path varchar(max) = '/QTS Internal/RTM/Service Disruption Report'
, @SPLC varchar(max) = '123456'
, @State varchar(max) = 'VA,NC'
, @Country varchar(max) = NULL
, @Carrier varchar(max) = NULL
, @Location varchar(max) = NULL
, @CustomerID varchar(max) = '1'
, @Disruption varchar(max) = 'Hurricane Florence'
, @StartDate datetime = '20180914 04:19:00'
, @EndDate datetime = '20181002'

create table #subs (SubscriptionID varchar(200), [Path] varchar(200))

insert into #subs

select s.SubscriptionID, c.Path

from Catalog c
join subscriptions s on c.ItemID = s.Report_OID
where c.Path = @Path

-----------------------------------------------------update schedule dates

UPDATE ReportServer.dbo.Schedule
SET StartDate = @StartDate, EndDate = @EndDate
from Catalog c
LEFT OUTER JOIN Subscriptions s WITH (NOLOCK) ON s.Report_OID = c.ItemID
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] RS WITH (NOLOCK) ON s.[SubscriptionID] = RS.[SubscriptionID] 
LEFT OUTER JOIN ReportServer.dbo.[Schedule] SC WITH (NOLOCK) ON RS.[ScheduleID] = SC.[ScheduleID]
JOIN #subs subs on s.SubscriptionID = subs.SubscriptionID

Open in new window


Does anybody have experience with this?
0
Hi,
How to convert an Integer value to TeXt Value
using DAX
0
Hi Database Experts,

Scenario:
As part of code deployment, we also execute Database DML and DDL. Recently a cursor statement (select / update) badly affected our PPTE environment. Since we have less data in lower environment. we actually couldnt catch this. however it took 3+ hrs in PPTE.

Is there any automated way to check this poor coding when executing in lower environment please?
E.g
  1. Can we add custom sql (as part of post sql on every deployment) to generate profile or gather stats when executing in lower env? So we check this report every time
  2. Anything with non-sys or non-system user. Basically with the user account who executes SQLs.
  3. SonarQ profile
0
Hi,

I want to fetch data from 3 table for time periods , i am using UNION All but its show error.
My sql code is
$sql_search = "(SELECT invoice_amount_wt_gst, cr_note, company, client, invoice_no, job_no FROM psb_billing WHERE invoice_date BETWEEN '$recv_date' AND '$invoice_end_date' and currency = '$currency' and company = $comp)
	UNION ALL
(SELECT on_ac, tds, client, job_no, invoice_no FROM receiving_report WHERE receiving_date BETWEEN '$recv_date' AND '$invoice_end_date' and currency = '$currency' and company = $comp)
	UNION ALL
(SELECT bf_amount, bf_credit_amount FROM psb_clients_bbf WHERE currency = '$currency' and bf_company = $comp)";	

Open in new window



i don't understand where i am wrong. and when i am using join query its show me lots of data. i think Its also fetch the wrong data.
my join query is
"SELECT PB.invoice_amount_wt_gst, PB.cr_note, PB.company, PB.client, PB.invoice_no, PB.job_no, PB.invoice_date, PB.currency, R.receiving_date, R.client, R.on_ac, R.tds, R.currency, BB.clients, BB.bf_amount, BB.bf_credit_amount, BB.currency, BB.bf_company FROM psb_billing PB left join receiving_report R on PB.company = R.company left join psb_clients_bbf BB on PB.client = BB.clients WHERE PB.invoice_date BETWEEN '$recv_date' AND '$invoice_end_date'  ";

Open in new window

 
i attached the table sql file.
I appreciate your's help.
Thank you
0

Query Syntax

52K

Solutions

20K

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.