Nested SELECT statements

Hello,
I’m using dhtmlx connector with dhtmlx grid and I’m trying to load grid from SQL statement with nested SELECT statements, like

SELECT katalog.c_titulu,CAST(katalog.id AS NVARCHAR)+'-'+CAST((SELECT cts.id FROM dbo.c_tabulky_sloupce AS cts WHERE (cts.nazev_tabulky = 'katalog') AND (cts.nazev_sloupce = 'doda')) AS NVARCHAR) AS doda, katalog.issn_isbn, katalog.issn_online,CAST((SELECT c_zeme1.id FROM c_zeme c_zeme1 WHERE c_zeme.text=c_zeme1.text) AS NVARCHAR)+'-'+CAST((SELECT cts2.id FROM dbo.c_tabulky_sloupce AS cts2 WHERE (cts2.nazev_tabulky = 'c_zeme') AND (cts2.nazev_sloupce = 'text')) AS NVARCHAR) AS zem_puvodu_id FROM katalog  LEFT JOIN c_zeme ON c_zeme.id=katalog.zem_puvodu_id

but the connector answers with a few error messages

The multi-part identifier &quot;c_zeme.text&quot; could not be bound.<br>The multi-part identifier &quot;c_zeme.id&quot; could not be bound.<br>Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.<br>The cursor was not declared.<br>sp_cursorfetch: The cursor identifier value provided (0) is not valid.<br>sp_cursorclose: The cursor identifier value provided (0) is not valid.

I know the problem is in the nested SELECT’s, but please help me to solve it. I can’t create a view, because the query is created by Stored Procedure for each user.

Which server side platform ( php, java, .net, etc ) you are using ? And which DB type?

I’m using SQL SERVER 2008 and .NET 0.9 connector.

:frowning:

You can try to store all SQL code as name of table , something like

string query = @"SELECT katalog.c_titulu,CAST(katalog.id AS NVARCHAR)+'-'+CAST((SELECT cts.id FROM dbo.c_tabulky_sloupce AS cts WHERE (cts.nazev_tabulky = 'katalog') AND (cts.nazev_sloupce = 'doda')) AS NVARCHAR) AS doda, katalog.issn_isbn, katalog.issn_online,CAST((SELECT c_zeme1.id FROM c_zeme c_zeme1 WHERE c_zeme.text=c_zeme1.text) AS NVARCHAR)+'-'+CAST((SELECT cts2.id FROM dbo.c_tabulky_sloupce AS cts2 WHERE (cts2.nazev_tabulky = 'c_zeme') AND (cts2.nazev_sloupce = 'text')) AS NVARCHAR) AS zem_puvodu_id FROM katalog  LEFT JOIN c_zeme ON c_zeme.id=katalog.zem_puvodu_id";
            
dhtmlxGridConnector con = new dhtmlxGridConnector("(" + query + ") AAA", "*", "cts", dhtmlxDatabaseAdapterType.SqlServer2005, ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString);

I tried your solution like

connector = new dhtmlxGridConnector("(" + dotaz + ") kat_katalog", "*", "kat_katalog.c_titulu", dhtmlxDatabaseAdapterType.SqlServer2005, connection_string);

which should give something like

SELECT * FROM (SELECT kat_katalog.c_titulu, kat_katalog.issn_isbn, kat_katalog.nazev, kat_katalog.jazyk,CAST((SELECT gc_zeme1.id FROM gc_zeme gc_zeme1 WHERE gc_zeme.text=gc_zeme1.text) AS NVARCHAR)+'-'+CAST((SELECT cts2.id FROM dbo.app_c_tabulky_sloupce AS cts2 WHERE (cts2.nazev_tabulky = 'gc_zeme') AND (cts2.nazev_sloupce = 'text')) AS NVARCHAR) AS zem_puvodu_id FROM kat_katalog  LEFT JOIN gc_zeme ON gc_zeme.id=kat_katalog.zem_puvodu_id) kat_katalog

and it works like a query without connector, but the connector throws

Column '' does not belong to table Table2.

:frowning:
Why is this happening?
edit:now the exception is thrown even if there are no nested Select’s.

The problem is still actual :frowning:

Runing the same code locally results in DB execution error - but this is not a parsing error, just has not the same table structure locally. Are you sure that SQL code which you are using in the query is valid ?

If issue still occurs can you provide some standalone sample or sample DB, against which sql query need to be executed.

Thank you for your answer.
I’ve copied the query from debug of handler, which works with connector. The query is 100% valid, it works thru Management Studio, but fails thru connector.

SELECT kat_katalog.c_titulu, kat_katalog.issn_isbn, kat_katalog.nazev, kat_katalog.jazyk,CAST((SELECT gc_zeme1.id FROM gc_zeme gc_zeme1 WHERE gc_zeme.text=gc_zeme1.text) AS NVARCHAR)+'-'+CAST((SELECT cts2.id FROM dbo.app_c_tabulky_sloupce AS cts2 WHERE (cts2.nazev_tabulky = 'gc_zeme') AND (cts2.nazev_sloupce = 'text')) AS NVARCHAR) AS zem_puvodu_id FROM kat_katalog  LEFT JOIN gc_zeme ON gc_zeme.id=kat_katalog.zem_puvodu_id

I’ve attached script for 3 tables used in query.

USE [testtesttest]
GO

