OraFAQ Forum: SQL & PL/SQL » BASIC SYNTAX / Dynamic Pivot Table (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » BASIC SYNTAX / Dynamic Pivot Table (merged) (PL SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

BASIC SYNTAX / Dynamic Pivot Table (merged) [message #686163]

Tue, 28 June 2022 11:40

Dhoback
Messages: 10
Registered: June 2022

Junior Member

I am new to SQL and just trying to follow instructions I found for a basic Begin-End statement but I am getting a PLS-00103 error. Anyone know what I am doing wrong?


DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Error:

ORA-06550: line 2, column 39:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset
. Native error: 6550. SQLSTATE: HY000
ORA-06550: line 2, column 30:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( % ;
. Native error: 6550. SQLSTATE: HY000
ORA-00900: invalid SQL statement
. Native error: 900. SQLSTATE: 42000
ORA-00900: invalid SQL statement
. Native error: 900. SQLSTATE: 42000

[EDITED by LF: applied [code] tags]

[Updated on: Tue, 28 June 2022 15:24] by Moderator

Report message to a moderator

Re: BASIC SYNTAX [message #686164 is a reply to message #686163]

Tue, 28 June 2022 13:55

John Watson
Messages: 8807
Registered: January 2010
Location: Global Village

Senior Member

It works for me:

orclz> set serverout on
orclz> DECLARE
  2  message varchar2(20):= 'Hello, World!';
  3  BEGIN
  4  dbms_output.put_line(message);
  5  END;
  6  /
Hello, World!

PL/SQL procedure successfully completed.

orclz>

You had better show what you are doing (s I did)

Report message to a moderator

Re: BASIC SYNTAX [message #686165 is a reply to message #686163]

Tue, 28 June 2022 13:55

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

The code you posted is correct, as you can see with the following execution using SQL*Plus:

SQL> DECLARE
  2     message  varchar2(20):= 'Hello, World!';
  3  BEGIN
  4     dbms_output.put_line(message);
  5  END;
  6  /
Hello, World!

The problem is most likely your SQL tool.
The best one to test SQL or PL/SQL code is SQL*Plus.

Report message to a moderator

Re: BASIC SYNTAX [message #686166 is a reply to message #686165]

Tue, 28 June 2022 15:12

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Thanks for your reply. I am currently using SQLdbx program for writing queries.

I already have a program called SQL Plus installed on my computer, but when I open it asked for a username and password and I do not know what those are.

I am not familiar with this command line type program, but if I put in a standard select statement, how does it generate a table in the command window?

Report message to a moderator

Re: BASIC SYNTAX [message #686167 is a reply to message #686166]

Tue, 28 June 2022 15:32

Littlefoot
Messages: 21761
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Until now, I've never heard of SQLdbx. Its online manual is rather poor, doesn't tell much. Apparently, you can run PL/SQL code (that's described in its "bind variables" FAQ).

Errors you got say:

ORA-06550: line 2, column 39:
ORA-06550: line 2, column 30:

Code you posted & its line #2 is

   message  varchar2(20):= 'Hello, World!'; 
01234567890123456789012345678901234567890123
          1         2         3         4
                              ^        ^ 
                              |        |
                              30       39

There's nothing "wrong" in either of these two positions, so ... no idea, really.

As of SQL*Plus: provide the same credentials you used in SQLdbx.

Report message to a moderator

Re: BASIC SYNTAX [message #686168 is a reply to message #686167]

Tue, 28 June 2022 15:37

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Attached is a picture of SQL Plus open. I am confused because I am not even designating a database or anything before it is asking for a password. I tried the same username and password I used for SQLdbx, but it throws an error. Is this the right SQL Plus software?

Attachment: Capture.PNG
(Size: 12.08KB, Downloaded 233 times)

Report message to a moderator

Re: BASIC SYNTAX [message #686169 is a reply to message #686168]

Tue, 28 June 2022 15:43

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

"username" is either an schema name if the database is local, or username@tnsalias if the database is remote.
If the database is local, you must set the environment variable ORACLE_SID to the database name and ORACLE_HOME to the directory where resides your Oracle installation.

[Updated on: Tue, 28 June 2022 15:45]

Report message to a moderator

Re: BASIC SYNTAX [message #686170 is a reply to message #686169]

Tue, 28 June 2022 16:37

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Excellent. Adding the username@tnsalias got me connected to the database and I was able to run the basic "Hello World" example.

Now I wanted to load my entire SQL query into SQL Plus to start some testing, but the query that ran in SQLDbx has an error when copying into SQL Plus. Do I need extra syntax to max SQL Plus work?

Current Code:

SELECT * FROM (
SELECT TO_CHAR(TIME) AS TIME, NAME, SUM(QTY_DONE) AS QTY

FROM
(
SELECT
--"TA_LABOR"."ID",
TRUNC("TA_LABOR"."TIME_OUT") AS Time,
--"PR_EMP"."EMPNO",
CONCAT(CONCAT("PR_EMP"."FIRST_NAME",' '),"PR_EMP"."LAST_NAME") AS NAME,
--"PR_EMP"."LAST_NAME",
--sum("TA_LABOR"."TOTAL_HRS") AS Hours,
--"TA_LABOR"."WORKORDER_ID",
--"ARINVT"."ITEMNO",
--"SNDOP"."OPDESC",
--"SNDOP"."CNTR_TYPE",
sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE--,
--sum("TA_LABOR"."REJECT_QTY") AS QTY_REJECT

FROM ((((("IQMS"."TA_LABOR" "TA_LABOR"

LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID")
LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID")
LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID")
LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID")
LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID")
LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
WHERE trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10) --TO_DATE ('01-01-2022 00:00:01', 'DD-MM-YYYY HH24:MI:SS')
--AND "PR_EMP"."FIRST_NAME"= 'Isela'
AND ("SNDOP"."CNTR_TYPE" = 'C-GRIND' OR "SNDOP"."CNTR_TYPE" = 'C-GRIND TIGHT TOL')
GROUP BY --"TA_LABOR"."ID",
"TA_LABOR"."TIME_OUT",
--"PR_EMP"."EMPNO",
"PR_EMP"."FIRST_NAME",
"PR_EMP"."LAST_NAME"
ORDER BY "TA_LABOR"."TIME_OUT")

GROUP BY TIME, NAME

)

Results:

TIME NAME QTY
28-JUN-22 Sonita Luy 3168
22-JUN-22 Sonita Luy 4008
18-JUN-22 Dora Alvarez 7206
20-JUN-22 Andrea Valdez 6552
22-JUN-22 Sinuon Keo 1572
21-JUN-22 Melissa Sandoval
20-JUN-22 Thomas Salazar 3048
20-JUN-22 Delores Minott
23-JUN-22 Isela Rodriguez 4392
25-JUN-22 Ella Hood 2592
26-JUN-22 Philip Morandarte 7338
22-JUN-22 Melissa Zambrano 2854
24-JUN-22 Ramona Barrios 6048

Report message to a moderator

Re: BASIC SYNTAX [message #686171 is a reply to message #686170]

Tue, 28 June 2022 16:44

Littlefoot
Messages: 21761
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That doesn't contain any errors you mentioned. Anyway: try to remove empty lines, SQL*Plus doesn't like them. For example:

SQL> select empno,
  2  ename
  3  from emp
  4  where deptno = 10;

     EMPNO ENAME
---------- ----------
      7782 CLARK
      7839 KING
      7934 MILLER

vs.

SQL> select empno,
  2  ename
  3
SQL> from emp
SP2-0042: unknown command "from emp" - rest of line ignored.
SQL>
SQL> where deptno = 10;
SP2-0734: unknown command beginning "where dept..." - rest of line ignored.
SQL>

Alternatively, download and install Oracle SQL Developer GUI tool (https://www.oracle.com/tools/downloads/sqldev-downloads.html).

Report message to a moderator

Re: BASIC SYNTAX [message #686174 is a reply to message #686171]

Wed, 29 June 2022 09:15

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Alright, I am really making some progress! This community is really amazing. Now I am trying to get back to what I was originally trying to do which is run a dynamic sql query to populate a Pivot-in-clause with variable data. I am trying to follow the example from here: https://sqlskull.com/2020/06/16/sql-server-dynamic-pivot/ but am still coming across syntax errors. Below is my code so far, but when I try to run it in SQL Plus, I get an error right away at the first @ symbol on the first line. I also had a question about the Set @SQLQuery. The whole thing is one big string, but I have the strings 'C-Grind' and 'C-Grind Tight Tol' inside the string. How do I put a string inside a string? In my SQL software it shows those strings as breaking out of the string format.

DECLARE @colnameList varchar(200)
SET @colnameList = NULL
SELECT @colnameList = COALESCE(@colnameList + ',','') + TRUNC("TA_LABOR"."TIME_OUT")
FROM "IQMS"."TA_LABOR";
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery =
'SELECT * FROM (
SELECT TO_CHAR(TIME) AS TIME, NAME, SUM(QTY_DONE) AS QTY
FROM
(SELECT
'+@colnameList' AS Time,
CONCAT(CONCAT("PR_EMP"."FIRST_NAME",' '),"PR_EMP"."LAST_NAME") AS NAME,
sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE
FROM ((((("IQMS"."TA_LABOR" "TA_LABOR"
LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID")
LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID")
LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID")
LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID")
LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID")
LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
WHERE trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10)
AND ("SNDOP"."CNTR_TYPE" = 'C-GRIND' OR "SNDOP"."CNTR_TYPE" = 'C-GRIND TIGHT TOL')
GROUP BY
"TA_LABOR"."TIME_OUT",
"PR_EMP"."FIRST_NAME",
"PR_EMP"."LAST_NAME"
ORDER BY "TA_LABOR"."TIME_OUT")
GROUP BY TIME, NAME)
PIVOT(
SUM(QTY) FOR TIME IN ('+@colnameList'))'
EXEC(@SQLQuery)
/

Here is the results of the code inside the @SQLQuery that I am trying to pivot:

TIME NAME QTY
28-JUN-22 Sonita Luy 3168
20-JUN-22 Andrea Valdez 6552
20-JUN-22 Thomas Salazar 3048
21-JUN-22 Melissa Sandoval
29-JUN-22 Melaine Ronquillo
29-JUN-22 Sovann Chev
23-JUN-22 Isela Rodriguez 4392
22-JUN-22 Sinuon Keo 1572
22-JUN-22 Sonita Luy 4008

Thanks for any advice!

Report message to a moderator

Re: BASIC SYNTAX [message #686175 is a reply to message #686174]

Wed, 29 June 2022 10:01

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please How to use [code] tags and make your code easier to read.

There this is a whole SQL*Plus and PL/SQL course we have to give you.

DECLARE @colnameList varchar(200)
=> VARIABLE colnameList varchar(200)

SET @colnameList = NULL
=> EXEC :colnameList = NULL;

SELECT @colnameList = COALESCE(@colnameList + ',','') + TRUNC("TA_LABOR"."TIME_OUT") FROM "IQMS"."TA_LABOR";
=> EXEC SELECT COALESCE(:colnameList + ',','') + TRUNC("TA_LABOR"."TIME_OUT") into :colnameList FROM "IQMS"."TA_LABOR";
...

or in a single PL/SQL block:

DECLARE 
  colnameList varchar(200) := NULL;
  SQLQuery NVARCHAR(32760);
BEGIN
  SELECT COALESCE(colnameList + ',','') + TRUNC("TA_LABOR"."TIME_OUT") into colnameList FROM "IQMS"."TA_LABOR";
  SQLQuery := '...';
  ...
END;
/

PL/SQL User's Guide and Reference
SQL*Plus User's Guide and Reference

[Updated on: Wed, 29 June 2022 10:06]

Report message to a moderator

Re: BASIC SYNTAX [message #686176 is a reply to message #686175]

Wed, 29 June 2022 11:29

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Thanks for the training resources. Definitely helped me make a lot of corrections to the code and I think I am getting really close but can't figure out how to actually execute the query I saved as a string. I formatted the code as requested. Hoping someone can help me get over this last hurdle.

DECLARE 
	colnameList varchar(200) := NULL;
	SQLQuery VARCHAR(32760);
BEGIN
	SELECT TRUNC("TA_LABOR"."TIME_OUT") INTO colnameList FROM "IQMS"."TA_LABOR" WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10);
	SQLQuery :=
		'SELECT * FROM (
			SELECT TO_CHAR(TIME) AS TIME, NAME, SUM(QTY_DONE) AS QTY
			FROM 
				(SELECT  
					colnameList AS Time, 
					CONCAT(CONCAT("PR_EMP"."FIRST_NAME",''),"PR_EMP"."LAST_NAME") AS NAME,  
					sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE
				 FROM  "IQMS"."TA_LABOR" "TA_LABOR" 
				 	LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID"  
				 	LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID" 
				 	LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
				 	LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID"
				 	LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID"
				 	LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
				 WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10) 
				 	AND ("SNDOP"."CNTR_TYPE" ='||'C-GRIND'||' OR "SNDOP"."CNTR_TYPE" ='|| 'C-GRIND TIGHT TOL'||')
				 GROUP BY  
					"TA_LABOR"."TIME_OUT",
					"PR_EMP"."FIRST_NAME",
					"PR_EMP"."LAST_NAME"
				ORDER BY "TA_LABOR"."TIME_OUT")
				GROUP BY TIME, NAME) 
		    PIVOT(SUM(QTY) FOR TIME IN (colnameList))';
EXECUTE SQLQuery;
END;
/

Results:

EXECUTE SQLQuery;
*
ERROR at line 30:
ORA-06550: line 30, column 9:
PLS-00103: Encountered the symbol "SQLQUERY" when expecting one of the
following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "SQLQUERY" to continue.

Thanks again!

Report message to a moderator

Re: BASIC SYNTAX [message #686180 is a reply to message #686176]

Wed, 29 June 2022 11:43

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

OK, now one further step. Smile

Generally speaking, to execute a SQL statement contained in a string you need to use the EXECUTE IMMEDIATE statement along with the USING clause.

But this is not your case here.
PL/SQL is not built to interact with the client, it is for retrieving data and computation.
The best you can do here is to declare a CURSOR variable and retrieve the result set of the query in this one which will be handled by the client (in this example, printed):

SQL> var c refcursor
SQL> declare
  2    sqltext varchar2(1000);
  3  begin
  4    sqltext := 'select * from dept';
  5    open :c for sqltext;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print c
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

[Updated on: Wed, 29 June 2022 11:44]

Report message to a moderator

Re: BASIC SYNTAX [message #686181 is a reply to message #686180]

Wed, 29 June 2022 11:48

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Also if you want to use some variables to build your SQL string you have to use the concatenation operator ||:

SQL> declare
  2    sqltext varchar2(1000);
  3    collist varchar2(1000);
  4  begin
  5    collist := 'dname, loc';
  6    sqltext := 'select '||collist||' from dept';
  7    open :c for sqltext;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> print c
DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

4 rows selected.

Report message to a moderator

Re: BASIC SYNTAX [message #686183 is a reply to message #686181]

Wed, 29 June 2022 12:47

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Getting closer! Now I am getting the error:

"ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5"

I thought that since I have the same WHERE statement in both the colnameList query as in the SQLQuery statement, I would get a same size list.

DECLARE 
	colnameList varchar(200) := NULL;
	SQLQuery VARCHAR(32760);
BEGIN
	SELECT DISTINCT TRUNC("TA_LABOR"."TIME_OUT") INTO colnameList 
	FROM "IQMS"."TA_LABOR" LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
		WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10) AND ("SNDOP"."CNTR_TYPE"='C-GRIND' OR "SNDOP"."CNTR_TYPE"='C-GRIND TIGHT TOL');
	SQLQuery :=
		'SELECT * FROM (
			SELECT TO_CHAR(TIME) AS TIME, NAME, SUM(QTY_DONE) AS QTY
			FROM 
				(SELECT'  
					||colnameList||' AS Time, 
					CONCAT(CONCAT("PR_EMP"."FIRST_NAME",''),"PR_EMP"."LAST_NAME") AS NAME,  
					sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE
				 FROM  "IQMS"."TA_LABOR" "TA_LABOR" 
				 	LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID"  
				 	LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID" 
				 	LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
				 	LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID"
				 	LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID"
				 	LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
				 WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10) 
				 	AND ("SNDOP"."CNTR_TYPE" ='||'C-GRIND'||' OR "SNDOP"."CNTR_TYPE" ='||'C-GRIND TIGHT TOL'||')
				 GROUP BY  
					"TA_LABOR"."TIME_OUT",
					"PR_EMP"."FIRST_NAME",
					"PR_EMP"."LAST_NAME"
				ORDER BY "TA_LABOR"."TIME_OUT")
				GROUP BY TIME, NAME) 
		    PIVOT(SUM(QTY) FOR TIME IN ('||colnameList||'))';
OPEN :c FOR SQLQuery;
END;
/

Report message to a moderator

Re: BASIC SYNTAX [message #686185 is a reply to message #686183]

Wed, 29 June 2022 14:14

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

"SELECT ... INTO ..." works only when the result set contains just one row.
It seems your first query returns more than one row, is it expected or not? Is this a query error or a data error?
What do you want from this query?

Quote:

I thought that since I have the same WHERE statement in both the colnameList query as in the SQLQuery statement, I would get a same size list.

Maybe yes maybe no as you have a DISTINCT in the first query and a PIVOT in the second one which both may change the number of rows of the core query with the WHERE clause.

[Updated on: Wed, 29 June 2022 14:19]

Report message to a moderator

Re: BASIC SYNTAX [message #686190 is a reply to message #686185]

Wed, 29 June 2022 15:07

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Now that I am starting to figure out the syntax, let me get back to my original problem that led me down this rabbit hole.

If I run this code:

SELECT * FROM (
	SELECT TO_CHAR(TIME) AS TIME, NAME, SUM(QTY_DONE) AS QTY
	FROM 
		(SELECT  
			TRUNC("TA_LABOR"."TIME_OUT") AS Time, 
			CONCAT(CONCAT("PR_EMP"."FIRST_NAME",' '),"PR_EMP"."LAST_NAME") AS NAME,  
			sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE
		 FROM   "IQMS"."TA_LABOR" "TA_LABOR" 
		 	LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID"  
		 	LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID" 
		 	LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
		 	LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID"
		 	LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID"
		 	LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
			WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10)
			 	AND ("SNDOP"."CNTR_TYPE" = 'C-GRIND' OR "SNDOP"."CNTR_TYPE" = 'C-GRIND TIGHT TOL')
			GROUP BY  
				"TA_LABOR"."TIME_OUT",
				"PR_EMP"."FIRST_NAME",
				"PR_EMP"."LAST_NAME"
			ORDER BY "TA_LABOR"."TIME_OUT")
	GROUP BY TIME, NAME)

I get these results (truncated):

TIME NAME QTY
29-JUN-22 Sonita Luy 3388
28-JUN-22 Sonita Luy 3168
20-JUN-22 Andrea Valdez 6552
20-JUN-22 Thomas Salazar 3048
21-JUN-22 Melissa Sandoval
29-JUN-22 Melaine Ronquillo
29-JUN-22 Sovann Chev
23-JUN-22 Isela Rodriguez 4392
22-JUN-22 Sinuon Keo 1572
22-JUN-22 Sonita Luy 4008
20-JUN-22 Delores Minott
29-JUN-22 Isela Rodriguez 3552
28-JUN-22 Andrea Valdez 2856
27-JUN-22 Melissa Sandoval 7368
20-JUN-22 Samantha Saccomano
20-JUN-22 Sovann Chev 1488
21-JUN-22 Melissa Zambrano 3024
23-JUN-22 Sinuon Keo 2172
26-JUN-22 Philip Morandarte 7338

I am trying to make a basic pivot table from this data where Names are in rows on the left, Dates are columns on top, with a sum of QTY populated in the middle. I can achieve this with a PIVOT function, but the issue is that the PIVOT function requires me to hard code the dates into the pivot-in-clause. I have been trying to follow examples on how to write something where the pivot-in-clause can be populated with a subquery but haven't been successful.

Any suggestions are greatly appreciated.

Here is query with pivot statement that works if hardcoded dates are in.

SELECT * FROM (
	SELECT TO_CHAR(TIME) AS TIMES, NAME, SUM(QTY_DONE) AS QTY
	FROM 
		(SELECT  
			TRUNC("TA_LABOR"."TIME_OUT") AS Time, 
			CONCAT(CONCAT("PR_EMP"."FIRST_NAME",' '),"PR_EMP"."LAST_NAME") AS NAME,  
			sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE
		 FROM   "IQMS"."TA_LABOR" "TA_LABOR" 
		 	LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID"  
		 	LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID" 
		 	LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
		 	LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID"
		 	LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID"
		 	LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
		 WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10)
		 	AND ("SNDOP"."CNTR_TYPE" = 'C-GRIND' OR "SNDOP"."CNTR_TYPE" = 'C-GRIND TIGHT TOL')
		 GROUP BY  
			"TA_LABOR"."TIME_OUT",
			"PR_EMP"."FIRST_NAME",
			"PR_EMP"."LAST_NAME"
		 ORDER BY "TA_LABOR"."TIME_OUT")
		GROUP BY TIME, NAME) 
PIVOT (
SUM(QTY) FOR TIMES IN ('29-JUN-22','28-JUN-22','27-JUN-22','26-JUN-22','25-JUN-22'))

Results in picture... I am not sure how to copy a table nicely into this forum.

Attachment: results.png
(Size: 21.88KB, Downloaded 125 times)

[Updated on: Wed, 29 June 2022 15:10]

Report message to a moderator

Re: BASIC SYNTAX [message #686191 is a reply to message #686190]

Wed, 29 June 2022 15:20

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Also, if I run the code below, it runs successfully, but once I try to uncomment either of the last lines to execute the SQLQuery, I get an error about a right paranthesis missing.

var c refcursor
DECLARE 
	colnameList varchar(200) := NULL;
	SQLQuery VARCHAR2(32760);
BEGIN
	FOR d IN (SELECT DISTINCT TRUNC("TA_LABOR"."TIME_OUT") AS Times INTO colnameList 
				FROM "IQMS"."TA_LABOR" LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
				WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10))
	LOOP
	colnameList := d.Times;
	END LOOP;
	SQLQuery :=
		'SELECT * FROM (
			SELECT TO_CHAR(TIME) AS TIME, NAME, SUM(QTY_DONE) AS QTY
			FROM 
				(SELECT'  
					||colnameList||' AS Time, 
					CONCAT(CONCAT("PR_EMP"."FIRST_NAME",''),"PR_EMP"."LAST_NAME") AS NAME,  
					sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE
				 FROM  "IQMS"."TA_LABOR" "TA_LABOR" 
				 	LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID"  
				 	LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID" 
				 	LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
				 	LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID"
				 	LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID"
				 	LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
				 WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10) 
				 GROUP BY  
					"TA_LABOR"."TIME_OUT",
					"PR_EMP"."FIRST_NAME",
					"PR_EMP"."LAST_NAME"
				ORDER BY "TA_LABOR"."TIME_OUT")
				GROUP BY TIME, NAME) 
		    PIVOT(SUM(QTY) FOR TIME IN ('||colnameList||'))';
	--OPEN :c FOR SQLQuery;
	--EXECUTE IMMEDIATE SQLQuery;
END;
/

Report message to a moderator

Dynamic Pivot Table [message #686193 is a reply to message #686163]

Wed, 29 June 2022 16:52

Dhoback
Messages: 10
Registered: June 2022

Junior Member

Running the query below gives me a pivot table I am looking for, however, it requires that I hard code the dates into the query. I have been trying to follow examples for subqueries in the pivot-in-clause, but haven't been successful in making any of them work. Any advice on how to make this happen is greatly appreciated.

FYI - I am connecting to an ODBC database. This query runs with my SQL editor SQLDBX.exe. I tried Oracle Developer, but couldn't figure out how to connect to ODBC database. When I run this code in SQL*Plus, I get an error for the Pivot Statement, but it ignores it and gives unpivoted results.

SELECT * FROM (
	SELECT TO_CHAR(TIME) AS TIMES, NAME, SUM(QTY_DONE) AS QTY
	FROM 
		(SELECT  
			TRUNC("TA_LABOR"."TIME_OUT") AS Time, 
			CONCAT(CONCAT("PR_EMP"."FIRST_NAME",' '),"PR_EMP"."LAST_NAME") AS NAME,  
			sum("TA_LABOR"."PROCESS_QTY") AS QTY_DONE
		 FROM   "IQMS"."TA_LABOR" "TA_LABOR" 
		 	LEFT OUTER JOIN "IQMS"."PR_EMP" "PR_EMP" ON "TA_LABOR"."PR_EMP_ID"="PR_EMP"."ID"  
		 	LEFT OUTER JOIN "IQMS"."PARTNO" "PARTNO" ON "TA_LABOR"."PARTNO_ID"="PARTNO"."ID" 
		 	LEFT OUTER JOIN "IQMS"."SNDOP" "SNDOP" ON "TA_LABOR"."SNDOP_ID"="SNDOP"."ID" 
		 	LEFT OUTER JOIN "IQMS"."SNDOP_LABOR" "SNDOP_LABOR" ON "SNDOP"."ID"="SNDOP_LABOR"."SNDOP_ID"
		 	LEFT OUTER JOIN "IQMS"."EMP_LEVEL" "EMP_LEVEL" ON "SNDOP_LABOR"."EMP_LEVEL_ID"="EMP_LEVEL"."ID"
		 	LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "PARTNO"."ARINVT_ID"="ARINVT"."ID"
		 WHERE   trunc("TA_LABOR"."TIME_OUT")>= trunc(SYSDATE -10)
		 	AND ("SNDOP"."CNTR_TYPE" = 'C-GRIND' OR "SNDOP"."CNTR_TYPE" = 'C-GRIND TIGHT TOL')
		 GROUP BY  
			"TA_LABOR"."TIME_OUT",
			"PR_EMP"."FIRST_NAME",
			"PR_EMP"."LAST_NAME"
		 ORDER BY "TA_LABOR"."TIME_OUT")
		GROUP BY TIME, NAME) 
PIVOT (
SUM(QTY) FOR TIMES IN ('29-JUN-22','28-JUN-22','27-JUN-22','26-JUN-22','2-JUN-22'))

Results in picture. I am still not sure how to copy results from a table aligned......

Attachment: results.png
(Size: 21.88KB, Downloaded 123 times)

Report message to a moderator

Re: Dynamic Pivot Table [message #686194 is a reply to message #686193]

Thu, 30 June 2022 00:20

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

When I run this code in SQL*Plus, I get an error for the Pivot Statement, but it ignores it and gives unpivoted results.

Please, copy and paste your SQL*Plus session as I did in your previous topic.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: BASIC SYNTAX [message #686195 is a reply to message #686191]

Thu, 30 June 2022 00:24

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

but once I try to uncomment either of the last lines to execute the SQLQuery, I get an error about a right paranthesis missing.

The best way to debug dynamic query is to use "dbms_output.put_line(SQLQuery)" to see what you actually generate.

Report message to a moderator

Previous Topic:	How to update multiple rows with different values from another table in the same query?
Next Topic:	SENDGRID \| Curl

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 01 14:46:15 CDT 2023