CODEWITHSUNDEEP
erpnextfrappe
Ashutosh Halape
Ashutosh Halape

Reputation: 19

How to fetch doctype eg: address or tax rule

I want to fetch the doctype. How do I do this? I want to add a separate column which will give doctype such as sales order, purchase order etc. The first line gives me error what query should be fired. Please help I am new to ERP Next.

SELECT
    AD.ref_doctype AS “Doctype:Link/User:120”,
    AD.name AS “Doc#:Link/Doctype:120”,
    AD.owner AS “Created By:Link/User:120”,
    AD.modified AS “Modified On:Date:120”
FROM tabAddress AS AD

WHERE
    DATEDIFF(now(),AD.modified) BETWEEN 1 AND 30

UNION ALL

SELECT
    TR.name AS “Doc#:Link/Doctype:120”,
    TR.owner AS “Created By:Link/User:120”,
    TR.modified AS “Modified On:Date:120”
FROM tabTax Rule AS TR
WHERE
    DATEDIFF(now(),TR.modified) BETWEEN 1 AND 30

UNION ALL

SELECT
    IT.name AS “Doc#:Link/Doctype:120”,
    IT.owner AS “Created By:Link/User:120”,
    IT.modified AS “Modified On:Date:120”
FROM tabItem AS IT
WHERE
    DATEDIFF(now(),IT.modified) BETWEEN 1 AND 30

Upvotes: 0

Views: 623

Answers (2)

Martin Bramwell
Martin Bramwell

Reputation: 2111

It isn't completely clear to me what you mean by docType field.

Are you wanting a result like this?

Doctype:Link/User:120|Doc#:Link/Doctype:120|Created By:Link/User:120|Modified On:Date:120|
---------------------|---------------------|------------------------|--------------------|
Email Account        |Jobs                 |Administrator           | 2019-12-04 06:07:55|
Email Account        |Notifications        |Administrator           | 2019-12-01 05:25:53|
Email Account        |Replies              |Administrator           | 2019-12-01 05:25:53|
Email Account        |Sales                |Administrator           | 2019-12-04 06:07:55|
Email Account        |Support              |Administrator           | 2019-12-04 06:07:55|

Here's the select :

set @docType = "Email Account";
SELECT
    @tabDocType  AS `Doctype:Link/User:120`,
    AD.name AS `Doc#:Link/Doctype:120`,
    AD.owner AS `Created By:Link/User:120`,
    AD.modified AS `Modified On:Date:120`
FROM `tabEmail Account` AS AD

Note the backticks on the field aliases! All these have different meanings in SQL:

"
'
`

The last one, backtick, is used to refer to database entities. You were trying to use “Doctype:Link/User:120” with double quotes, which declare plain text. Using backtick converts the alias into a db entity which can be referred to from elsewhere.

MariaDb doesn't allow the use of variables as table names directly, but you can do it using prepared statements, like this:

set @docType = "Email Account";
set @tabDocType = CONCAT('tab', @docType);
SET @sql_text = concat('

SELECT 
    "', @docType, '"  AS `Doctype:Link/User:120`
  , AD.name AS `Doc#:Link/Doctype:120`
  , AD.owner AS `Created By:Link/User:120`
  , AD.modified AS `Modified On:Date:120`
FROM `', @tabDocType, '` as AD;

');


PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The table name is now also specified by a variable, created from concatenation of 'tab' with the docType declared before.

You get the same result as above but -- you avoid accidentally changing the table name in one place but not in the other when editing some time in the future.

Upvotes: 0

Jenisha
Jenisha

Reputation: 1

to fetch doctype name you have to give the linked doctype name, For example, select IT.name as "IT No:Link/IT:120"

Upvotes: 0

Related Questions