/****** Object:  Table [dbo].[app_c_tabulky_sloupce]    Script Date: 12/21/2010 09:39:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[app_c_tabulky_sloupce](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nazev_tabulky] [nvarchar](50) NOT NULL,
	[nazev_sloupce] [nvarchar](50) NOT NULL,
	[hlavicka] [nvarchar](50) NULL,
	[prava_pro_zobrazeni] [int] NULL,
 CONSTRAINT [PK_c_tabulky_sloupce] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [testtesttest]
GO

/****** Object:  Table [dbo].[gc_zeme]    Script Date: 12/21/2010 09:40:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[gc_zeme](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[kod] [char](2) NULL,
	[kontinent] [char](30) NULL,
	[preference] [varchar](20) NULL,
	[text] [varchar](250) NULL,
 CONSTRAINT [PK_c_zeme] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [testtesttest]
GO

/****** Object:  Table [dbo].[kat_katalog]    Script Date: 12/21/2010 09:40:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[kat_katalog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[c_titulu] [int] NOT NULL,
	[issn_isbn] [char](20) NULL,
	[issn_online] [char](20) NULL,
	[nazev] [nvarchar](254) NOT NULL,
	[pod_titul] [nvarchar](254) NULL,
	[perio] [int] NULL,
	[volume] [nvarchar](50) NULL,
	[zem_kod] [nvarchar](50) NULL,
	[zem_puvodu] [char](10) NULL,
	[zem_puvodu_id] [int] NULL,
	[elektro] [bit] NULL,
	[elektro_je] [bit] NULL,
	[doda] [nvarchar](50) NULL,
	[poznamka] [nvarchar](max) NULL,
	[jazyk] [nvarchar](100) NULL,
	[doc_type] [nvarchar](50) NULL,
	[rok_vydani] [nvarchar](50) NULL,
	[coden] [nvarchar](50) NULL,
	[abstract] [nvarchar](max) NULL,
	[abs_idx] [nvarchar](250) NULL,
	[alternate] [nvarchar](250) NULL,
	[status_publikace] [nvarchar](50) NULL,
	[uzi_1] [bit] NULL,
	[uzi_2] [varchar](50) NULL,
	[katalog_id]  AS ([id]),
	[kategorie_id] [int] NULL,
 CONSTRAINT [PK_katalog] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[kat_katalog]  WITH CHECK ADD  CONSTRAINT [FK_katalog_c_zeme] FOREIGN KEY([zem_puvodu_id])
REFERENCES [dbo].[gc_zeme] ([id])
GO

ALTER TABLE [dbo].[kat_katalog] CHECK CONSTRAINT [FK_katalog_c_zeme]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__issn_is__787EE5A0]  DEFAULT (NULL) FOR [issn_isbn]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__issn_on__797309D9]  DEFAULT (NULL) FOR [issn_online]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__pod_tit__7A672E12]  DEFAULT (NULL) FOR [pod_titul]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__perio__7B5B524B]  DEFAULT (NULL) FOR [perio]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__volume__7D439ABD]  DEFAULT (NULL) FOR [volume]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__zem_kod__7F2BE32F]  DEFAULT (NULL) FOR [zem_kod]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__zem_puv__00200768]  DEFAULT (NULL) FOR [zem_puvodu]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__elektro__01142BA1]  DEFAULT (NULL) FOR [elektro]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__elektro__02084FDA]  DEFAULT (NULL) FOR [elektro_je]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__doda__02FC7413]  DEFAULT (NULL) FOR [doda]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__jazyk__04E4BC85]  DEFAULT (NULL) FOR [jazyk]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__doc_typ__05D8E0BE]  DEFAULT (NULL) FOR [doc_type]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__rok_vyd__06CD04F7]  DEFAULT (NULL) FOR [rok_vydani]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__coden__07C12930]  DEFAULT (NULL) FOR [coden]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__abs_idx__08B54D69]  DEFAULT (NULL) FOR [abs_idx]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__alterna__09A971A2]  DEFAULT (NULL) FOR [alternate]
GO

ALTER TABLE [dbo].[kat_katalog] ADD  CONSTRAINT [DF__katalog__status___0A9D95DB]  DEFAULT (NULL) FOR [status_publikace]
GO


And what error are you talking about? The exception is thrown only by connector, the same sql statement works w/out it perfectly.

Instead of kat_katalog.c_titulu try to use c_titulu

dhtmlxGridConnector connector = new dhtmlxGridConnector(@"(SELECT kat_katalog.c_titulu, kat_katalog.issn_isbn, kat_katalog.nazev, kat_katalog.jazyk,CAST((SELECT gc_zeme1.id FROM gc_zeme gc_zeme1 WHERE gc_zeme.text=gc_zeme1.text) AS NVARCHAR)+'-'+CAST((SELECT cts2.id FROM dbo.app_c_tabulky_sloupce AS cts2 WHERE (cts2.nazev_tabulky = 'gc_zeme') AND (cts2.nazev_sloupce = 'text')) AS NVARCHAR) AS zem_puvodu_id FROM kat_katalog LEFT JOIN gc_zeme ON gc_zeme.id=kat_katalog.zem_puvodu_id) kat_katalog", "*", "c_titulu", dhtmlxDatabaseAdapterType.SqlServer2005, "Data Source=127.0.0.1, 1433;Initial Catalog=testtesttest;Persist Security Info=True;User ID=sa;Password=1" );

Thank you for your answer. It works, but now the grid filter fails with “Incorrect syntax near ‘*’.The cursor was not declared.” :cry: