JSqlparser throw exception when statement contains lateral view
To parse sql statement like :
select id, name, lateral view explode(info) from info_table;
JSqlParser will throw exception:
ParseException:Encountered unexpected token: "lateral" "LATERAL" at line 1.
See also questions close to this topic
How to calculate a GPA in MySql
The query should output the student's name and GPA.
The following tables are given:
CREATE TABLE `Section` ( `ID` int(11) NOT NULL, `Semester` varchar(45) DEFAULT NULL, `Room` varchar(45) DEFAULT NULL, `Instructor_ID` int(11) NOT NULL, `Course_ID` int(11) NOT NULL, PRIMARY KEY (`ID`), KEY `fk_Section_Instructor_idx` (`Instructor_ID`), KEY `fk_Section_Course1_idx` (`Course_ID`), CONSTRAINT `fk_Section_Course1` FOREIGN KEY (`Course_ID`) REFERENCES `Course` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Section_Instructor` FOREIGN KEY (`Instructor_ID`) REFERENCES `Instructor` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Section` VALUES (1,'Fa17','828',1,1),(2,'Fa17','828',2,3),(3,'Fa17','829',1,4),(4,'Fa17','829',4,5),(5,'Sp18','828',1,1),(6,'Sp18','829',1,2),(7,'Sp18','828',3,4),(8,'Sp18','828',4,5);
DROP TABLE IF EXISTS `Course`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `Course` ( `ID` int(11) NOT NULL, `Title` varchar(45) DEFAULT NULL, `Description` text, `Units` int(11) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Course` VALUES (1,'CIS-15','Cloud Programming in Python',4),(2,'CIS-54','Relational Databases',4),(3,'CIS-81','Introduction to Networking',4),(4,'CIS-75','Introduction to Computer Security',3),(5,'CIS-90','Introduction to Linux',3);
CREATE TABLE `Registration` ( `Section_ID` int(11) NOT NULL, `Student_ID` int(11) NOT NULL, `Grade` int(11) DEFAULT NULL, PRIMARY KEY (`Section_ID`,`Student_ID`), KEY `fk_Section_has_Student_Student1_idx` (`Student_ID`), KEY `fk_Section_has_Student_Section1_idx` (`Section_ID`), CONSTRAINT `fk_Section_has_Student_Section1` FOREIGN KEY (`Section_ID`) REFERENCES `Section` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Section_has_Student_Student1` FOREIGN KEY (`Student_ID`) REFERENCES `Student` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Registration` VALUES (1,1,4),(1,2,4),(2,2,3),(3,3,2),(4,1,3),(4,3,3),(5,3,NULL),(5,4,NULL),(6,1,NULL),(6,2,NULL),(7,1,NULL),(7,4,NULL),(8,2,NULL),(8,3,NULL);
CREATE TABLE `Student` ( `ID` int(11) NOT NULL, `Name` varchar(45) DEFAULT NULL, `Email` varchar(45) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Student` VALUES (1,'Steve Inskeep','firstname.lastname@example.org'),(2,'Rene Montaign','email@example.com'),(3,'David Green','firstname.lastname@example.org'),(4,'Rachel Martin','email@example.com');
I tried this code tried this and got nonsense output. I'm pretty lost
SELECT student.Name, (sum( registration.grade * course.units) / sum(course.units) ) as GPA FROM registration join student on registration.student_ID = student.id join section on section.ID = registration.section_ID join course on section.course_ID = course.ID group by registration.student_ID ;
The returned result from @Barbaros Özhan 's suggestion as well as similar solutions of my own is :
'1', 'Steve Inskeep', '1.7857'
as the first returned row.
but clearly from eyeballing the registration table Student #1 does not have a 1.7857 GPA.
Edit: Answered by Gordon Linoff:
select student.Name, (sum( registration.grade * course.units) / sum( case when registration.grade is not null then course.units end ) ) as GPA from registration join student on registration.student_ID = student.id join section on section.ID = registration.section_ID join course on section.course_ID = course.ID group by student.ID ;
How to prove NT AUTHORITY / NT SERVICE SQL accounts cannot be authenticated to?
Auditors are asking me to show them that NT AUTHORITY / NT SERVICE accounts (NT SERVICE\SQLWriter, NT SERVICE\Winmgmt, etc.) in our SQL databases. I was taught that they are service accounts and are non-interactive.
Is there a way I can demonstrate this to them? I've send them the MS documentation on Windows Service Accounts and Permissions and they're saying they want a live demonstration of it.
How can I do this? Is there a flag I can show them?
How to write an SQL query based on properties of the oldest element of a has-many relationship?
Given the following example datamodels/tables: (specified in pseudo-Ruby/Rails for brevity's sake)
class User has_many :orders end
class Order has_and_belongs_to_many :products ordered_at :timestamp end
class Product name :string price :decimal end
(and each of these tables also has a unique 'id' identifier field that is its primary key, and a joins table connecting
I would like to select all users, for which e.g. their oldest Order (with the earliest
ordered_at), contains more than n products.
How can this be done?
What I've tried so far: I tried using a
JOIN, but then we end up looking at all orders rather than only the oldest. I've also tried to use
DISTINCT ONbut I have no idea how to combine this properly with (a) selecting the users and (b) doing a check for the count of all related products.
I am using Postgres, but if a DB-agnostic SQL answer exists, that would be much appreciated as well.
Changing dd/mm/yyyy/ hh/mm/ss format to yyyymm in Hive
I'm using Hive at the moment. I have a column (column A) of strings which is in the following format
11/9/2009 0:00:00. I'd like to extract the
yyyymm. i.e. I'd like the above string to be
200909. I've tried two different methods none of them worked.
I have tried to convert the string using two different methods
concat(year(Column A),lpad(month(Column A),2,0)) convert(datetime, Column A)
For the first row of code I'm receiving : NULL in all rows
For the second one I'm receiving :
Encountered: DATETIME Expected: ALL, CASE, CAST, DEFAULT, DISTINCT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error
How Can I Run Sequential Temp Tables & Final SELECT Query
I'm used to BigQuery where I can run temp tables with the 'WITH' clause and then join those temp tables with a final query. However, I am now using a Hive db via DataGrip where I cannot run sequential temp tables in one query execution. Instead, I have to highlight every temp table block (within one script) and execute then on to the next one, then on to the next one, ... which is very annoying.
Help w/two items:
Does anyone know how I can run sequential temp tables and then finally join them all with a final SELECT statement?
Also, i find that the temp tables are stored in my session and I need to delete them with a simple line of code which is not a thing in BigQuery (again annoying). Can anyone help how I can deal with this? Because sometimes the temp tables can change column names and I don't want to worry about deleting the previous temp table with old columns names.
Here is an example of code:
-- audience temp table CREATE TEMPORARY VIEW aud AS ( 1 SELECT exp_luid FROM audience_manager.segments5_luid WHERE segment_version_id IN (627, 629) ) -- KVJ table CREATE TEMPORARY VIEW prod AS ( SELECT station_callsign, exp_luid, ds, ad_start_ts_utc as ad_time, COUNT(ds) AS impressions FROM vizio_production.kantar_vizio_v4_new WHERE product_id = 36325675 AND ds BETWEEN 20190101 AND 20190430 AND exp_luid IS NOT NULL GROUP BY 1,2,3,4 ) -- Join KVJ and audience data set CREATE TEMPORARY VIEW join_one AS ( SELECT aud.exp_luid AS exp_luid, prod.station_callsign AS network, prod.ds AS ds, prod.ad_time AS ad_time, SUM(prod.impressions) AS impressions FROM aud INNER JOIN prod ON aud.exp_luid = prod.exp_luid GROUP BY 1,2,3,4 ) SELECT * FROM join_one
Final join from select statement 'join_one' without caching the temp tables and running the entire SQL script in one script execution.
Hive explain plan where to see full table scan?
How can I see from hive
EXPLAINis there a full table scan?
So , when i use this software , the output is always no injection found , please help
JSQL Parser - info on dblink
I m using JSQLPARSER for the first time and I wonder if it s possible to detect remote links (oracle dblink)?
If for example a simple
select * from table1@remote
PlainSelect and SetOperationList in jsql parser
what is the difference between PlainSelect and SetOperationList in jsql parser