[SAP B1] create a VIEW with UNION and WHILE

[Ammammata]

Hello

I'm creating a VIEW (to be used as Excel pivot source) that collects data
from five different tables. I coded it in MS SQL Server Management Studio
and it works fine, but when I copy it to the VIEW I get the error (Unable
to parse).

The SQL is like this:

SELECT *
INTO #tmp
FROM (
SELECT a,b,c
FROM tableONE
UNION ALL
SELECT a,b,c
FROM tableTWO
UNION ALL
SELECT a,b,c
FROM tableTHREE
UNION ALL
SELECT a,b,c
FROM tableFOUR
) tmp
DECLARE @cnt INT = 1
WHILE @cnt < 13
BEGIN
insert into #tmp
select a,b,@cnt as c
from tableFIVE
SET @cnt = @cnt + 1
END
select * from #tmp
DROP TABLE #tmp


As I wrote above, it WORKS, but not as a VIEW

The only "solution" I can imagine is to remove the #tmp table and make an
UNION of all 16 (4+12) select statements


Any tip about this?

[Erland Sommarskog]

Post by Ammammata
I'm creating a VIEW (to be used as Excel pivot source) that collects data
from five different tables. I coded it in MS SQL Server Management Studio
and it works fine, but when I copy it to the VIEW I get the error (Unable
to parse).
SELECT *
INTO #tmp

That's not a good start for a view. A view is a single SELECT statement.

It seems that this should make it for you:

CREATE VIEW yourview AS
SELECT a,b,c
FROM tableONE
UNION ALL
SELECT a,b,c
FROM tableTWO
UNION ALL
SELECT a,b,c
FROM tableTHREE
UNION ALL
SELECT a,b,c
FROM tableFOUR
UNION ALL
select a,b,V.n as c
from tableFIVE
cross join (VALUES(1), (2), (3), (4), (5), (6), (7),
(8), (9), (10), (11), (12), (13)) AS V(n)


THe error message "unable to parse" sounds like you are using the view
designer. Stay out of that tool, it's quite crippled and may not parse
the above either. Just run the above in a query window.

[Ammammata]

Il giorno Wed 31 Mar 2021 10:32:13p, *Erland Sommarskog* ha inviato su

Post by Erland Sommarskog
Just run the above in a query window.

thank you Erland, I'll give it a try

[Ammammata]

Il giorno Tue 06 Apr 2021 10:15:50a, *Ammammata* ha inviato su

Post by Ammammata
I'll give it a try

this is your "tip"

CREATE VIEW yourview AS
SELECT a,b,c
FROM tableONE
UNION ALL
SELECT a,b,c
FROM tableTWO
UNION ALL
SELECT a,b,c
FROM tableTHREE
UNION ALL
SELECT a,b,c
FROM tableFOUR
UNION ALL
select a,b,V.n as c
from tableFIVE
cross join (VALUES(1), (2), (3), (4), (5), (6), (7),
(8), (9), (10), (11), (12), (13)) AS V(n)

so:

the line "CREATE VIEW yourview AS" is underlined in red, that means there's
something wrong:


create view [dbo.PIPPO_FC] as


as well as "cross join" at the end

maybe the error is at the end and mirrors at the beginning, I'm not sure
about the syntax of "cross join":

--------- start of last part of my query ---------
[...]

UNION ALL

select
'S' as SP
, 'T' as DT
, 0 as QtyDel
, 0 as QtyTBD
, '' as Currency
, 0 as UnitPrice
, 0 as TotalCur
, 'EUR' as EUR
, 0 as PriceEUR
, 0 as TotalEUR
, h.U_F_YEAR as DelivYear
, V.n as DelivMonth -- <<<<<<<<<<<<<<<< Variable: what .n stays for?
, h.U_Rel as u_relation
, c.U_Man_CardCode as Manufacturer
, m.U_Shortname as ShortManuf
, c.U_Dist_CardCode as Distributor
, d.U_Shortname as ShortDistr
, Isnull(d.u_responsible, '0000') AS Salesman
, Isnull(f.u_u_shortname, 'NULL') AS ShortSales
, d.country AS CountryDistr
, r.U_Item AS ProductCode
, r.U_Item_CatNr AS ModelNumber
, e.itmsgrpcod AS PTCode
, pt.itmsgrpnam AS ProductType
, e.u_pippocode AS PippoCode
, 0 AS DocNo
, 'None' AS ServComp
, isnull(r.U_QTY1, 0) as Forecast

from [@PIPPO_FOR_ROW] r
inner join [@PIPPO_FOR_H] h on h.Code=r.Code
INNER JOIN [@mti_pippo_relations] c ON c.code = h.u_rel
INNER JOIN ocrd m ON m.cardcode = c.u_man_cardcode
INNER JOIN ocrd d ON d.cardcode = c.u_dist_cardcode
INNER JOIN oitm e ON e.itemcode = r.U_Item
INNER JOIN [@a_pippo_resp_salesm] f ON f.code = d.u_responsible
INNER JOIN oitb pt ON pt.itmsgrpcod = e.itmsgrpcod

where h.U_F_YEAR=2021 and h.U_F_MONTH=1

cross join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) as
V(n)
--------- end of last part of my query ---------

a quick search on the web reported this example:

MySQL CROSS JOIN Keyword
https://www.w3schools.com/mysql/mysql_join_cross.asp

CROSS JOIN Syntax
SELECT column_name(s)
FROM table1
CROSS JOIN table2;



keep in mind, I'm using SQL Server Management Studio 17.9.1, not MySQL

Microsoft SQL Server Management Studio 14.0.17289.0
Microsoft Analysis Services Client Tools 14.0.1016.283
Microsoft Data Access Components (MDAC) 10.0.14393.0
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.14393.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.14393

[Ammammata]

Il giorno Tue 06 Apr 2021 05:19:27p, *Ammammata* ha inviato su

Post by Ammammata
INNER JOIN oitb pt ON pt.itmsgrpcod = e.itmsgrpcod
where h.U_F_YEAR=2021 and h.U_F_MONTH=1
cross join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) as
V(n)

found ONE mistake: cross join must stay before where :/


the create view is still underlined, maybe it's not available in SAP B1
environment...

[Ammammata]

Il giorno Tue 06 Apr 2021 05:22:57p, *Ammammata* ha inviato su

Post by Ammammata
the create view is still underlined, maybe it's not available in SAP B1
environment...

ok, once I removed the CREATE command it worked in SQL MMS

then I copied the code in the VIEW but I got another error because of a
couple of DECLARE statement

finally, removed also those lines, the VIEW now works fine and Excel can
create the PIVOT table

well done, thank you once more

[Doc Tree]

Post by Ammammata
Il giorno Tue 06 Apr 2021 05:22:57p, *Ammammata* ha inviato su

Post by Ammammata
the create view is still underlined, maybe it's not available in SAP B1
environment...

ok, once I removed the CREATE command it worked in SQL MMS
then I copied the code in the VIEW but I got another error because of a
couple of DECLARE statement
finally, removed also those lines, the VIEW now works fine and Excel can
create the PIVOT table
well done, thank you once more
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Man my kid told me about this thread and I COULDNT BELIEVE IT